Join haalt data uit andere tabel niet juist op

Door Ariën op 26-07-2017 21:16 gewijzigd op 26-07-2017 21:27

2.038 views

Ik zit met een probleem aan een query die ik niet goed werkend krijg. Allereerst een query die wel werkt, en dit toont de laatste 4 bericht uit de nieuws-tabel, met een koppeling met gebruikerstabel

Dit werkt wel:


SELECT m.name, m.surname, n.id, n.title AS title, n.message_short, n.message, n.date_posted, n.date_editted, n.active
		FROM nieuws n
		    INNER JOIN members m ON n.author = m.id
		WHERE n.active = '1'
		AND n.deleted='0' 
		AND n.date_posted <= NOW()
		ORDER BY n.date_posted DESC 
		LIMIT 4

Maar nu wil ik dus een speciaal thumbnail-plaatje meegeven die gekoppeld is aan het bericht.
Dit is de tabel uploads-items, hieraan kan ik meerdere foto's aan een item (nieuwsbericht, review, agenda; wordt bepaald met een integer in UploadtypeID waar 7 in dit geval voor nieuwsberichten staan) koppelen. Die met Featured is de thumbnail die hij moet pakken, per KindID en Uploadtype_ID kan er normaal gezien maar eentje bestaan.
[img]https://image.ibb.co/mXVpc5/upload_items_tabel.jpg[/img]

Dit is de tabel uploads, en staan alle uploads in.
[img]https://image.ibb.co/dJpFH5/uploads_tabel.jpg[/img]

Nu geeft deze query alleen een enkel record i.p.v. de laatste vier?
De bedoeling is dus dat hij in de upload_items tabel kijkt of het KindID bestaat, en anders moet filename gewoon leeg zijn, want een thumbnail is niet verplicht.

SELECT m.name, m.surname, n.id, n.title AS title, n.message_short, n.message, n.date_posted, n.date_editted, n.active, n.author,up.filename
		FROM nieuws n
		    INNER JOIN members m ON n.author = m.id
		    LEFT JOIN uploads_items ui ON ui.KindID = n.id
		    LEFT JOIN uploads up ON up.ID = ui.UploadID
		WHERE n.active = '1'
		AND ui.Featured = '1' 
		AND n.deleted='0' 
		AND Uploadtype_ID = '7'
		AND n.date_posted <= NOW()
		ORDER BY n.date_posted DESC 
		LIMIT 4

Hoe kan dat? Ik hoop dat het verhaal een beetje duidelijk is.

Erik H

26-07-2017 22:30

Met een join in combinatie met een limit wordt het wat ingewikkeld. Aangezien je van de 4 resultaten die je al had telkens 1 veld wilt toevoegen kun je in dit geval ook een subselect gebruiken. Hopelijk werkt zo iets:

SELECT m.name, m.surname, n.id, n.title AS title, n.message_short, n.message, n.date_posted, n.date_editted, n.active,

(SELECT up.filename FROM
uploads up INNER JOIN uploads_items ui ON
up.ID = ui.UploadID
WHERE ui.KindID = n.id
AND ui.Featured = '1'
AND Uploadtype_ID = '7' LIMIT 1) AS filename

FROM nieuws n
INNER JOIN members m ON n.author = m.id
WHERE n.active = '1'
AND n.deleted='0'
AND n.date_posted <= NOW()
ORDER BY n.date_posted DESC
LIMIT 4

Ben van Velzen

26-07-2017 22:38

Een LEFT JOIN in combinatie met WHERE is ook wat lastig, omdat je in het geval van een LEFT JOIN ook NULL wil toestaan. Hier zul je dan in de WHERE mee rekening moeten houden, of je conditie verplaatsen naar de JOIN.

Ariën

26-07-2017 22:53 gewijzigd op 26-07-2017 23:00

Het lijkt erop dat die query van Erik precies doet wat het moet doen.
Ik had niet verwacht dat er een subselect met die JOIN nodig voor zou zijn.

Vergeleken met zonder subselect heeft het wel als voordeel dat er meerdere waardes van een ID en KindID een 1 kunnen hebben (wat normaal nooit voorkomt), terwijl dat geen dubbele records zal tonen. Hij pakt zelfs netjes de eerste in de rij. Best robuust zelfs!

Ben van Velzen

26-07-2017 23:55 gewijzigd op 26-07-2017 23:56

>> Hij pakt zelfs netjes de eerste in de rij.
Zolang je daar geen condities voor geeft zou je dat heel erg niet moeten willen, al is het alleen maar omdat "eerste" een arbitrair iets is.
Voor de performance zou je dat soort zaken wel een beetje moeten voorkomen, omdat nu per saldo per tuple dezelfde query nog een keer gedraaid wordt. Mogelijk werkt zoiets ook (ik wist niet zeker uit welke tabel UploadTypeID moest komen):


SELECT m.name, m.surname, n.id, n.title AS title, n.message_short, n.message, n.date_posted, n.date_editted, n.active, n.author,up.filename
        FROM nieuws n
            INNER JOIN members m ON n.author = m.id
            LEFT JOIN uploads_items ui ON ui.KindID = n.id AND ui.Featured = '1' AND ui.Uploadtype_ID = '7'
            LEFT JOIN uploads up ON up.ID = ui.UploadID
        WHERE n.active = '1'
        AND n.deleted='0' 
        AND n.date_posted <= NOW()
        ORDER BY n.date_posted DESC 
        LIMIT 4

Ariën

27-07-2017 00:00 gewijzigd op 27-07-2017 00:02

UploadTypeID is eigenlijk een integer die gebruikt wordt, er komt in deze query geen koppeling aan te pas.

Ik hoef immers toch niet aan te geven wat voor type (nieuws,reviews,event etc..) het is, en of er een gallery gebruikt hoeft te worden. Wel moet dit veld gespecificeerd worden omdat een KindID niet geheel uniek is. Een nieuwsartikel met het ID 43 kan bestaan, maar ook een review of event kan eht ID 43 hebben, en daar is onderscheid voor nodig.

Het is netter geweest om dit in een enkele tabel op te slaan naturlijk, maar dat is niet zo makkelijk gedaan, omdat elke tabel weer een hoop eigen metadata heeft.

Ik zal jouw query ook eens binnenkort proberen, en kijken welke het snelste is ;-)

Ben van Velzen

27-07-2017 00:03 gewijzigd op 27-07-2017 00:04

Bij kleine aantallen records zal het denk ik weinig tot geen verschil geven, maar als het spul wat groeit kan het vuren van een subquery in de SELECT list een behoorlijke overhead geven.

Reageren

Inloggen om te reageren