In de producten tabel staan de producten met uniek volgnummer, hetzelfde voor gebruikers. In de wijzigingen tabel wordt bijgehouden wanneer een gebruiker een product wijzigt. Nu wil ik graag een lijst publiceren met:
product, gebruiker, timestamp waarbij gebruiker en timestamp het jongste record voor dat product is, m.a.w. de gebruiker die het product het meeste recent heeft gewijzigd en de timestamp waarop dat gebeurde, gesorteerd op product.
De volgende query geeft wel het overzicht, daarbij is de datum echter de oudste (dus de eerste keer dat er een wijziging plaatsvond):
SELECT *
FROM producten
INNER JOIN wijzigingen ON producten.pid = wijzigingen.pid
INNER JOIN gebruikers ON wijzigingen.gid = gebruikers.gid
GROUP BY producten.pid
ORDER BY producten.product ASC, wijzigingen.timestamp DESC
Wat zie ik over het hoofd?
[size=xsmall]Toevoeging op 02/07/2021 09:07:31:[/size]
Wanneer ik het volgende wijzig in de query, gaat het zoals gehoopt:
SELECT producten.product, gebruikers.gebruiker, MAX(wijzigingen.timestamp) AS timestamp
?Onbekende gebruiker
02-07-2021 09:11
Je moet de hoogste timestamp meenemen in de JOIN-conditie:
SELECT *
FROM producten
INNER JOIN wijzigingen
ON producten.pid = wijzigingen.pid
AND wijzigingen.timestamp = (
SELECT MAX(w.timestamp)
FROM wijzigingen AS w -- AS keyword wordt niet door iedere database herkend
WHERE w.gid = wijzigingen.gid
AND w.pid = wijzigingen.pid)
INNER JOIN gebruikers
ON wijzigingen.gid = gebruikers.gid
GROUP BY
producten.pid
ORDER BY
producten.product ASC,
wijzigingen.timestamp DESC
Als het om auditing gaat is er meestal ook nodig om te weten wat er precies gewijzigd is, dat zou je ook nog in je databankontwerp mee kunnen nemen.
SELECT * en group-by laten zich slecht combineren.
Mogelijk is er een soort van Distinct effect dat dan optreedt, maar de velden kunnen nogal random verschijnen.
Group by gebruik je in combinatie met aggregatie functies. Zoals MAX().
Maar dan moet je alle velden vermelden die niet de aggregatie functie zijn.
Hier gebruik je niet eens een aggregatie functie, maar als je dan per se group-by wilt gebruiken, moet je alle velden (* dus dus alle velden uit producten en alle velden uit wijzigingen en uit gebruikers) moeten vermelden.
Daarmee haal je heel het effect dat group-by zou hebben onderuit, dus zonder aggregatie functie laat je group by achterwege.
-
in dit geval krijg je warschijnlijk maar 1 record uit wijzigingen terug, maar als er 2 wijzigingen gelijktijdig zijn opgeslagen krijg je er 2.
Daarmee zou je ook 2 gebruikers kunnen krijgen.
group-by in de query (in combinatie met een mysql die dat oogluikend toestaat) geeft je dan 1 set terug.
Maar welk record uit wijzigingen en welke gebruiker is een redelijke verrassing.
Kijk, als je die vraag al zelf gaat beantwoorden hoef ik dat niet meer te doen he.
Zoals je al aangeeft moet die weg. Het is een overblijfsel uit de query van de vraagsteller.
Dank voor de opmerkzaamheid.
Dacht dat ik dit had opgelost, maar nu blijkt in het overzicht toch nog altijd de verkeerde gebruiker te staan, namelijk die van de eerste wijziging. De query van Ad levert een overzicht met daarin de eerste log en niet de laatste.
Huidige query:
SELECT
producten.pid,
producten.product,
MAX(wijzigingen.timestamp) AS timestamp,
gebruikers.gebruiker
FROM producten
INNER JOIN wijzigingen ON producten.tid=wijzigingen.id
INNER JOIN gebruikers ON wijzigingen.zid=gebruikers.zid
GROUP BY producten.pid
ORDER BY producten.product
Klopt toch dat dit overzicht met 1 query te maken moet zijn?
Vreemd genoeg nu er meerdere log records bij zijn gekomen, werkt het toch niet zoals verwacht. In tegenstelling tot de eerste datum echter, verschijnen er nu dubbele records in de lijst waarbij van alle gebruikers de meeste recente wijziging wordt getoond.
waarschijnlijk omdat er meerdere lijntjes lopen tussen wijzingen en producten?
elke keer dat je een product wijzigt, komt dat id nog een keer in de tabel.
dus als 1 gebruiker een zeker product meermaals aanpast, krijg je die mogelijk dubbel terug?
(zou ik nog wel willen testen, maar dit is mijn eerste ingeving)
Iedere keer als een gebruiker een product wijzigt komt er een entry in de tabel wijzigingen (id, product id, gebruikers id, timestamp), dat klopt. De entries van gebruikers en producten zijn uiteraard uniek.
