Many-to-Many munosabat — junction table, so'rovlar

~20 daq · MySQL · JOIN va Munosabatlar

Muammodan boshlaylik

Mahsulotlarga teglar qo'shmoqchisiz: yangi, chegirma, xit, simsiz.

  • AirPods Pro — xit, simsiz
  • Qopqoq — chegirma
  • Samsung Galaxy A55 — yangi, chegirma, xit

Bitta mahsulotda ko'p teg. Bitta tegda ko'p mahsulot. Foreign key ni qayerga qo'yish kerak?

  • mahsulotlar.teg_id — mahsulotda faqat bitta teg bo'ladi ❌
  • teglar.mahsulot_id — tegda faqat bitta mahsulot bo'ladi ❌

Ikkala tomon ham "ko'p". Bitta foreign key yetmaydi.

Yechim: oraliq (junction) jadval

Many-to-Many — uchinchi jadval orqali, ikkita One-to-Many ga bo'linadi:

CREATE TABLE teglar (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nom  VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE mahsulot_teglar (
    mahsulot_id INT UNSIGNED NOT NULL,
    teg_id      INT UNSIGNED NOT NULL,
    PRIMARY KEY (mahsulot_id, teg_id),
    FOREIGN KEY (mahsulot_id) REFERENCES mahsulotlar(id) ON DELETE CASCADE,
    FOREIGN KEY (teg_id)      REFERENCES teglar(id)      ON DELETE CASCADE
);

mahsulotlar          mahsulot_teglar           teglar
id | nom             mahsulot_id | teg_id      id | nom
2  | Samsung   ←──── 2           | 1  ───────→ 1  | yangi
               ←──── 2           | 2  ───────→ 2  | chegirma
               ←──── 2           | 3  ───────→ 3  | xit
5  | AirPods   ←──── 5           | 3  ──┘      4  | simsiz
               ←──── 5           | 4  ───────→
6  | Qopqoq    ←──── 6           | 2  ──┘

Har bir qator — bitta bog'lanish: "2-mahsulot 1-tegga ega".

Muhim tafsilotlar:

  • PRIMARY KEY (mahsulot_id, teg_id) — composite key. Bir mahsulotga bir tegni ikki marta qo'shib bo'lmaydi. Alohida id ustuni shart emas
  • ON DELETE CASCADE ikkala tomonda — mahsulot yoki teg o'chirilsa, bog'lanish ham o'chadi; mahsulotning o'zi esa teg o'chirilganda qoladi
  • Nomlash — ikki jadval nomidan: mahsulot_teglar, talaba_kurslar, foydalanuvchi_rollar

Ma'lumot qo'shish

INSERT INTO teglar (nom) VALUES ('yangi'), ('chegirma'), ('xit'), ('simsiz');

INSERT INTO mahsulot_teglar (mahsulot_id, teg_id) VALUES
    (2, 1), (2, 2), (2, 3),
    (5, 3), (5, 4),
    (6, 2);

So'rovlar

Mahsulot — teglari bilan

Ikki JOIN — oraliq jadval orqali:

SELECT p.nom, t.nom AS teg
FROM mahsulotlar p
JOIN mahsulot_teglar mt ON mt.mahsulot_id = p.id
JOIN teglar t           ON t.id = mt.teg_id
WHERE p.id = 2;

Bir qatorda — GROUP_CONCAT bilan:

SELECT p.nom, GROUP_CONCAT(t.nom ORDER BY t.nom SEPARATOR ', ') AS teglar
FROM mahsulotlar p
LEFT JOIN mahsulot_teglar mt ON mt.mahsulot_id = p.id
LEFT JOIN teglar t           ON t.id = mt.teg_id
GROUP BY p.id, p.nom;

+--------------------+---------------------+
| nom                | teglar              |
+--------------------+---------------------+
| iPhone 15          | NULL                |
| Samsung Galaxy A55 | chegirma, xit, yangi|
| MacBook Air M3     | NULL                |
| Lenovo IdeaPad     | NULL                |
| AirPods Pro        | simsiz, xit         |
| Qopqoq             | chegirma            |
| Sovg'a kartasi     | NULL                |
+--------------------+---------------------+

LEFT JOIN — tegsiz mahsulotlar ham chiqadi.

Teg bo'yicha mahsulotlar

SELECT p.nom, p.narx
FROM mahsulotlar p
JOIN mahsulot_teglar mt ON mt.mahsulot_id = p.id
JOIN teglar t           ON t.id = mt.teg_id
WHERE t.nom = 'chegirma';

Bir nechta tegning hammasiga ega mahsulotlar

"Ham xit, ham chegirma" — qiyin, lekin ko'p kerak bo'ladigan savol:

SELECT p.nom
FROM mahsulotlar p
JOIN mahsulot_teglar mt ON mt.mahsulot_id = p.id
JOIN teglar t           ON t.id = mt.teg_id
WHERE t.nom IN ('xit', 'chegirma')
GROUP BY p.id, p.nom
HAVING COUNT(DISTINCT t.id) = 2;
-- Samsung Galaxy A55

IN — "kamida bittasi bor"larni oladi. HAVING COUNT(...) = 2 — faqat ikkalasi ham borlarini qoldiradi. Bu naqsh internet do'konlardagi filtrlarda doim ishlatiladi.

Eng mashhur teglar

SELECT t.nom, COUNT(mt.mahsulot_id) AS mahsulotlar
FROM teglar t
LEFT JOIN mahsulot_teglar mt ON mt.teg_id = t.id
GROUP BY t.id, t.nom
ORDER BY mahsulotlar DESC;

Bog'lanishning o'z ma'lumoti bo'lsa

Ko'pincha bog'lanishning o'zi ma'lumotga ega. Talaba va kurs:

CREATE TABLE talaba_kurslar (
    talaba_id    INT UNSIGNED NOT NULL,
    kurs_id      INT UNSIGNED NOT NULL,
    yozilgan     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    progress     TINYINT UNSIGNED NOT NULL DEFAULT 0,
    yakuniy_ball TINYINT UNSIGNED NULL,
    PRIMARY KEY (talaba_id, kurs_id),
    FOREIGN KEY (talaba_id) REFERENCES talabalar(id),
    FOREIGN KEY (kurs_id)   REFERENCES kurslar(id)
);

progress va yakuniy_ball — talabaga ham, kursga ham tegishli emas. Ular aynan shu talabaning aynan shu kursdagi ma'lumoti. Ularning yagona to'g'ri joyi — oraliq jadval.

Aslida buyurtma_qatorlari ham shunday jadval: buyurtmalar ↔ mahsulotlar orasidagi Many-to-Many, o'z ma'lumoti (soni, narx) bilan. Bunday hollarda oraliq jadval o'z nomiga va o'z id siga ega bo'lgan to'laqonli "ob'ekt"ga aylanadi.

Indekslar

PRIMARY KEY (mahsulot_id, teg_id) — mahsulot_id bo'yicha qidiruvni tezlashtiradi ("bu mahsulotning teglari"). Lekin teskari savol — "bu tegdagi mahsulotlar" — teg_id bo'yicha. Composite indeks faqat chap ustundan boshlab ishlaydi (31-dars), shuning uchun teskari yo'nalish uchun alohida indeks kerak. Yaxshiyamki, FOREIGN KEY (teg_id) uni avtomatik yaratadi.

Nima uchun shunday?

Nima uchun teglarni JSON massiv sifatida saqlamaslik kerak — teglar JSON = ["xit", "chegirma"]? Oddiyroq-ku.

Haqiqatan oddiyroq — faqat yozish uchun. Lekin keyin: "chegirma tegini aksiya ga o'zgartirish" — har bir mahsulotning JSON ini o'zgartirish kerak. "xit tegi bilan nechta mahsulot bor" — butun jadvalni ko'rib chiqish. Xato yozilgan chegrima tegini hech kim to'xtatmaydi. Oraliq jadval bilan: teg nomi bitta joyda, foreign key noto'g'ri tegni qabul qilmaydi, indekslar ikkala yo'nalishda ishlaydi. JSON — tuzilmasi oldindan noma'lum, kam qidiriladigan ma'lumot uchun.

Xulosa

Eslab qoling:

  • Many-to-Many — ikkala tomonda ham "ko'p": mahsulot ↔ teg, talaba ↔ kurs
  • Oraliq (junction) jadval: ikki foreign key + PRIMARY KEY (a_id, b_id)
  • Nom: mahsulot_teglar; ON DELETE CASCADE — bog'lanish o'chadi, ob'ektlar qoladi
  • So'rov — oraliq jadval orqali ikki JOIN
  • "Hammasiga ega": WHERE ... IN (...) GROUP BY ... HAVING COUNT(DISTINCT) = n
  • Bog'lanishning o'z ma'lumoti (ball, soni, sana) — oraliq jadvalda
  • Teglarni vergul bilan yoki JSON da saqlamang

Keyingi dars

20-dars: One-to-One munosabat — qachon kerak