join met nieuwste record
producten: pid, product
gebruikers: gid, gebruiker
wijzigingen: wid, pid, gid, timestamp
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):
Code (php)
1
2
3
4
5
6
2
3
4
5
6
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
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?
Toevoeging op 02/07/2021 09:07:31:
Wanneer ik het volgende wijzig in de query, gaat het zoals gehoopt:
Code (php)
1
SELECT producten.product, gebruikers.gebruiker, MAX(wijzigingen.timestamp) AS timestamp
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
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.
Waarom staat er group by in de query?
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.
https://www.phphulp.nl/php/tutorial/databases/group-by/846/
Ivo P op 05/07/2021 19:45:57:
Waarom staat er group by in de query?
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.
Huidige query:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
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
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?
Code (php)
1
GROUP BY producten.pid, producten.product, gebruikers.gebruiker
zodat _alle_ kolommen mee doen met het "groeperen" en niet at random een overige kolom wordt toegevoegd
Gewijzigd op 07/09/2021 21:11:46 door Ivo P
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)
4,product 1,2021-09-14 14:59:16,gebruiker 2
4,product 1,2021-07-30 09:29:06,gebruiker 1
Ik begrijp hieruit dat je de recentste aanpassing zoekt per product en dan wil weten welke gebruiker dat gedaan heeft?
Maar ik doorzie de datastructuur niet helemaal:
staat in de tabel Wijzigingen nu ergens het productID?
Of bij Producten het tid wat een verwijzing is naar de (laatste) wijziging?
producten: pid, product
gebruikers: gid, gebruiker
wijzigingen: wid, pid, gid, timestamp
In de wijzigingen tabel wordt dus opgeslagen welk product (pid) door welke gebruiker (gid) op welk moment (timestamp) is gewijzigd.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
SELECT p.pid, p.product,
subq.laatstetime,
g.gebruiker
FROM producten p
JOIN (SELECT MAX(wijzigingen.timestamp) AS laatstetime,
wijzigingen.pid
FROM wijzigingen
GROUP BY wijzigingen.pid ) AS subq
ON subq.pid = p.pid
JOIN wijzigingen w ON w.timestamp = subq.laatstetime AND w.pid = p.pid
JOIN gebruikers g ON g.gid = w.gid
subq.laatstetime,
g.gebruiker
FROM producten p
JOIN (SELECT MAX(wijzigingen.timestamp) AS laatstetime,
wijzigingen.pid
FROM wijzigingen
GROUP BY wijzigingen.pid ) AS subq
ON subq.pid = p.pid
JOIN wijzigingen w ON w.timestamp = subq.laatstetime AND w.pid = p.pid
JOIN gebruikers g ON g.gid = w.gid
Toevoeging op 15/09/2021 12:21:54:
in de post van 7 sept gebruik je ook nog tid en zid
TIP:
gebruik namen voor de kolommen die ook over een jaar nog begrijpelijk zijn.
Ik heb ooit moeten werken in een berg code waar het wemelde van de gid, tid, docid, ldocid etc.
Het kost weinig om de kolom gid gewoon gebruiker_id te noemen. Want op een gegeven moment komt er nog een id kolom bij voor een tabel die ook met een G begint.
Toevoeging op 15/09/2021 13:08:53:
mischien nog even een uitleg erbij:
De subquery verzameld de recentste wijziging per productid.
Dat gejoind met producten levert je dan de recentste wijziging bij je product.
Daarna kijk ik weer naar de tabel wijzigingen als geheel.
Daarbij pak ik de in de subquery gevonden "laatstetime" om het betreffende record in z'n geheel te vinden.
En om te voorkomen dat er 2 producten gelijktijdig zijn aangepast en dit in de soep loopt, kan deze join-voorwaarde nog uitgebreid worden met :
AND w.pid = p.pid
Als het goed is, hebben we nu precies 1 record in Wijzigingen te pakken.
Daar wordt dan over gid nog de tabel Gebruikers tegenaan gejoind, zodat we ook de gebruiker die de laatste aanpassing deed zullen vinden.
--
edit: tekst wat ingekort en AND w.pid = p.pid in de query toegevoegd.
Gewijzigd op 15/09/2021 14:44:26 door Ivo P