join met nieuwste record

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Veur Heur

Veur Heur

02/07/2021 08:33:59
Quote Anchor link
Ik heb 3 tabellen (vereenvoudigd):

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)
PHP script in nieuw venster Selecteer het PHP script
1
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


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)
PHP script in nieuw venster Selecteer het PHP script
1
SELECT producten.product, gebruikers.gebruiker, MAX(wijzigingen.timestamp) AS timestamp
 
PHP hulp

PHP hulp

19/03/2024 10:08:33
 
Ad Fundum

Ad Fundum

02/07/2021 09:11:33
Quote Anchor link
Je moet de hoogste timestamp meenemen in de JOIN-conditie:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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

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.
 
Veur Heur

Veur Heur

02/07/2021 09:13:02
Quote Anchor link
@Ad, ik sla inderdaad de wijziging op, maar zoals aangegeven is dit een vereenvoudigde weergave.
 
Ivo P

Ivo P

05/07/2021 19:45:57
Quote Anchor link
@Ad:
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/
 
Ad Fundum

Ad Fundum

06/07/2021 14:39:36
Quote Anchor link
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.
 
Veur Heur

Veur Heur

07/09/2021 12:59:25
Quote Anchor link
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:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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


Klopt toch dat dit overzicht met 1 query te maken moet zijn?
 
Ivo P

Ivo P

07/09/2021 14:26:41
Quote Anchor link
Wat gebeurt er als je, zoals eerder opgemerkt, het stuk GROUP BY uitbreidt tot

Code (php)
PHP script in nieuw venster Selecteer het PHP script
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
 
Veur Heur

Veur Heur

07/09/2021 14:37:51
Quote Anchor link
Heb ik over het hoofd gezien? Werkt!!
 
Veur Heur

Veur Heur

14/09/2021 13:58:47
Quote Anchor link
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.
 
Ivo P

Ivo P

14/09/2021 22:12:12
Quote Anchor link
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)
 
Veur Heur

Veur Heur

15/09/2021 07:23:56
Quote Anchor link
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.
 
Ivo P

Ivo P

15/09/2021 11:17:09
Quote Anchor link
Wat is een voorbeeld van een dubbel record?
 
Veur Heur

Veur Heur

15/09/2021 11:22:38
Quote Anchor link
pid,product,timestamp,gebruiker
4,product 1,2021-09-14 14:59:16,gebruiker 2
4,product 1,2021-07-30 09:29:06,gebruiker 1
 
Ivo P

Ivo P

15/09/2021 11:57:16
Quote Anchor link
Dat zijn 2 verschillende data voor 2 verschillende gebruikers....

Ik begrijp hieruit dat je de recentste aanpassing zoekt per product en dan wil weten welke gebruiker dat gedaan heeft?
 
Veur Heur

Veur Heur

15/09/2021 11:58:58
Quote Anchor link
Klopt, een lijst met alle producten, de laatste wijzigingsdatum en de gebruiker die de wijziging heeft gedaan.
 
Ivo P

Ivo P

15/09/2021 12:11:39
Quote Anchor link
ik denk dat je dan niet aan een subquery ontkomt.

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?
 
Veur Heur

Veur Heur

15/09/2021 12:14:01
Quote Anchor link
Dit is de structuur:

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.
 
Ivo P

Ivo P

15/09/2021 12:16:38
Quote Anchor link
ah in de eerste post staat PID genoemd.

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
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


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
 
Veur Heur

Veur Heur

16/09/2021 07:55:13
Quote Anchor link
Dat is een flinke query, had ik zelf nooit bedacht. Deze geeft het gewenste resultaat, top!
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.