SQL - Relatie met onbekende tabel
Deze objecten persist ik in de database met gebruik van Doctrine. Dat kon makkelijk met een ODM (NoSQL), maar nu moet ik er ook ORM (SQL) support voor hebben. Hoe pak ik zoiets aan?
Moet ik dan een "content_table" en "content_id" veld hebben en dan 2 queries in elkaar: 1 om de content_table op te halen en 1 om die dan te gebruiken in een JOIN?
Het Route en content object hebben een n-1 relatie. Elke Route heeft dus maar 1 Content object.
Stel de content kan wel alleen een Content object zijn, dan zou de tabel er zo uitzien: (gesimplificeerd)
routes
id
path
content_id
contents
id
body
Mijn situatie hier is dat de content_id niet perse de id van de tabel contents hoeft te zijn, het kan ook een record uit de topics tabel zijn.
Bij het inserten/updaten van de route weet je welk type het is, bij het ophalen niet.
Gewijzigd op 07/06/2014 11:42:00 door Wouter J
Je kan dan twee dingen doen, afhankelijk van hoe de gegevens aangemaakt worden. Als het content_id, bekeken over de verschillenden tabellen altijd uniek is, dan kan je 1 query bouwen waarin je elke aparte content tabel joined. Omdat er in de verschillende tabellen maar 1 kan zijn die voor dat id een record heeft kan je de juiste eruit halen via een COALESCE:
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT a.content_id, COALESCE(b.body, c.body, d.body) AS body
FROM routes a
LEFT JOIN content b ON a.content_id = b.content_id
LEFT JOIN topics c ON a.content_id = c.topic_id
LEFT JOIN messages d ON a.content_id = d.message_id
WHERE a.content_id = ...
FROM routes a
LEFT JOIN content b ON a.content_id = b.content_id
LEFT JOIN topics c ON a.content_id = c.topic_id
LEFT JOIN messages d ON a.content_id = d.message_id
WHERE a.content_id = ...
Als je echter dubbele ids over de verschillende tabellen hebt dan zal je het type op moeten slaan in de routes tabel en dat gebruiken bij de joins:
routes
id
path
content_id
content_type
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT a.content_id, COALESCE(b.body, c.body, d.body) AS body
FROM routes a
LEFT JOIN content b ON(
a.content_id = b.content_id
AND a.content_type = 1
)
LEFT JOIN topics c ON(
a.content_id = c.topic_id
AND a.content_type = 2
LEFT JOIN messages d ON(
a.content_id = d.message_id
AND a.content_type = 3
)
WHERE a.content_id = ...
FROM routes a
LEFT JOIN content b ON(
a.content_id = b.content_id
AND a.content_type = 1
)
LEFT JOIN topics c ON(
a.content_id = c.topic_id
AND a.content_type = 2
LEFT JOIN messages d ON(
a.content_id = d.message_id
AND a.content_type = 3
)
WHERE a.content_id = ...
Gewijzigd op 07/06/2014 12:59:18 door Erwin H
ORM staat toch voor Object Relation Manager
Dus dat zou in Doctrine ORM geregeld moeten worden
Ger van Steenderen:
Dus dat zou in Doctrine ORM geregeld moeten worden
Maar een ORM heeft mapping data nodig zodat hij weet hoe hij het moet regelen. Zoiets als dit is niet op te vangen in deze mapping data, dus zul je moeten werken met DQL (de ORM versie van SQL).
Erwin H:
Als je echter dubbele ids over de verschillende tabellen hebt dan zal je het type op moeten slaan in de routes tabel en dat gebruiken bij de joins:
Ah, op die fiets :) Maar op die manier moet je dus bij het aanmaken van de query weten welke typen content objecten je allemaal hebt. Dat kan opzich wel, maar de code die ik schrijf is bedoelt voor het CMF project, een open source project dat gebruikt wordt in vele verschillende applicaties. Het zou dus mooier zijn als er een hele generieke manier is. Maar die is er dus niet? In dat geval moeten we gewoon leven met de minder generieke manier en het overlaten aan de gebruiker om de query op te stellen.
Ik dacht zelf aan iets als:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
SELECT c.*
FROM routes AS r
INNER JOIN (
SELECT content_table
FROM routes
WHERE path = '/blog/my-first-blogpost'
) AS c
ON c.id = r.content_id
WHERE r.path = '/blog/my-first-blogpost';
FROM routes AS r
INNER JOIN (
SELECT content_table
FROM routes
WHERE path = '/blog/my-first-blogpost'
) AS c
ON c.id = r.content_id
WHERE r.path = '/blog/my-first-blogpost';
Maar dat lijkt helaas niet echt te werken...
Maar dan kan je net zo makkelijk twee aparte queries uitvoeren.
Wouter, kan je dan niet beter de object naam of pad in de routes tabel opslaan, en aan de hand daarvan de DQL opbouwen?
Ger van Steenderen op 08/06/2014 21:18:05:
Het kan wel via een SQL prepare omweg.
Hoe zou jij dat doen dan?
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT content_id, content_table INTO @c_id, @c_table FROM routes
WHERE path = '/blog/my-first-blogpost';
SET @q = CONCAT('SELECT * FROM ', @c_table, ' WHERE id = ?');
PREPARE st_content FROM @q;
EXECUTE st_content USING @c_id;
WHERE path = '/blog/my-first-blogpost';
SET @q = CONCAT('SELECT * FROM ', @c_table, ' WHERE id = ?');
PREPARE st_content FROM @q;
EXECUTE st_content USING @c_id;
Zoals ik al zei, een omweg ...
Gewijzigd op 08/06/2014 21:43:51 door Ger van Steenderen