[SQL] Query (info uit 5 tabellen) lukt niet

[Reveller]

code:

nodes    users   categories   stories   comments
-----    -----   ----------   -------   --------
nid      uid     cid          nid       cid
title    name    url          body      body
created                       category
uid
type

In de tabel nodes houd ik alle content bij die aan de website wordt toegevoegd. Momenteel zijn er meerdere content typen, waaronder 'story' (staan in de stories tabel) en 'comment' (staan in de comments tabel). (De nid uit de nodes tabel correspondeert met de cid uit de comments tabel) De gebruiker kan zelf verschillende categorien stories aanmaken, bijvoorbeeld 'persberichten', 'recepten' en 'analyses'. Nu wil ik een chronologisch overzicht maken van alle content die aan de website werd toegevoegd. Momenteel lees ik alle stories uit:

SQL:

SELECT n.title, n.created, n.uid, s.*, u.name, c.url
FROM nodes n
LEFT JOIN stories s ON s.nid = n.nid
LEFT JOIN users u ON u.uid = n.uid
LEFT JOIN categories c ON c.cid = s.category
WHERE n.type = 'story'

Hier mee maak ik het volgende overzicht:

code:

Titel           Categorie      Created     Auteur
-------------------------------------------------
NS-Quiz met...  Persberichten  2 mei 2006  Admin
Ik heb toen...  Weblog         1 mei 2006  Peer
Zandtaartjes..  Recepten       5 feb 2006  Erik

Nu wil ik graag de comment aan dit overzicht toevoegen. Met andere woorden:

code:

Titel           Categorie      Created     Auteur
-------------------------------------------------
Hulde dat jij.  Comments       5 mei 2006  Admin
NS-Quiz met...  Persberichten  2 mei 2006  Admin
Ik heb toen...  Weblog         1 mei 2006  Peer
Ik vind dat...  Comments       8 mrt 2006  Dave
Zandtaartjes..  Recepten       5 feb 2006  Erik

Ik krijg alleen de query hiervoor niet goed. Ik heb nu dit:

SQL:

SELECT n.title, n.created, n.uid, s.*, u.name, c.url, m.*
FROM nodes n
LEFT JOIN stories s ON s.nid = n.nid
LEFT JOIN users u ON u.uid = n.uid
LEFT JOIN categories c ON c.cid = s.category
LEFT JOIN comments m ON m.cid = n.nid
WHERE n.type = 'story' OR n.type = 'comment'

Dit werk alleen niet helemaal. Ik krijg wel een resultset, maar zo krijg ik de verkeerde namen bij de comments. Ook heeft geen enkele comment een nod (node-id) en body.

Ik hoop dat deze informatie genoeg is om mij te zeggen wat er voud is aan deze query Alle hulp is welkom!

[JaQ]

Qua performance raad ik je die "or" ten zeerste af (trek maar eens een executie plan, je zal schrikken ). Door "in" te gebruiken kan je dit voorkomen. Onderstaande is m.i. dezelfde query:

SQL:

select n..titel,n.created,n.uid,s.*,u.uname,c.url,m.*
  from nodes n, stories s, users u, categories c, comments m
 where s.nid = n.nid
    and u.uid = n.uid
    and c.cid = s.category
    and m.cid = n.nid
    and n.type in ('story', 'comment')    

Nu terug naar je originele probleem; de comments. Zoals je design er nu uit ziet, heb je de node id als sleutel overgenomen in comments en cid genoemd. Klopt dit? Zo ja, heb je die cid primary sleutel gemaakt? Dan kan je namelijk net zo goed comment als niet verplicht veld in nodes opnemen. Zo nee: je kan beter een nieuwe kolom opnemen met daarin het node id in je comments tabel.

Als deze query je nog steeds niet dezelfde gegevens opleverd, dan kan het volgens je bedrijfsregels dus voorkomen dat er nodes zijn zonder comment. Op zich prima, maar dan moet je een outer join leggen. (zie de manual van het RDBMS dat je gebruikt).

Ik hoop dat dit je op de juiste weg stuurt.

[Reveller]

JaQ schreef op zondag 01 oktober 2006 @ 17:27:
Qua performance raad ik je die "or" ten zeerste af

Veranderd in 'in'

Nu terug naar je originele probleem; de comments. Zoals je design er nu uit ziet, heb je de node id als sleutel overgenomen in comments en cid genoemd. Klopt dit? Zo ja, heb je die cid primary sleutel gemaakt?

Ja, ja

Dan kan je namelijk net zo goed comment als niet verplicht veld in nodes opnemen. Zo nee: je kan beter een nieuwe kolom opnemen met daarin het node id in je comments tabel.

Tussen nodes en comments zit een 1-op-oneindig relatie: er kunnen meerdere comments op een node gegeven worden. Sterker nog: een comment is zelf ook een node, dus comments op comments (net als bij tweakers) is ook mogelijk. De 'cid' (comment-id) in de comments tabel komt overeen met de nid (node-id) in de nodes tabel.

Als deze query je nog steeds niet dezelfde gegevens opleverd, dan kan het volgens je bedrijfsregels dus voorkomen dat er nodes zijn zonder comment. Op zich prima, maar dan moet je een outer join leggen. (zie de manual van het RDBMS dat je gebruikt).

Dat snap ik niet: die relatie is niet nodig ihmo. Ik wil alleen een chronologische lijst weergeven van alle nodes (uit de stories en comments tabellen), zonder een relatie tussen nodes en comments te leggen.

[whoami]

Qua performance raad ik je die "or" ten zeerste af (trek maar eens een executie plan, je zal schrikken ). Door "in" te gebruiken kan je dit voorkomen. Onderstaande is m.i. dezelfde query:

Hmm, dit is gewoon niet waar.
Een IN is gewoon niets anders dan een OR; check het exec plan maar.

Op zich prima, maar dan moet je een outer join leggen

Dat doet hij; een Left join is een outer join.

Om het over performance te hebben: ben je wel zeker dat je overal die LEFT join nodig hebt ? Is een INNER join niet voldoende ?

Om het over je probleem te hebben: waar leg je vast welk comment er bij wat hoort ?
Aan wat kan je een comment trouwens hangen ? Ik zie dat je die joined aan nodes, maar ik zie nergens een relatie tussen node en comment ?

code:

LEFT JOIN comments m ON m.cid = n.nid

Je joined hier de 2 primary keys met elkaar ?? Heb je wel foreign key constraints op je tabellen ? Heb je al eens je tabellen in een ERD uitgetekend ?

Tussen nodes en comments zit een 1-op-oneindig relatie: er kunnen meerdere comments op een node gegeven worden. Sterker nog: een comment is zelf ook een node, dus comments op comments (net als bij tweakers) is ook mogelijk. De 'cid' (comment-id) in de comments tabel komt overeen met de nid (node-id) in de nodes tabel.

Wat is dan je primary key veld in je comments table ?

zonder een relatie tussen nodes en comments te leggen.

Als je geen relatie daartussen legt, hoe wil je dan uberhaupt de gerelateerde comments van een node ophalen ?

Hetzelfde geldt voor stories trouwens; waarom heb je daar geen PK ?

Trouwens:

voud

fout.

[ Voor 3% gewijzigd door whoami op 01-10-2006 23:36 ]