Ik heb een query waarmee ik een gemiddeld cijfer wil bepalen van een hotel.
SELECT `hotels`.*,
(SELECT AVG(rating)
FROM hotelreviews WHERE hotel_id = hotels.id
WHERE (rating IS NOT NULL AND rating > 0)
GROUP BY hotel_id) AS `avgrating`
FROM `hotels`
WHERE (id = '2791')
Als ik de 'WHERE' weglaat uit de subquery, gaat het goed, echter wanneer ik de voorwaarden toevoeg geeft mysql een fout. Zo'n typische fout waarmee je denkt, wat is er?
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (rating IS NOT NULL AND rating > 0) GROUP BY hotel_id) AS `avgrating` FROM' at line 1
Waarom gebruik je niet gewoon een join om dit uit te voeren?
SELECT hotels.* , AVG(rating) AS avgrating
FROM hotels AS h
LEFT JOIN hotelreviews AS hr
ON h.id=hr.hotel_id
WHERE hr.rating IS NOT NULL AND hr.rating > 0 AND h.id=2791
GROUP BY h.id
pff, wat stom zeg! Heb ik gewoon helemaal overheen gekeken. Sorry voor deze vervuiling. Ik dacht dat het aan MySql lag, maar dat valt weer mee.
@Tikkes: Query heb ik even vanuit phpmyadmin geplakt, ik gebruik het zelf nooit.
@Bastiaan: Zou ook kunnen, maar heb je een argument waarom dat beter zou zijn? Ik vind het namelijk een beetje dom werk als je eerst je recordset gaat uitbreiden (dus ook alle hotelinformatie over meerdere rijen) en vervolgens weer gaat samenvoegen naar 1 rij
maar heb je een argument waarom dat beter zou zijn?
Ja, omdat het vele malen sneller is dan voor elk record een aparte subquery uit te voeren om het gemiddelde te bepalen.
Als je overiges de query van Bastiaan gebruikt, moet je voor de volledigheid wel groeperen op alle kolommen die je in je SELECT query ophaalt. Gebruik dus geen * maar geef de kolomnamen op en voeg die tevens toe aan de GROUP BY.
Dus iets als:
SELECT
h.naam,
h.adres,
h.etc,
AVG(hr.rating) AS avgrating
FROM
hotels AS h
LEFT JOIN
hotelreviews AS hr
ON hr.hotel_id = h.id
WHERE
hr.rating IS NOT NULL
AND
hr.rating > 0
AND
h.id = 2791
GROUP BY
h.naam,
h.adres,
h.etc
Uiteindelijk halen beide queries natuurlijk evenveel data op en is de dataset dus even groot. Het verschil is alleen of je het in 1 keer haalt of in stapjes. Mijn gedachtegang is dat je leiver je data eerst haalt en dan berekeningen doet dan per hotel een query te draaien voor subdata aangezien dit resulteert in veel meer queries.
SELECT hotels.*,
(SELECT AVG(rating)
FROM hotelreviews WHERE hotel_id = hotels.id
WHERE (rating IS NOT NULL AND rating > 0)
GROUP BY hotel_id) AS avgrating)
FROM hotels
WHERE (id = '2791')
@Blanche: Ja bij meerdere rijen is dat natuurlijk logisch, maar het gaat mij hier om één hotel, dus 1 rij. (een detail pagina). Bij lijsten gebruik in inderdaad een join om het gemiddelde te bepalen.
Ik heb even een testje gedaan, een join is inderdaad sneller, wel 0,00006 seconden (getest op 1000 queries). Ik zie geen reden om het anders te doen.
Bij zo weinig records zal het snelheidsverschil inderdaad niet merkbaar zijn. Maar er is nog een andere belangrijke reden waarom ik het niet met een subquery zou aanpakken en dat is de integriteit van je query.
In dit geval groepeer je in je subquery op het hotel_id dat in dit geval uniek zal zijn. Maar zodra je gaat groeperen op kolommen waarvan niet alle waarden uniek zijn en je in de SELECT clausule van je hoofdquery kolommen selecteert die de groepen uit je subquery verder onderverdelen, zijn de waarden die je met je subquery selecteert incorrect en niet relevant. Kortom, je zult er altijd zelf voor moeten zorgen dat de GROUP BY clausules kloppen met alle SELECT clausules. En dat vergroot de kans op bugs of incorrecte gegevens aanzienlijk...
