[SQL]Tabellen combineren met 1:M relaties

[eghie]

Ik zit een beetje met een redelijk "complex" probleem, tenminste ik heb zelf niet zoveel ervaring om dit probleem alleen aan te pakken, omdat ik ook niet weet hoe ik hier een oplossing moet vinden.

We nemen hier even een fictieve situatie, aangezien mijn situatie iets teveel om uitleg vraagt. Uiteraard hoef ik hier geen uitleg over de database structuur, aangezien dit alleen het probleem schetst en mijn database het probleem ook bevat, maar wel een goede structuur bevat.

Stel, je hebt de volgende tabellen:

Gebruikers
ID	Naam
1	Gebruikertje
2	Hans
3	Kees

gebruikers_acties
gebruikerID	actieID
1	1
1	2
2	3
2	4
2	5

Acties
ID	Titel	Datum
1	Actie oud	2007-06-10 11:30:00
2	Actie nieuw	2007-07-21 10:30:00
3	Actie neutraal	2007-06-10 11:30:00
4	Actie oud	2007-03-05 17:30:00
5	Actie nieuwste	2007-11-15 13:00:00

Nu wil ik gebruikers en acties met elkaar in 1 overzicht hebben. Er zit echter wel een voorwaarde aan, dat voor elke gebruiker alleen de laatste actie erbij moet staan. Hierbij bedoel ik de laatste dmv de datum, dus niet het ID. Als de gebruiker geen actie heeft dan moet hij er ook bij staan, maar dan met die velden leeg.

In dit geval zou het de volgende resultaat moeten leveren:

Acties van gebruikers
Gebruiker	Titel actie	Datum
Gebruikertje	Actienieuw	2007-07-21 10:30:00
Hans	Actie nieuwste	2007-11-15 13:00:00
Kees

Ik wil dit echter met 1 query doen, is dit mogelijk?

SQL:

SELECT Gebruikers.Naam, Berichten.Titel, Berichten.Datum
FROM gebruiker_berichten
RIGHT JOIN Gebruikers ON gebruikers_acties.gebruikerID = Gebruikers.ID
LEFT JOIN Acties ON gebruikers_acties.berichtID = Acties.ID

Verder dan dat kom ik niet, omdat ik niet weet wat ik dan moet doen. Nu heb ik alle regels te pakken uit beide tabellen, maar ik wil voor elke gebruiker 1 regel en die zit ook nog aan een voorwaarde vast, namelijk dat het de laatste is, als die gesorteerd zou zijn op datum.

Hoe krijg ik dat dus voor elkaar in 1 query?

Ik doe nu dmv PHP 2 query's combineren. Dit gaat ook wel goed, maar ik wil de query kunnen ordenen vanuit de database. Dit heeft te maken met dat ik een abstractie laag gebruik met een datagrid. Ik wil die datagrid kunnen ordenen, maar die datagrid heeft ook nog paging. Dus ik wil onafhankelijk van die paging kunnen ordenen op alle pagina's. Maar het moet qua performance ook goed lopen bij 1000en records. Dus die paging moet al gebeuren bij de query (LIMIT enzo). Daarom wil ik het dus in 1 query regelen, want dan kan ik de ordening op beide tabellen gewoon in de query regelen.

[LuCarD]

Waaromm heb je geen gebruikers_id in je berichten tabel?

Of kunnen berichten ook meerdere gebruikers hebben?

[Zr40]

Waar heb je de gebruiker_berichten tabel voor nodig? Tenzij een bericht door meerdere gebruikers geplaatst kan worden, is normaal gesproken een gebruiker veld in de berichten tabel voldoende.

[eghie]

Hehe, oeps. Inderdaad, in deze situatie heb ik toch wat een verkeerde casus genomen. Ga er maar even van uit dan dat een bericht ook meerdere gebruikers kan hebben. Laten we van berichten maar even acties maken, en dat een bepaalde actie bij meerdere gebruikers hoort.

Het gaat mij er vooral om, hoe ik dit type probleem aanpak.

[ Voor 34% gewijzigd door eghie op 24-09-2007 12:10 ]

[_js_]

Dat gaat moeilijk met alleen maar joins, handiger is een subquery gebruiken.

Iets als:

SQL:

SELECT ...
FROM acties
JOIN gebruiker_acties ga ON...
JOIN ...
WHERE datum = (
  SELECT MAX(datum)
  FROM acties
  JOIN gebruiker_acties ga2 ON ...
  WHERE ga.gebruikerId = ga2.gebruikerId
)

[Grijze Vos]

_js_ schreef op maandag 24 september 2007 @ 13:50:
Dat gaat moeilijk met alleen maar joins, handiger is een subquery gebruiken.

Kan het niet met 'having' ? Staat me zoiets van bij, maar heb even niet mijn boeken bij de hand hier.

[Voutloos]

Grijze Vos schreef op maandag 24 september 2007 @ 13:54:
[...]
Kan het niet met 'having' ? Staat me zoiets van bij, maar heb even niet mijn boeken bij de hand hier.

Having icm MAX() in dit geval.

[Grijze Vos]

Voutloos schreef op maandag 24 september 2007 @ 14:07:
[...]
Having icm MAX() in dit geval.

idd, iets als HAVING datum = MAX(datum).

(of quantificeer je dan over alle data ipv alleen over die data die betrekking hebben op die bepaalde user, ik weet het niet zeker.)

[ Voor 25% gewijzigd door Grijze Vos op 24-09-2007 14:28 ]

[eghie]

Grijze Vos schreef op maandag 24 september 2007 @ 14:27:
[...]

idd, iets als HAVING datum = MAX(datum).

(of quantificeer je dan over alle data ipv alleen over die data die betrekking hebben op die bepaalde user, ik weet het niet zeker.)

Ah, inderdaad. HAVING was nog niet eens bij me opgekomen. Komt ook omdat ik het zowat nooit gebruik.

Bij HAVING geeft je trouwens je voorwaarden op basis van de GROUP BY, dus hij doet eerst een groupby en dan van het veld waarop je groeppeert pakt hij er een aantal uit aan de hand van de voorwaarden. Dan zou het iets van dit worden:

SQL:

GROUP BY Gebruikers.ID HAVING MAX(Acties.Datum)

Dit is een vermoeden, maar ik ga het wel even testen zo.

[ Voor 9% gewijzigd door eghie op 24-09-2007 15:33 ]

[_js_]

HAVING werkt nadat de GROUP BY is gedaan, als je het datum veld zonder aggregate functie opgeeft in de having, dan ben je verplicht group by te doen op datum, en dan heb je er dus niets meer aan om datum=max(datum) te vragen, want dat geldt voor elke rij.

MySQL zal misschien geen foutmelding geven, maar het zal ook niet de juiste gegevens teruggeven.