One-to-Many — loyihalash va so'rovlar

~20 daq · PostgreSQL · JOIN va Munosabatlar

Muammodan boshlaylik

Onlayn do'kon yozyapsiz. Har bir foydalanuvchi bir nechta buyurtma bera oladi. Buni qanday saqlash kerak?

Birinchi fikr: foydalanuvchi jadvaliga buyurtmalar ustunini qo'shish. Lekin bitta ustunda nechta buyurtmani saqlaysiz? Massivmi? Vergul bilan ajratilgan IDlarmi? Bu yo'l — ma'lumotlar bazasi uchun dahshat: qidirish qiyin, o'zgartirish qiyin, yaxlitlikni kafolatlash imkonsiz.

To'g'ri yechim — One-to-Many munosabat.

One-to-Many nima?

One-to-Many — "bitta" tomonidagi har bir yozuv "ko'p" tomonidagi bir nechta yozuvga ega bo'lishi mumkin bo'lgan munosabat. Lekin "ko'p" tomonidagi har bir yozuv faqat bitta "bitta" tomoniga tegishli.

Hayotiy o'xshatma: bir maktabda ko'p o'quvchi o'qiydi — bu One-to-Many. Har bir o'quvchi faqat bitta maktabda o'qiydi. Bir maktab ko'p o'quvchiga ega. Lekin bir o'quvchi ikki maktabda bir vaqtda o'qiy olmaydi.

Boshqa misollar:

Bir muallif → ko'p kitob
Bir kategoriya → ko'p mahsulot
Bir foydalanuvchi → ko'p buyurtma
Bir blog post → ko'p izoh

Qanday loyihalanadi?

Qoida sodda: "ko'p" tomonidagi jadvalga "bitta" tomonining IDsini saqlash. Bu ustun — foreign key.

-- "Bitta" tomoni
CREATE TABLE foydalanuvchilar (
  id    SERIAL PRIMARY KEY,
  ism   TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

-- "Ko'p" tomoni — foreign key orqali bog'lanadi
CREATE TABLE buyurtmalar (
  id                SERIAL PRIMARY KEY,
  foydalanuvchi_id  INT NOT NULL REFERENCES foydalanuvchilar(id),
  mahsulot          TEXT NOT NULL,
  summa             NUMERIC NOT NULL,
  sana              TIMESTAMPTZ DEFAULT now()
);

foydalanuvchi_id — bu foreign key. U foydalanuvchilar.id ga ishora qiladi. PostgreSQL bu bog'liqlikni kafolatlaydi: mavjud bo'lmagan foydalanuvchi IDsini buyurtmalar ga qo'shib bo'lmaydi.

Ma'lumot qo'shish

INSERT INTO foydalanuvchilar (ism, email) VALUES
  ('Ali',  'ali@mail.com'),
  ('Vali', 'vali@mail.com'),
  ('Soli', 'soli@mail.com');

INSERT INTO buyurtmalar (foydalanuvchi_id, mahsulot, summa) VALUES
  (1, 'Noutbuk',    5000000),
  (1, 'Sichqon',     150000),
  (2, 'Klaviatura',  350000),
  (2, 'Monitor',    2800000),
  (2, 'Headset',     450000);
-- Soli hali buyurtma bermagan

Endi ma'lumotlar ikki jadvalda — lekin foydalanuvchi_id orqali bog'liq.

Asosiy so'rovlar

Foydalanuvchi bilan uning buyurtmalari

SELECT
  f.ism,
  b.mahsulot,
  b.summa
FROM foydalanuvchilar f
JOIN buyurtmalar b ON f.id = b.foydalanuvchi_id
WHERE f.id = 2;

 ism  │ mahsulot    │  summa
------+-------------+---------
 Vali │ Klaviatura  │  350000
 Vali │ Monitor     │ 2800000
 Vali │ Headset     │  450000

Har bir foydalanuvchining buyurtmalar soni va umumiy summasi

SELECT
  f.ism,
  COUNT(b.id)    AS buyurtmalar_soni,
  COALESCE(SUM(b.summa), 0) AS jami_summa -- ← NULL bo'lsa 0
FROM foydalanuvchilar f
LEFT JOIN buyurtmalar b ON f.id = b.foydalanuvchi_id
GROUP BY f.id, f.ism
ORDER BY jami_summa DESC;

 ism  │ buyurtmalar_soni │ jami_summa
------+------------------+------------
 Vali │ 3                │  3600000
 Ali  │ 2                │  5150000
 Soli │ 0                │          0

Nima uchun LEFT JOIN? Soli buyurtma bermagan — INNER JOIN ishlatilsa u natijaga tushmaydi. LEFT JOIN uni ham ko'rsatadi, buyurtmalari ustunlari NULL.

Eng ko'p buyurtma bergan foydalanuvchi

SELECT
  f.ism,
  COUNT(b.id) AS soni
FROM foydalanuvchilar f
JOIN buyurtmalar b ON f.id = b.foydalanuvchi_id
GROUP BY f.id, f.ism
ORDER BY soni DESC
LIMIT 1;

Oxirgi buyurtmani topish

SELECT DISTINCT ON (b.foydalanuvchi_id)
  f.ism,
  b.mahsulot,
  b.sana
FROM foydalanuvchilar f
JOIN buyurtmalar b ON f.id = b.foydalanuvchi_id
ORDER BY b.foydalanuvchi_id, b.sana DESC; -- ← har foydalanuvchi uchun eng oxirgi

DISTINCT ON — PostgreSQL ning maxsus sintaksisi. Har bir foydalanuvchi_id uchun birinchi qatorni oladi. ORDER BY orqali "birinchi" qaysi ekanini belgilaymiz — bu yerda eng yangi sana.

Foreign key va yaxlitlik

Foreign key — ma'lumotlar yaxlitligini kafolatlaydi. Mavjud bo'lmagan foydalanuvchiga buyurtma qo'shib bo'lmaydi:

INSERT INTO buyurtmalar (foydalanuvchi_id, mahsulot, summa)
VALUES (999, 'Telefon', 1000000);
-- ❌ ERROR: insert or update on table "buyurtmalar" violates foreign key constraint

Foydalanuvchini o'chirishda nima bo'ladi? Unga tegishli buyurtmalar bor — PostgreSQL bloklaydi:

DELETE FROM foydalanuvchilar WHERE id = 1;
-- ❌ ERROR: update or delete on table "foydalanuvchilar" violates foreign key constraint

Bu xulq-atvorni ON DELETE bilan o'zgartirish mumkin:

-- Foydalanuvchi o'chirilsa — uning buyurtmalari ham o'chadi
foydalanuvchi_id INT REFERENCES foydalanuvchilar(id) ON DELETE CASCADE

-- Foydalanuvchi o'chirilsa — foydalanuvchi_id NULL bo'ladi
foydalanuvchi_id INT REFERENCES foydalanuvchilar(id) ON DELETE SET NULL

Qachon qaysi? CASCADE — foydalanuvchi bilan birga uning barcha ma'lumotlari ham keraksiz bo'lganda. SET NULL — buyurtmalar saqlanib qolishi kerak, lekin foydalanuvchi bilan bog'liq bo'lmasligi kerak bo'lganda.

Index — performance uchun

Foreign key ustuniga index qo'shish shart. PostgreSQL PRIMARY KEY va UNIQUE ustunlarga avtomatik index qo'shadi — lekin foreign key ustuniga qo'shmaydi.

CREATE INDEX ON buyurtmalar(foydalanuvchi_id);

Bu index nima beradi? WHERE foydalanuvchi_id = 5 yoki JOIN da ON f.id = b.foydalanuvchi_id — PostgreSQL butun jadvalni ko'rib chiqmaydi, indeks orqali tezda topadi.

Millionlik jadvallarda bu index yo'q — JOIN sekin, bor — tez. Farq 100 barobar bo'lishi mumkin.

Tez-tez uchraydigan xatolar

Foreign key ustunida index yo'qligi:

-- ❌ Ko'p ishlatiladi, lekin index yo'q
foydalanuvchi_id INT REFERENCES foydalanuvchilar(id)

-- ✅ Index ham qo'shildi
CREATE INDEX idx_buyurtmalar_foydalanuvchi ON buyurtmalar(foydalanuvchi_id);

Jadval kichik bo'lsa sezilmaydi. Katta bo'lganda — sezilarli sekinlashadi.

NOT NULL unutish:

-- ❌ — foydalanuvchisiz buyurtma saqlanib qoladi
foydalanuvchi_id INT REFERENCES foydalanuvchilar(id)

-- ✅ — har bir buyurtma foydalanuvchiga tegishli bo'lishi shart
foydalanuvchi_id INT NOT NULL REFERENCES foydalanuvchilar(id)

Mantiqan har bir buyurtma birovga tegishli bo'lishi kerak — NOT NULL bilan shu kafolatlanadi.

N+1 muammosi:

-- ❌ — har foydalanuvchi uchun alohida so'rov (N+1)
SELECT * FROM foydalanuvchilar; -- 100 foydalanuvchi
-- keyin har biri uchun:
SELECT * FROM buyurtmalar WHERE foydalanuvchi_id = 1;
SELECT * FROM buyurtmalar WHERE foydalanuvchi_id = 2;
-- ... 100 ta so'rov

-- ✅ — bitta so'rovda JOIN bilan
SELECT f.ism, b.mahsulot
FROM foydalanuvchilar f
JOIN buyurtmalar b ON f.id = b.foydalanuvchi_id;

N+1 — eng keng tarqalgan performance muammolaridan biri. Dastur kodida JOIN o'rniga sikl ichida so'rov yozilganda yuzaga keladi.

Xulosa

One-to-Many — eng ko'p uchraydigan munosabat. "Ko'p" tomonidagi jadvalga foreign key qo'yiladi — bu kalit.

Loyihalashda esda tuting:

"Ko'p" tomonda NOT NULL foreign key
Foreign key ustuniga CREATE INDEX
Yaxlitlik strategiyasini tanlash — ON DELETE CASCADE yoki SET NULL

So'rovlarda:

JOIN — birgalikda ko'rsatish
LEFT JOIN + GROUP BY — statistika, shu jumladan nol buyurtmali foydalanuvchilar
DISTINCT ON — har bir ota uchun bitta yozuv (oxirgi, birinchi va h.k.)

Keyingi dars

14-dars: Many-to-Many — junction table, so'rovlar