JOIN query met teveel results
zoals jullie al eerder waarschijnlijk vernomen hebben is mijn huidig project een ticket systeem.
Hierbij heb ik een one-to-many relatie vanaf ticket tabel, en media tabel.
dmv. een JOIN query ben ik een overzicht aan het maken, waarbij ik maar 1 resultaat (eerste) van de media tabel wil.
Code (php)
1
SELECT t.*,m.media_path FROM tickets t JOIN media ON t.ticket_id = media_ticket_id ORDER BY t.ticket_date DESC LIMIT 0,20
Hiermee wil ik dus de laatste 20 tickets weergeven en wil ik het path weten van de eerste media file die erbij hoort.
Helaas krijg ik nu voor de laatste ticket al 5 resultaten, omdat er 5 bestanden aan gelinked zijn.
Hoe kan ik de JOIN ook limiteren tot 1 resultaat ?
Mogelijk kun je iets met MIN(<media id>) ofzo doen om het eerste media bestand te vinden (indien aanwezig)? Je zou ook kunnen overwegen om het media id redundant bij het ticket op te slaan.
In principe is er bij elke ticket ten alle tijden minimaal 1 media item beschikbaar, het zij een screenshot, video, document.
De media table bevat de paden naar deze media incl een thumbnail/preview ervan.
Thanks Thomas!
"group by did the trick" klinkt een beetje als: "ik heb group by in de query gefrommeld en op het eerste oog krijg ik de data die ik wil" en dan zonder begrip van wat group by doet.
Group by hoort in een query thuis waar aggregatie functies staan.
Standaard staat de error reporting van Mysql laag mbt het foutief gebruik van group-by.
Met gevolg dat Mysql iets doet dat mogelijk is wat je wilt hebben. Of niet, of niet altijd. Dat is af te wachten.
http://wiki.pfz.nl/group-by
Achter GROUP BY horen alle kolommen te volgen die in SELECT ook staan, met uitzondering van de aggregatie functies als MIN(), MAX(), COUNT() etc.
Doe je dat niet, dan trekt mysql zijn eigen plan en levert je voor de niet genoemde kolommen willekeurige waarden aan.
Ivo P op 09/04/2016 21:12:27:
"group by did the trick" klinkt een beetje als: "ik heb group by in de query gefrommeld en op het eerste oog krijg ik de data die ik wil" en dan zonder begrip van wat group by doet.
Ehm eerlijk? Ja inderdaad :D
Ik zal is ff die link bekijken
Ivo P op 09/04/2016 21:12:27:
Achter GROUP BY horen alle kolommen te volgen die in SELECT ook staan, met uitzondering van de aggregatie functies als MIN(), MAX(), COUNT() etc.
Doe je dat niet, dan trekt mysql zijn eigen plan en levert je voor de niet genoemde kolommen willekeurige waarden aan.
Doe je dat niet, dan trekt mysql zijn eigen plan en levert je voor de niet genoemde kolommen willekeurige waarden aan.
Maar group by ticket_id, geeft ie dan niet gewoon slechts de waardes in de 'result' van die ene ticket weer? Het lijkt er niet op dat ik willekeurige tickets krijg met de preview van daarbij horende media, in de link gaat het ook maar over 1 GROUP BY field.
Zou in principe nog een MIN(m.media_id) kunnen doen, dan weet ik zeker dat ik de eerste media erbij krijg
Gewijzigd op 09/04/2016 21:17:24 door Dennis WhoCares
FROM tabel a
JOIN tabel2 b ON ...
GROUP BY a.ticket_id
als je dit doet, krijg je *een* media_id
Maar welke zal een verrassing blijven.
mogelijk die met de laagste primary key, maar misschien verandert dat weer als de records op een andere volgorde komen te staan.
Een query moet altijd eenduidig zijn.
Stel bijvoorkeur ook je database zo in dat hij niet in de vergevingsmode staat en maar wat doet.
Ivo P op 09/04/2016 21:47:11:
SELECT a.ticktet_id, b.media_id
FROM tabel a
JOIN tabel2 b ON ...
GROUP BY a.ticket_id
als je dit doet, krijg je *een* media_id
Maar welke zal een verrassing blijven.
mogelijk die met de laagste primary key, maar misschien verandert dat weer als de records op een andere volgorde komen te staan.
Een query moet altijd eenduidig zijn.
Stel bijvoorkeur ook je database zo in dat hij niet in de vergevingsmode staat en maar wat doet.
FROM tabel a
JOIN tabel2 b ON ...
GROUP BY a.ticket_id
als je dit doet, krijg je *een* media_id
Maar welke zal een verrassing blijven.
mogelijk die met de laagste primary key, maar misschien verandert dat weer als de records op een andere volgorde komen te staan.
Een query moet altijd eenduidig zijn.
Stel bijvoorkeur ook je database zo in dat hij niet in de vergevingsmode staat en maar wat doet.
Je hebt absoluut gelijk. Hoe kan ik ervoor zorgen dat die ene media id die bij ticket id hoort, daadwerkelijk de eerste media id van die ticket is?
Is de enige optie dan om deze eerste media id te benoemen in de ticket record?
En dan alle andere kolommen in group by,
Zitten er nog meer waarden bij die dubbel voorkomen?
SELECT met *
en group-by is nooit een fijne combi.
Geef gewoon alle kolommen op
Dan heb je ook de zekerheid dat je group by klopt.
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT t.ticket_id, t.foo, t.bar, t.ticket_date
MIN(m.media_path)
FROM tickets t
JOIN media ON t.ticket_id = media_ticket_id
GROUP BY t.ticket_id, t.foo, t.bar, t.ticket_date
ORDER BY t.ticket_date
DESC LIMIT 0,20
MIN(m.media_path)
FROM tickets t
JOIN media ON t.ticket_id = media_ticket_id
GROUP BY t.ticket_id, t.foo, t.bar, t.ticket_date
ORDER BY t.ticket_date
DESC LIMIT 0,20
wat is "daadwerkelijk de eerste media id"?...
Is dat die met het laagste id, oudste datum, of media_path zoals Ivo gebruikt.
ik ga eens spelen met MIN(m.media_id) en MAX(m.media_id) of ik dan daadwerkelijk de eerste en laatste media krijg.
De reden van t.* is omdat er enorm veel velden in staan
@Jan, de eerste media van de ticket is inderdaad met de laagste id :)
Komt goed! Thanks guys
Code (php)
1
2
3
2
3
SELECT media_ticket_id, MIN(id)
FROM media
GROUP BY media_ticket_id
FROM media
GROUP BY media_ticket_id
Dit resultaat kun je als subquery bij je join gebruiken.
Met de 2e join heb je toegang tot alle kolommen uit media tabel.
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT t.*, m1.id, m.media_path
FROM tickets t
LEFT OUTER JOIN (
SELECT media_ticket_id, MIN(id) as id
FROM media
GROUP BY media_ticket_id) m1 ON m1.media_ticket_id = t.id
LEFT OUTER JOIN media m ON m.id = m1.id
FROM tickets t
LEFT OUTER JOIN (
SELECT media_ticket_id, MIN(id) as id
FROM media
GROUP BY media_ticket_id) m1 ON m1.media_ticket_id = t.id
LEFT OUTER JOIN media m ON m.id = m1.id
Gewijzigd op 10/04/2016 20:39:05 door Jan de Laet
Thomas van den Heuvel op 09/04/2016 20:47:30:
Mogelijk kun je iets met MIN(<media id>) ofzo doen om het eerste media bestand te vinden (indien aanwezig)? Je zou ook kunnen overwegen om het media id redundant bij het ticket op te slaan.
Ik zie eigenlijk weinig nieuwe informatie, enkel aanvullende.