[MySQL] Crossover van data in cellentabel?

[Fietslamp398]

Ik ben bezig met een site waarvoor ik een eigenlijk vrij standaard MySQL query uit probeer te voeren. Echter stuit ik op een, voor mij vreemde en vervelende, "feature" van mijn query.

Zoals je ziet voer ik verschillende joins uit met andere tabellen. Dit gaat in het algemeen goed met de 'total_comments' en 'kudos_count'.

Echter, wanneer beide waardes niet gelijk zijn aan 0, dan wordt de output van 'kudos_count' vermenigvuldigd met de waarde van 'total_comments'. Dit hoort natuurlijk niet.

Ik ben geen expert op het gebied van MySQL (kom meer uit de javascript/html/css hoek) en hoop dat jullie mij de goede richting op kunnen sturen.

de query:

code:

 SELECT `f`.`feedname_output` , `f`.`feedname` , count( c.article_id ) AS `total_comments` , sum( ifnull( k.value, 0 ) ) AS `kudos_count` , `p` . *
FROM `espr_articles` AS `p`
LEFT JOIN `espr_feeds` AS `f` ON p.feed_id = f.feed_id
LEFT JOIN `espr_comments` AS `c` ON p.article_id = c.article_id
AND c.type = 'article'
LEFT JOIN `espr_kudos` AS `k` ON p.article_id = k.article_id
GROUP BY `p`.`article_id`
ORDER BY `p`.`ts_created` DESC
LIMIT 50

[RobIII]

Aggregate functies
Hoe werkt dat GROUP BY nu eigenlijk?

quote: http://gathering.tweakers..._messages/1105378#groupby

Het wordt moeilijker als je meerdere kolommen wil hebben. Stel je de volgende query eens voor:

SQL:

SELECT a, b FROM Table GROUP BY a

SQL Server, Oracle en andere fatsoenlijke databases slikken deze query niet. Om SQL2000 vrij te citeren:

quote:
Column 'Table.B' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Waarom zegt ie dat? Kijk eens mee naar de tabel waar we deze query op doen. We vragen hem te groeperen op identieke waarden in kolom A. Echter, als we dat doen, welke waarde van B moeten we dan selecteren bij waarde 1 in A? Daar kan je zowel 1 als 2 bij verwachten in kolom B, en omdat dat niet gespecificeerd is staat de database het terecht niet toe.

MySQL is een hele brakke database, die deze laatste constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes in kolom B aantreft. Don't do it.

<snip>

Hoeveel kolommen kan ik aggregeren en groeperen?
Zoveel je wilt. Maar let op de hoofdregel: zodra je een GROUP BY-clause gebruikt, moeten alle geselecteerde kolommen terugkomen in de GROUP BY list ofwel een aggregate functie hebben. Een voorbeeld van groeperen op meerdere kolommen:

SQL:

    
SELECT a, b, count(c) FROM Table GROUP BY a, b

Begin daar eens mee.

[ Voor 86% gewijzigd door RobIII op 23-12-2008 17:03 ]

[Niemand_Anders]

Volgens mij was de vraag hoe de vermenigvuldiging van comments en kudos voorkomen kan worden.

Echter de vermenigvuldiging gebeurt niet op database niveau, maar in het (waarschijnlijk) PHP script welke de resultaten presenteert. Voor zover ik kan beoordelen (op basis van de topic) kijkt de TS op de verkeerde plek voor een oplossing.

De TS krijgt geen foutmelding op zijn query en zal waarschijnlijk nog wel even bij MySQL blijven.

[RobIII]

Niemand_Anders schreef op dinsdag 23 december 2008 @ 17:14:
Volgens mij was de vraag hoe de vermenigvuldiging van comments en kudos voorkomen kan worden.

Echter de vermenigvuldiging gebeurt niet op database niveau, maar in het (waarschijnlijk) PHP script welke de resultaten presenteert. Voor zover ik kan beoordelen (op basis van de topic) kijkt de TS op de verkeerde plek voor een oplossing.

De TS krijgt geen foutmelding op zijn query en zal waarschijnlijk nog wel even bij MySQL blijven.

Ik ging er juist een beetje van uit dat, vanwege de "garbage" die niet grouped fields geven, de 'vermenigvuldiging' gewoon een verkeerde conclusie was. Maar inderdaad, het kan ook wel degelijk in de output zitten; dat is iets dat TS dan prima kan uitzoeken met deze tips

[Fietslamp398]

Bedankt voor de tip(s).

Voor zover ik kan overzien zit de fout wel degelijk in MySQL aangezien ik dezelfde "foutieve" resultaten krijg als ik de query uitvoer in phpmyadmin. Ik hoop de fout dmv de GROUP BY aanwijzingen te kunnen vinden.

[_js_]

De vermenigvuldiging gebeurt wel in de query, en inderdaad door gebruik van joins en group by zonder de gevolgen te overzien.

Stel je hebt een feed met 2 kudo's en 2 comments.

Je selecteert eerst uit de tabel met feeds en krijg je in totaal 1 rij terug.
Daarna join je de kudo's tabel, je hebt nu 2 rijen met dezelfde feed, maar verschillende kudo's.
Daarna join je de comments tabel, voor elke al bestaande rij worden de twee comments toegevoegd, dus 2x2.