[MySQL] Informatie uit 4 tabellen halen lukt niet helemaal

[Reveller]

Ik heb de volgende tabellen:

code:

nodes     pages       users    nodes_roles
-----     -----       -----    -----------
nid       nid         uid      nid
type      level       name     rid
title     weight
created   alias
uid       pid

In de tabel 'nodes' wil ik een overzicht geven van alle content die door gebruikers is aangemaakt. Dit kan bijvoorbeeld een pagina, artikel, forum post of plaatje zijn. In dit geval wil ik informatie ophalen van alle pagina's ('pages'). Ook wil ik uit de tabel nodes_roles alle rid's (rol-id's) ophalen die toegang hebben tot een bepaalde pagina (relatie nodes.nid - nodes_roles.nid is 1 op veel). De uiteindelijke resultset moet dus zoiets opleveren als

code:

nid type title     created    uid uname    level weight alias     pid rid
15  page support   1121015767 1   Admin    1     1      support   1   2
17  page Vacatures 1112027143 2   Reveller 3     4      vacatures 10  2
17  page Vacatures 1112027143 2   Reveller 3     4      vacatures 10  5
17  page Vacatures 1112027143 2   Reveller 3     4      vacatures 10  6
18  page contact   1121026884 1   Admin    2     1      contact   3   2
18  page contact   1121026884 1   Admin    2     1      contact   3   5

Ik probeer dit te bewerkstelligen met de volgende query:

SQL:

SELECT n.nid, n.type, n.title, n.created, n.uid, p.level, p.weight, p.alias, p.pid, u.name
FROM nodes n, pages p, users u
LEFT JOIN nodes_roles nr ON (n.nid = nr.nid)
WHERE n.type = 'page' AND n.uid = u.uid
ORDER BY p.pid, p.weight ASC

Maar nu krijg ik:

code:

nid type title     created    uid name    level weight alias     pid rid
1   page Home      1121015453 1   Admin    0     1                0   1
15  page support   1121015767 0   Anoniem  1     1      support   1   2
17  page Vacatures 1112027143 0   Anoniem  3     4      vacatures 10  2
17  page Vacatures 1112027143 0   Anoniem  3     4      vacatures 10  5
17  page Vacatures 1112027143 0   Anoniem  3     4      vacatures 10  6
18  page contact   1121026884 0   Anoniem  2     1      contact   3   2
18  page contact   1121026884 0   Anoniem  2     1      contact   3   5

Met andere woorden - er worden een aantal zaken goed door elkaar gegooid. Wie kan mij helpen deze query te verbeteren? Ik zit er nu al uren op en kom maar niet verder. Informatie uit twee tabellen halen lukt nog wel, maar omdat ik met 4 tabellen zit raak ik telkens in de war.

[ Voor 7% gewijzigd door Reveller op 07-08-2006 14:41 ]

[Tijgertje84]

bij je pages gebruik je ook een nid
ik denk dat je daar iets vergeet
want ik neem aan dat deze hetzelfde zal moeten zijn als n.nid??

[Reveller]

Tijgertje84 schreef op maandag 07 augustus 2006 @ 11:48:
bij je pages gebruik je ook een nid
ik denk dat je daar iets vergeet
want ik neem aan dat deze hetzelfde zal moeten zijn als n.nid??

Dat klopt. De relatie is n.nid = p.nid. Het idee is dat de nodes tabel een index tabel is van alle content in de database. elk content type heeft daarnaast een eigen tabel voor verdere informatie, dus:

code:

nodes     pages       images   weblog_posts   documents
-----     -----       -----    ------------   ---------
nid       nid         nid      nid            nid
type      level       size     body           size
title     weight      mime     comments       mime
created   alias                trackback      folder
uid       pid

Een voorbeeld: stel dat in de nodes tabel staat:

code:

nid type     title        created    uid
23  image    plaatje.jpg  1121026884 3 
24  page     Contact us   1112027143 2
25  document contract.doc 1112054358 2

Node nummer 23 is dus een plaatje. Als ik meer info wil over het plaatje, ga ik naar de images-tabel:

code:

nid size  mime       folder
23  43243 image/jpeg images/icons

Etcetera. Het voordeel van dit systeem is volgens mij dat ik in 1 oogopslag kan zien welke content door welke user is toegevoegd. Ook is het heel makkelijk bij te houden hoeveel content er op een dag is toegevoegd etc. Nadeel is dat je wat lastiger queries krijgt om informatie over bijvoorbeeld 1 of een aantal 'pages' op te vragen, omdat de informatie daarover in twee tabellen verspreid staat. Vandaar mijn vraag uit de TS

[ Voor 32% gewijzigd door Reveller op 07-08-2006 12:15 ]

[EdwinG]

In je Query uit de TS ben je in ieder geval nr.rid vergeten. (Anders kun je die node_roles net zo goed weglaten)

Verder, p.pid is bij elke rij 0, terwijl de rest van de waardes uit die tabel wel lijken te kloppen. Staat die kolom wel goed in de tabel?

[Reveller]

Die nr.rid kwam ik net ook achter. Ik heb nu deze query (ik haal iets andere kolommen op maar dat maakt voor het idee niet uit):

SQL:

SELECT n.nid, n.uid, n.title, n.created, p.pid, p.alias, u.name, nr.rid
FROM nodes n, users u
LEFT  JOIN pages p ON n.nid = p.nid
LEFT  JOIN nodes_roles nr ON n.nid = nr.nid
WHERE n.type =  'page' AND n.uid = u.uid
ORDER BY p.pid, p.weight ASC 

Hiermee lukt het al aardig, maar de n.uid die ik terugkrijg klopt niet. Zo staat bij mij in de tabel 'nodes' dat bij nid = 47, de uid (user-id; user die de pagina gecreerd heeft) 2 is. In het resultaat van mijn query staat dat 'ie 0 is?

[EdwinG]

Zit de fout dan niet in het stuk code dat de weergave regelt? Dat de sql-query wel de juiste resultaten teruggeeft, maar de weergave gewoon foutloopt?

[Reveller]

EdwinG schreef op maandag 07 augustus 2006 @ 14:11:
Zit de fout dan niet in het stuk code dat de weergave regelt? Dat de sql-query wel de juiste resultaten teruggeeft, maar de weergave gewoon foutloopt?

Lijkt me sterk. Wordt weergegeven door PHPMyAdmin en die heeft bij mij nog nooit een query voud weergegeven

offtopic:
Waarom zet Google hieronder de advertentie "Verleid iedere vrouw"? Tot zover context-based adverteren

[ Voor 15% gewijzigd door Reveller op 07-08-2006 14:20 ]

[EdwinG]

Als de username goed wordt weergegeven, is de juiste waarde van n.uid ook opgehaald in de query.
Probeer anders een u.uid in plaats van n.uid

[Reveller]

Die naam wordt dus ook niet goed opgehaald. De naam is overal 'anoniem' wat opzich wel overeenkomt met uid = 0. Maar slechts enkele nodes hebben als uid, 0. Ik hoop dat iemand met een briljante opmerking komt, want ik zie het gewoon niet meer.

[ Voor 21% gewijzigd door Reveller op 07-08-2006 14:26 ]

[EdwinG]

Reveller schreef op maandag 07 augustus 2006 @ 11:43:

code:

nid type title     created    uid uname    level weight alias     pid
15  page support   1121015767 1   Admin    1     1                0  
17  page Vacatures 1112027143 0   Reveller 3     4                0  
17  page Vacatures 1112027143 0   Reveller 3     4                0  
17  page Vacatures 1112027143 0   Reveller 3     4                0  
18  page contact   1121026884 1   Admin    2     1                0  
18  page contact   1121026884 0   Admin    2     1                0

Hier staan de namen anders wel goed, terwijl UID niet klopt.

// Wat te denken van
LEFT JOIN users AS u ON u.uid = n.uid

[geenstijl]

code:

SELECT

nodes.nid,
nodes.type,
nodes.title,
nodes.created,
nodes.uid,
USERS.name,
pages.level,
pages.weight,
pages.alias,
pages.pid,
ROLES.rid

FROM nodes
INNER JOIN users AS USERS
    ON USERS.uid = nodes.uid
INNER JOIN nodes_roles AS ROLES
    ON ROLES.nid = nodes.nid
LEFT JOIN pages
    ON pages.nid = nodes.nid
WHERE nodes.type = 'page'

(ik ga ervanuit dat je uid 0 dan ook in je user-tabel hebt staan?)

Misschien zie ik iets over het hoofd, maar bovenstaande zou toch moeten werken?
Hou er ook rekening mee dat waarschijnlijk alias en type "reserved words" zijn

edit:
EdwinG: je hebt gelijk, ik was er altijd stellig van overtuigd

[ Voor 8% gewijzigd door geenstijl op 07-08-2006 15:15 ]

[EdwinG]

geenstijl schreef op maandag 07 augustus 2006 @ 14:39:
Hou er ook rekening mee dat waarschijnlijk alias en type "reserved words" zijn

http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html
Ik zie ze niet in de tabel staan?

[Reveller]

EdwinG schreef op maandag 07 augustus 2006 @ 14:29:
[...]Hier staan de namen anders wel goed, terwijl UID niet klopt.

Dat was mijn fout. Heb de TS inmiddels aangepast. De output met die functie is:

code:

nid type title     created    uid name    level weight alias     pid rid
1   page Home      1121015453 1   Admin    0     1                0   1
15  page support   1121015767 0   Anoniem  1     1      support   1   2
17  page Vacatures 1112027143 0   Anoniem  3     4      vacatures 10  2
17  page Vacatures 1112027143 0   Anoniem  3     4      vacatures 10  5
17  page Vacatures 1112027143 0   Anoniem  3     4      vacatures 10  6
18  page contact   1121026884 0   Anoniem  2     1      contact   3   2
18  page contact   1121026884 0   Anoniem  2     1      contact   3   5

// Wat te denken van
LEFT JOIN users AS u ON u.uid = n.uid

In welke query? Als je in onderstaande bedoelt, is het resultaat hetzelfde

SQL:

SELECT n.nid, n.type, n.title, n.created, n.uid, p.level, p.weight, p.alias, p.pid, u.name 
FROM nodes n, pages p, users u 
LEFT JOIN nodes_roles nr ON (n.nid = nr.nid) 
WHERE n.type = 'page' AND n.uid = u.uid 
ORDER BY p.pid, p.weight ASC

geenstijl schreef op maandag 07 augustus 2006 @ 14:39:
<query>[/code]
(ik ga ervanuit dat je uid 0 dan ook in je user-tabel hebt staan?)

Ja, uiteraard

Misschien zie ik iets over het hoofd, maar bovenstaande zou toch moeten werken?
Hou er ook rekening mee dat waarschijnlijk alias en type "reserved words" zijn

Geeft dezelfde fout als de queries hierboven: de uid is telkens nul en name dus anoniem. Bijzonder: met alle queries komt dit probleem voor, behalve bij de node met id 1 (Home). Hierbij is in de resultset de uid 1 en name Admin (wat ook klopt met de usertabel). Bij alle andere nodes geeft 'ie uid 0 en name anoniem terug

Probleem zit hem niet in de reserved words. Is met andere queries op dezelfde tabellen nooit een probleem geweest en in de comments staat ook dat MySQL er niet over valt als je er een tabelnaam voor zet: 'p.alias' ipv 'alias'.

[geenstijl]

Geef dan eens de gegevens die momenteel in de tabellen staan en de output zoals je die verkrijgt door de door mij geposte query?

Toevallig niet iets fout met de data-types van de naar elkaar verwijzende kolommen:m.a.w. zijn die hetzelfde?

[ Voor 30% gewijzigd door geenstijl op 07-08-2006 14:57 . Reden: data-type gedeelte toegevoegd ]

[Dido]

Probeer, op zijn minst voor het overzicht, eens consequent alle joins expliciet, of alle joins impliciet te doen. Je gebruikt nu beiden, en dat leest iig al niet makkelijk

[Reveller]

geenstijl schreef op maandag 07 augustus 2006 @ 14:55:
Geef dan eens de gegevens die momenteel in de tabellen staan en de output zoals je die verkrijgt door de door mij geposte query?

Verdorie, mijn fout! In de tabel 'nodes' staat voor elke node de nid op 0. Ik had een testdatabase gemaakt om een paar dingen uit te proberen, en ben daarbij vergeten die nid's op 1, 2 etc. te zetten. Excuses! Ik ga de query zo netjes mogelijk maken en zal hem nog eens hier zetten Bedankt hoor, voor jullie hulp!

[geenstijl]

Overkomt iedereen wel eens
Waarschijnlijk krijg je nu wel de juiste gegevens terug.

[ Voor 50% gewijzigd door geenstijl op 07-08-2006 15:08 ]

[Reveller]

Dido schreef op maandag 07 augustus 2006 @ 15:03:
Probeer, op zijn minst voor het overzicht, eens consequent alle joins expliciet, of alle joins impliciet te doen. Je gebruikt nu beiden, en dat leest iig al niet makkelijk

Ik had daar nog nooit van gehoord / over nagedacht, maar ik stel me voor dat je dit bedoelt?

Impliciet

SQL:

SELECT n.nid, n.uid, n.title, n.created, p.pid, p.alias, u.name, nr.rid 
FROM nodes n, users u, pages p, nodes_roles nr
WHERE u.uid = n.uid AND n.nid = p.nid AND n.nid = nr.nid AND n.type = 'page'
ORDER BY p.pid, p.weight ASC

Expliciet

SQL:

SELECT n.nid, n.uid, n.title, n.created, p.pid, p.alias, u.name, nr.rid 
FROM nodes n
LEFT JOIN users u ON u.uid = n.uid
LEFT JOIN pages p ON n.nid = p.nid 
LEFT JOIN nodes_roles nr ON n.nid = nr.nid 
WHERE n.type =  'page'
ORDER BY p.pid, p.weight ASC

Wat is overigens beter / sneller / netter?

[Dido]

Reveller schreef op maandag 07 augustus 2006 @ 15:13:
Ik had daar nog nooit van gehoord / over nagedacht, maar ik stel me voor dat je dit bedoelt?

yupz

Wat is overigens beter / sneller / netter?

Geen idee wat sneller is (ik denk niet dat het heel veel uitmaakt, maar voor zover ik weet wordt expliciet als "netter" gezien: het heeft als voordeel dat je je join-condities duidelijk gescheiden houdt van je echte recordselectie aan de hand van parameters.

Ik heb vaak genoeg moeten zoeken in where clauses als

code:

where a.id=b.id and a.id in (select c.id from anderetabel c 
where c.naam like '%blaat%' or vrijdag = maandag) and ((d.veld >= f.veld) 
or (g.veld <= f.veld) and d.id=z.id and c.gid=g.id and f.kleur = 'blauw'

En daar word je niet vroilijk van