पाठ 3 / 35

The SQL You Need Before Any ORM

यह पाठ अभी हिंदी में अनुवादित नहीं है, इसलिए अंग्रेज़ी में दिखाया जा रहा है।

Recall primary keys, foreign keys, constraints and joins well enough to read the SQL an ORM generates.

लेखक: · 15 साल से वेब डेवलपर

Five ideas that every ORM assumes

Every ORM feature maps to a plain SQL idea, so revise these first. A primary key uniquely identifies a row and is usually an auto-increment integer or a UUID. A foreign key is a column that refers to another table's primary key, and the database can refuse inserts that point at missing rows. Constraints such as NOT NULL, UNIQUE and CHECK let the database enforce rules even if application code has a bug. A join combines rows from two tables on a condition; an inner join keeps only matches, while a left join keeps every row from the left table and fills missing matches with NULL. An index is a separate sorted structure that makes lookups on a column fast at the cost of slower writes and extra storage. When an ORM says relation, unique or @@index, it means exactly these things.

A join you can run without installing anything

Python ships with SQLite, so this runs anywhere. It creates two linked tables and counts posts per user with a left join.

import sqlite3
db = sqlite3.connect(':memory:')
db.executescript("""
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL);
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL, author_id INTEGER NOT NULL REFERENCES users(id));
INSERT INTO users (name, email) VALUES ('Asha', 'asha@example.com'), ('Ravi', 'ravi@example.com');
INSERT INTO posts (title, author_id) VALUES ('Hello SQL', 1), ('Joins 101', 1), ('First post', 2);
""")
for row in db.execute("""
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
GROUP BY u.id
ORDER BY post_count DESC;"""):
    print(row)

Output:

('Asha', 2)
('Ravi', 1)

Roll numbers and attendance registers

A college assigns each student a unique roll number (primary key). The attendance register for a subject writes only roll numbers, not full names (foreign key). To print names with attendance, the office matches the register against the student list (a join). Nobody copies the full student record into every register.

त्वरित जाँच: A LEFT JOIN from users to posts returns what for a user who has no posts?

  • One row for that user with NULL in the post columns
  • No row for that user
  • An error, because the foreign key is missing
  • One row per post in the whole table
Answer

One row for that user with NULL in the post columns — A left join keeps every row from the left table. Where no matching post exists, the post columns are NULL. An inner join would drop the user instead.