ארבע עשרה דוגמאות לשאילתות SQL ב knex.js
לאחרונה התחלתי לעבוד יותר ב knex.js בסביבת Node ובינתיים מאוד נהנה מהספריה והיכולות שלה. קנקס היא בעצם בונה שאילתות, קצת דומה ל Sequelize, רק שבמקום מבנה של ORM קנקס אימצה גישה יותר פתוחה ויותר קרובה ל SQL. הם לא מנסים להסתיר מכם את העובדה שאתם עובדים ב SQL, אלא לתת תחביר JavaScript קצת יותר גמיש במקום התחביר הטקסטואלי של SQL.
ההבדל המרכזי בין גישה זו ל ORM הוא שכאן אנחנו צריכים לדעת טוב SQL כדי להצליח לכתוב את השאילתות, ואין קשר הכרחי בין השאילתות למבנה המחלקות ביישום. מי שירצה יוכל לבנות עדיין את היישום בגישה מונחית עצמים אבל אתם תצטרכו לחבר לבד בין השאילתות לבין המחלקות בתוכנית.
בשביל להבין את הגישה של קנקס טוב יותר ולהתרשם מהתחביר הנה 14 דוגמאות לשאילתות SQL לא טריוויאליות באמצעותו.
1. מבנה בסיס הנתונים
את בסיס הנתונים והשאלות לקחתי מקורס SQL חינמי שמצאתי ברשת בקישור הזה: https://sql.sh/.
לא עברתי על חומרי הלימוד אבל התרגילים מצאו חן בעיניי ולכן בחרתי להשתמש בהם לדוגמאות כאן בפוסט. את קובץ התרגילים המקורי יחד עם נתונים לדוגמה להורדה אפשר למצוא כאן: https://sql.sh/exercices-sql.
בסיס הנתונים מורכב מ-3 טבלאות לפי המבנה הבא:
mysql> show tables;
+----------------------+
| Tables_in_myapp_test |
+----------------------+
| client |
| commande |
| commande_ligne |
+----------------------+
3 rows in set (0.01 sec)
mysql> describe client;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| prenom | varchar(255) | NO | | NULL | |
| nom | varchar(255) | NO | | NULL | |
| email | varchar(255) | NO | | NULL | |
| ville | varchar(255) | NO | | NULL | |
| password | varchar(255) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> describe commande;
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| client_id | int unsigned | NO | | NULL | |
| date_achat | date | NO | | NULL | |
| reference | varchar(255) | NO | | NULL | |
| cache_prix_total | float | NO | | NULL | |
+------------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> describe commande_ligne;
+---------------+----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| commande_id | int unsigned | NO | | NULL | |
| nom | varchar(255) | NO | | NULL | |
| quantite | int unsigned | NO | | NULL | |
| prix_unitaire | float unsigned | NO | | NULL | |
| prix_total | float unsigned | NO | | NULL | |
+---------------+----------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
אל תיבהלו מהצרפתית השמות ממילא דומים לאנגלית ואני אתרגם פה מה שצריך. מוכנים? נצא לדרך.
2. חיבור לבסיס הנתונים ושאילתה ראשונה
קבצי הנתונים הגיעו בפורמט של MySQL ולכן זה בסיס הנתונים בו השתמשתי לתרגיל. בשביל להתחבר לבסיס נתונים MySQL מתוכנית node.js באמצעות קנקס נפעיל את הפקודה:
const knex = require('knex')({
client: 'mysql2',
connection: {
host : '127.0.0.1',
user : 'root',
password : 'my-secret-pw',
database : 'myapp_test'
}
});
בעולם האמיתי נרצה לשמור את הסיסמה מחוץ לקוד ולגשת אליה באמצעות משתנה סביבה. את פקודת החיבור מפעילים פעם אחת ובכל שאר התוכנית משתמשים באוביקט הגלובאלי knex כדי ליצור שאילתות ולהריץ אותן מול בסיס הנתונים שבחרתם.
קנקס משתמש ב Promises ולכן נוח לעבוד איתו בתחביר של async/await. הקוד הבא הוא מבנה של תוכנית מלאה שמתחברת לבסיס הנתונים ומדפיסה את כל הנתונים מטבלת client:
const knex = require('knex')({
client: 'mysql2',
connection: {
host : '127.0.0.1',
user : 'root',
password : 'my-secret-pw',
database : 'myapp_test'
}
});
async function main() {
// prints all the users details
const users = await knex('client').select('*');
console.log(users);
knex.destroy();
}
main();
הקריאה ל knex.destroy בסוף סוגרת את כל החיבורים לבסיס הנתונים ומאפשרת לתוכנית להסתיים. בסעיפים הבאים אציג רק את פקודות knex עצמן שאני כותב. בשביל להפעיל כל פקודה צריך לכתוב אותה בתוך פוקנציית ה main לפני ה destroy.
3. משימה 1: מיוריאל
מצאו בטבלת הלקוחות את Muriel וודאו שהסיסמה שלה היא test11. הסיסמאות נשמרות ב SHA1 בעמודת password בטבלת הלקוחות (וכבר התחלנו ברגל שמאל בכל מה שקשור לאבטחת מידע - נו tant pis).
בחזרה לקנקס השאילתה הראשונה שלנו תיראה כך:
const muriel = await knex('client').where({
'prenom': 'Muriel',
'password': knex.raw('sha1("test11")'),
}).first();
console.log(muriel);
אפשר ממש לראות בראש את התרגום ל SQL. שימו לב ל knex.raw שנועד כדי להגיד ל knex שהערך שאנחנו מעבירים צריך לעבור כמו שהוא לשאילתה בלי להוסיף Quoting. עוד נראה הרבה ממנו בהמשך הפוסט.
הסיום ב first גורם לקנקס להחזיר אוביקט יחיד ולא רשימה וגם await מאוד חשוב שם כי knex מחזיר Promise. הדפסה של התוצאה נראית כך:
TextRow {
id: 11,
prenom: 'Muriel',
nom: 'Dupuis',
email: 'muriel@example.com',
ville: 'Paris',
password: '100c4e57374fc998e57164d4c0453bd3a4876a58'
}
4. משימה 2: חיפוש מוצרים בהזמנות
מצאו את כל המוצרים שמופיעים ביותר מהזמנה אחת. זאת השאילתה שהייתי כותב ב SQL:
select nom, count(commande_id) as commande_count from commande_ligne group by nom having commande_count > 1;
והתרגום לקנקס הוא כמעט אחד לאחד. הנה הקוד:
const products = await knex('commande_ligne').
select('nom').
count('commande_id as commande_count').
groupBy('nom').
having('commande_count', '>', '1');
console.log(products);
הפקודות select ו count מגדירות איזה שדות לבחור, groupBy זה group by ו having זה having.
5. משימה 3: הוספת עמודה
מצאו את כל המוצרים שמופיעים ביותר מהזמנה אחת, והפעם הוסיפו לתוצאה עמודה עם כל מזהי ההזמנות בהם המוצר מופיע.
הפונקציה group_concat של MySQL מיד קופצת לראש וקנקס נותן לנו להשתמש בה בלי לעשות יותר מדי עניין בעזרת knex.raw:
const products3 = await knex.
select('nom').
count('commande_id as commande_count').
select(knex.raw('group_concat(commande_id) as commandes')).
groupBy('nom').
from('commande_ligne').
having('commande_count', '>', '1');
console.log(products3);
שימו לב שהפעם במקום להעביר את שם הטבלה כפרמטר ל knex בתחילת השורה בחרתי להעביר אותה כפרמטר לפונקציה from. זה רק בשביל שיהיה נוח לקרוא.
6. משימה 4: עדכון עמודה
שמרו את המחיר הכולל של כל שורת בהזמנה בעמודת prix_total בטבלת commande_ligne. המחיר הכולל הוא המכפלה של prix_unitaire ו quantite.
אז לקנקס יש תחביר update מאוד פשוט שמקבל אוביקט ונראה כך:
await knex('commande_ligne')
.update({
prix_total: knex.raw('prix_unitaire * quantite'),
});
ושוב השימוש ב knex.raw כדי לתת לבסיס הנתונים לעשות את החישוב.
7. משימה 5: הצגת מחיר לכל הזמנה
עכשיו כשכתבנו את המחיר של כל שורה בהזמנה נוכל לחבר את כל השורות ולהציג את המחיר של כל הזמנה. על הדרך הוסיפו בבקשה גם את שם הלקוח ותאריך הקניה.
ג'וין משלוש טבלאות? C'est du gâteau:
const sol5 = await knex.
select('commande.id').
sum('commande_ligne.prix_total as prix_total').
select('commande.date_achat', 'client.prenom', 'client.nom').
from('commande').
join('commande_ligne', 'commande.id', '=', 'commande_ligne.commande_id').
join('client', 'client.id', '=', 'commande.client_id').
groupBy('commande.id');
console.log(sol5);
הזמנות ריקות לא יופיעו בתוצאות בגלל הבחירה ב INNER JOIN (זו ברירת המחדל). קנקס תומך גם בג'וין משמאל ומימין עם הפונקציות leftJoin ו rightJoin.
8. משימה 6: שמירת המחיר הכולל
הוסיפו לטבלת ההזמנות את המחיר הכולל של כל הזמנה בתוך עמודת cache_prix_total.
המשימה הזאת היא היחידה שנתקעתי עליה מתוך ה-14 פה בפוסט, בגלל שביום יום אני עובד על PostgreSQL. מסתבר שבפעולת עדכון של MySQL אי אפשר להשתמש בטבלה אותה מעדכנים בתוך Sub Query. אחרי הרבה משחקים הבנתי איך לארגן מחדש את השאילתות כדי שלא נצטרך והנה התוצאה:
await knex('commande').
join(
knex('commande_ligne').
sum('commande_ligne.prix_total as price').
select('commande_id').
groupBy('commande_id').
as('commande_lignes')
, 'commande_lignes.commande_id', 'commande.id').
update({
'cache_prix_total': knex.raw('CAST(commande_lignes.price AS FLOAT)'),
});
השאילתה המתאימה ב SQL נראית כך:
UPDATE `commande` INNER join
(SELECT
sum(`commande_ligne`.`prix_total`) as `price`,
`commande_id`
FROM `commande_ligne`
GROUP BY `commande_id`)
AS `commande_lignes`
ON `commande_lignes`.`commande_id` = `commande`.`id`
SET `cache_prix_total` = CAST(commande_lignes.price AS FLOAT)
מה שיוצר לנו את השאילתה הפנימית ב JOIN זה ההעברה של אוביקט knex בתור פרמטר ראשון לפונקציה join במקום שם של טבלה (כמו שראינו בדוגמאות קודמות). נקודה מבלבלת בתחביר היא שבגירסת ה knex אני מפעיל את as מתוך השאילתה הפנימית, אבל ב SQL הוא נמצא אחרי הסוגריים.
וכמובן החבר שלנו knex.raw נמצא שם בשביל שהקריאה ל CAST תעבור טוב לבסיס הנתונים.
9. משימה 7: רווח חודשי
הציגו את הסכום הכולל של ההזמנות שנכנסו בכל חודש.
הפונקציה שמיד קופצת לראש היא DATE_FORMAT והפיתרון נראה כך:
const sol7 = await knex('commande').
select(knex.raw("DATE_FORMAT(date_achat, '%Y_%m') as date")).
sum('cache_prix_total as total_orders').
groupBy('date');
console.log(sol7);
10. משימה 8: סדר לפי
מצאו את 10 הלקוחות שיצרו את ההזמנות הכי גדולות במערכת והציגו את מזהי הלקוחות וסכום כלל ההזמנות מכל אחד מהם.
מאחר ובטבלת ההזמנות כבר הוספתי את עמודת cache_prix_total נשאר רק לסכום את הערך הזה לכל ההזמנות של כל לקוח ולהציג את 10 הלקוחות עבורם התוצאה היא הגבוהה ביותר:
const sol8 = await knex('commande').
select('client_id').
sum('cache_prix_total as client_sum').
groupBy('client_id').
orderBy('client_sum', 'desc').
limit(10);
console.log(sol8);
11. משימה 9: סכום הזמנות בכל יום
הציגו את סכום ההזמנות שהתקבל בכל יום.
בהנחה שלא טעיתי בתרגום השאלה הזאת דומה בצורה מחשידה למשימה 7. בכל מקרה זה היה הפיתרון שלי:
const sol9 = await knex('commande').
select(knex.raw("DATE_FORMAT(date_achat, '%Y_%m_%d') as date")).
sum('cache_prix_total as total_orders').
groupBy('date_achat');
console.log(sol9);
12. משימה 10: יצירת עמודה חדשה
הוסיפו עמודה בשם category מסוג מספר לטבלת ההזמנות.
קנקס כולל לא רק בונה שאילתות אלא גם מנגנון לעדכון הסכימה, כלומר יצירה ועדכון של טבלאות. בשביל להוסיף עמודה אנחנו ניגשים לאוביקט schema של knex ומפעילים את הפונקציה עם השם המבלבל table:
await knex.schema.table('commande', function(table) {
table.integer('category');
});
וזה קצת מבלבל כי מצד אחד אנחנו לא מתארים את כל מבנה הטבלה אלא רק את מה שצריך לשנות, אבל התחביר מספיק פשוט בשביל שלא נהיה קשים איתם.
13. משימה 11: עדכון הקטגוריה בהזמנות
עדכנו את טבלת ההזמנות כך שעמודת category תכיל את הערך 1 להזמנות קטנות מ 200, הערך 2 להזמנות בין 200 ל 500, הערך 3 להזמנות בסכום בין 500 ל 1,000 והערך 4 להזמנות בסכום מעל 1,000.
זוכרים את knex.raw? בשילוב עם סימן הגרש ההפוך של JavaScript התרגיל הזה נפתר כמעט כולו ב SQL:
await knex('commande').update({
'category': knex.raw(`
CASE
WHEN cache_prix_total BETWEEN 0 AND 200 THEN 1
WHEN cache_prix_total BETWEEN 200 AND 500 THEN 2
WHEN cache_prix_total BETWEEN 500 AND 1000 THEN 4
WHEN cache_prix_total > 1000 THEN 4
END
`),
});
14. משימה 12: יצירת טבלה חדשה
צרו טבלה בשם commande_category ובה שתי עמודות: עמודה מספרית עם הערך של הקטגוריה, ועמודת טקסט עם תיאור הקטגוריה.
השם של הפונקציה ליצירת טבלה ב knex הוא כבר הרבה יותר הגיוני והקוד לפיתרון נראה כך:
await knex.schema.createTable('commande_category', function(table) {
table.integer('category');
table.string('description');
});
15. משימה 13: הכנסת ערכים במכה
מלאו את הטבלה החדשה בתיאורי 4 הקטגוריות שיצרנו.
הכנסה של 4 ערכים היא הזדמנות מצוינת להכיר את batchInsert של knex:
await knex.batchInsert('commande_category', [
{ category: 1, description: '< 200' },
{ category: 2, description: 'Between 200 and 500' },
{ category: 3, description: 'Between 500 and 1000' },
{ category: 4, description: '> 1000' },
]);
היא עושה בדיוק מה שאתם מדמיינים ואם אתם יודעים יותר טוב מקנקס מה טוב בשבילכם תוכלו לקבוע מה יהיה גודל הצ'אנק שיישלח לבסיס הנתונים (ברירת המחדל היא 1,000).
16. משימה 14: מחיקה
מחקו את כל ההזמנות הישנות יותר מ 1.2.2019, ועבור כל הזמנה מחקו גם את השורות המתאימות בטבלת commande_ligne.
הפונקציה del היא הדרך למחוק רשומות. תחילה נשתמש ב Sub Query כדי למחוק את כל ההזמנות מ commande_ligne:
await knex('commande_ligne')
.whereIn('commande_id', function() {
this.
select('id').
from('commande').
where('date_achat', '<', knex.raw("DATE('2019-02-1')"));
}).del();
חשוב לשים לב בבניית ה whereIn שהפונקציה לא צריכה להחזיר ערך אלא בונה את השאילתה באמצעות שימוש באוביקט this ש knex מעביר לה.
לאחר מכן אפשר להמשיך ולמחוק את כל ההזמנות מטבלת commande:
await knex('commande').
where('date_achat', '<', knex.raw("DATE('2019-02-1')")).
del();
17. לסיכום
העבודה עם knex תהיה מאוד נוחה למתכנתים שמכירים טוב SQL ורוצים דרך קלה לכתוב שאילתות בצורה שגם תשתלב יפה בתוך קוד התוכנית, גם תאפשר Code Reuse וגם תאפשר יחסית בקלות לעבור לבסיס נתונים אחר. קנקס לא יגיד לכם איך לבנות את הקוד ולכן נוח להשתמש בו כמעט בכל מערכת.
בצד השלילי הפוקוד רק על ה SQL אומר שאין לנו פינוקים כמו וולידציות וטיפול אוטומטי ב Associations שאנחנו יכולים לקבל מ ORM-ים אמיתיים כמו לדוגמה Sequelize.