[MySQL] Problemen met query

[Reveller]

Ik heb de volgende tabellen en query. Met de query wil ik alle weblog posts ophalen van user 27. Ook wil ik zien wat de naam van deze gebruiker is en hoeveel comments er op zijn posts zijn geleverd.

code:

+----------------------------------------------+
| weblog_posts                                 |
+----+-------+------+---------+----------+-----+
| id | title | body | created | category | uid |
+----+-------+------+---------+----------+-----+

+-------------------+
| weblog_categories |
+----+--------------+
| id | name         |
+----+--------------+

+-------------+
| users       |
+-----+-------+
| uid | name  |
+-----+-------+

+---------------------+
| weblog_comments     |
+----+---------+------+
| id | post_id | body |
+----+---------+------+

Ik heb nu de volgende query:

SQL:

SELECT wp.*, u.name, wc.name AS category, COUNT(wt.post_id) AS num_comments 
FROM weblog_posts wp, users u, weblog_categories wc, weblog_comments wt 
WHERE wp.uid = u.uid 
AND wp.category = wc.id 
AND wp.id = wt.post_id 
AND wp.uid = 27 
ORDER BY wp.created 
DESC LIMIT 0, 5

Deze geeft alleen een foutmelding: "user error: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause". Dus ik heb toen toegevoegd:

SQL:

GROUP BY wp.id

...maar dan krijg ik maar 1 post terug, terwijl user 27, 9 posts in de database heeft staan met comments op elke post. Wie kan mij zeggen waar het fout gaat?

[[BoSS]]

Als je zowel de COUNT als de GROUP BY weg laat, krijg je dan wel genoeg resultaten terug? En als je daarna alléén de GROUP BY toevoegd?

[Wacky]

Probeer eens

code:

GROUP BY wt.post_id

[NMe]

P&W FAQ - SQL: Hoe werkt dat GROUP BY nou eigenlijk?

[ Voor 15% gewijzigd door NMe op 27-07-2005 22:54 ]

[Reinier]

En is het niet handiger om joins te gebruiken i.p.v. een enorm carthesisch product te gaan filteren via een uitgebreid where-statement? Het doet niet echt terzake, maar het viel me wel op.

[Reveller]

B_O_S_S schreef op woensdag 27 juli 2005 @ 22:51:
Als je zowel de COUNT als de GROUP BY weg laat, krijg je dan wel genoeg resultaten terug?

Dan doet 'ie het perfect

En als je daarna alléén de GROUP BY toevoegd?

Wat voegt dat toe dan? Dan tel ik nog steeds niet het aantal comments, toch?

Wacky schreef op woensdag 27 juli 2005 @ 22:52:
Probeer eens

code:

GROUP BY wt.post_id

Resultaat: user error: Unknown column 'wp.post_id' in 'group statement'

-NMe- schreef op woensdag 27 juli 2005 @ 22:53:
P&W FAQ - SQL: Hoe werkt dat GROUP BY nou eigenlijk?

Dat ga ik eens bekijken <edit>Net bekeken. Ik snap het wel, maar zie nu niet meer hoe een GROUP BY statement mij zou kunnen helpen in mijn query. Waarom vraagt MySQL daar eigenlijk om? Ik wil toch helemaal niets groeperen, alleen tellen

Reinier schreef op woensdag 27 juli 2005 @ 23:03:
En is het niet handiger om joins te gebruiken i.p.v. een enorm carthesisch product te gaan filteren via een uitgebreid where-statement? Het doet niet echt terzake, maar het viel me wel op.

Kan natuurlijk ook. Blijft staan dat ik nog steeds wil uitvinden waarom deze niet werkt Zou er trouwens performance verschil zijn tussen uitgereid WHERE statement of JOINS? Overigens: als ik het goed begrijp zou je in dat geval dus gebruik moeten maken van LEFT JOINs, toch?

[ Voor 72% gewijzigd door Reveller op 28-07-2005 08:59 ]

[justmental]

Je wilt een rij terug per post met een aggregatie over de comments (count).
Groeperen doe je op alle kolommen waar je geen aggregated functie op hebt.
Dus: die 'wp.*' moet weg, uitschrijven wat je wilt hebben.
Voeg daarna een group by toe op alle kolommen in je select behalve de count.

[Mike78]

Reveller schreef op donderdag 28 juli 2005 @ 08:45:

code:

code:

 GROUP BY wt.post_id

Resultaat: user error: Unknown column 'wp.post_id' in 'group statement'
[...]

Je gebruikt to wel wt.post_id en geen wppost_id.

[ Voor 5% gewijzigd door Mike78 op 28-07-2005 09:20 ]

[Reveller]

Mike78 schreef op donderdag 28 juli 2005 @ 09:19:
[...]Je gebruikt to wel wt.post_id en geen wppost_id.

Mijn fout Voor de duidelijkheid de huidige query:

SQL:

SELECT wp.* , u.name, wc.name AS category, COUNT(wt.post_id) AS num_comments 
FROM weblog_posts wp, users u, weblog_categories wc, weblog_comments wt 
WHERE wp.uid = u.uid 
AND wp.category = wc.id 
AND wp.id = wt.post_id 
AND wp.uid = 27 
GROUP BY wt.post_id 
ORDER BY wp.created DESC

Deze geeft geen foutmelding, maar laat alleen berichten zien waar commentaar op is gegeven. Berichten zonder comments zitten niet in de result set

justmental schreef op donderdag 28 juli 2005 @ 09:06:
Je wilt een rij terug per post met een aggregatie over de comments (count).
Groeperen doe je op alle kolommen waar je geen aggregated functie op hebt.
Dus: die 'wp.*' moet weg, uitschrijven wat je wilt hebben.
Voeg daarna een group by toe op alle kolommen in je select behalve de count.

De volgende query (nav justmental) heeft, voor zover ik kan nagaan, hetzelfde probleem:

SQL:

SELECT wp.id, wp.title, wp.body, wp.created, wp.category, wp.uid, u.name, 
  wc.name AS category, COUNT(wt.post_id) AS num_comments 
FROM weblog_posts wp, users u, weblog_categories wc, weblog_comments wt 
WHERE wp.uid = u.uid 
AND wp.category = wc.id 
AND wp.id = wt.post_id 
AND wp.uid = 27 
GROUP BY wp.id, wp.title, wp.body, wp.created, wp.category, wp.uid, 
  u.name, wc.name
ORDER BY wp.created 
DESC LIMIT 0, 5

offtopic:
Indent bij query alleen voor leesbaarheid te bevorderen - in mijn PHP zet ik hem helemaal achter elkaar (ga later nog wel opdelen)

Wel zijn beide queries redelijk traag...maar dat kan ook aan mijn machine liggen

[ Voor 23% gewijzigd door Reveller op 28-07-2005 09:50 ]

[Mike78]

Reveller schreef op donderdag 28 juli 2005 @ 09:38:
[...]

Mijn fout Voor de duidelijkheid de huidige query:

SQL:

SELECT wp.* , u.name, wc.name AS category, COUNT(wt.post_id) AS num_comments 
FROM weblog_posts wp, users u, weblog_categories wc, weblog_comments wt 
WHERE wp.uid = u.uid 
AND wp.category = wc.id 
AND wp.id = wt.post_id 
AND wp.uid = 27 
GROUP BY wt.post_id 
ORDER BY wp.created DESC

Deze geeft geen foutmelding, maar laat alleen berichten zien waar commentaar op is gegeven. Berichten zonder comments zitten niet in de result set
[...]
Wel zijn beide queries redelijk traag...maar dat kan ook aan mijn machine liggen

Dat je alleen berichten met commentaar ziet komt door

code:

AND wp.id = wt.post_id

Je zou een union kunnen doen waarin je de posts selecteert zonder comment

[ripexx]

Mike78 schreef op donderdag 28 juli 2005 @ 09:55:
Je zou een union kunnen doen waarin je de posts selecteert zonder comment

Waarom een UNION als hij nu eens de INNER JOIN op comments veranderd in een (LEFT) OUTER JOIN dan krigt hij waarschijnlijk veel meer resultaten terug

Reveller schreef op donderdag 28 juli 2005 @ 09:38:
[...]

offtopic:
Indent bij query alleen voor leesbaarheid te bevorderen - in mijn PHP zet ik hem helemaal achter elkaar (ga later nog wel opdelen)

Waarom zou je in de PHP code dit achter elkaar zetten? Heeft toch weinig nu en als je later moet debuggen kan je alles weer terug gaan zetten. Nu kan je ook ff snel het statement printen in een webpagina, al is het maar tussn <!-- -->

Wel zijn beide queries redelijk traag...maar dat kan ook aan mijn machine liggen

Check eens met EXPLAIN welke indexen er gebruikt worden. dan kan je sneller zien wat er mis gaat.

[ Voor 52% gewijzigd door ripexx op 28-07-2005 10:10 ]

[_js_]

dubbel

[ Voor 98% gewijzigd door _js_ op 28-07-2005 10:09 . Reden: hier stond hetzelfde als in de post van ripex ]

[Reveller]

ripexx schreef op donderdag 28 juli 2005 @ 10:07:
[...]als hij nu eens de INNER JOIN op comments veranderd in een (LEFT) OUTER JOIN dan krigt hij waarschijnlijk veel meer resultaten terug

SQL:

SELECT wp. * , u.name, wc.name AS category, COUNT( wt.post_id ) AS num_comments
FROM weblog_posts wp, users u, weblog_categories wc
LEFT JOIN weblog_comments wt ON ( wp.id = wt.post_id )
WHERE wp.uid = u.uid AND wp.category = wc.id AND wp.id = wt.post_id AND wp.uid =27
GROUP BY wt.post_id
ORDER BY wp.created DESC

Zelfde resultaat...wat doe ik nu verkeerd?

Check eens met EXPLAIN welke indexen er gebruikt worden. dan kan je sneller zien wat er mis gaat

Ik moet de database ueberhaupt nog optimaliseren - heb nog geen enkele rekening gehouden met indexen, keys etc. Dus ik moet niet zeuren idd

[ Voor 18% gewijzigd door Reveller op 28-07-2005 10:17 ]

[ripexx]

Reveller schreef op donderdag 28 juli 2005 @ 10:14:
[...]

SQL:

SELECT wp. * , u.name, wc.name AS category, COUNT( wt.post_id ) AS num_comments
FROM weblog_posts wp, users u, weblog_categories wc
LEFT JOIN weblog_comments wt ON ( wp.id = wt.post_id )
WHERE wp.uid = u.uid AND wp.category = wc.id AND wp.id = wt.post_id AND wp.uid =27
GROUP BY wt.post_id
ORDER BY wp.created DESC

Zelfde resultaat...wat doe ik nu verkeerd?

Even wat zaken die niet helamal goed zijn:
wp.id = wt.post_id komt voor in zowel de WHERE als in de ON. Dus wat ius het nu. Jammer trouwens dat MySQL hier niet over begint te miepen.

Je GROUP BY klopt niet. Hoewel MYSQL het accepteerd zal SQL server of Oracle gendadeloos klappen op jouw GROUP BY.

Vermijd het gebruik van table.* En gebruik gewoon de kolomnamen. Table.* is leuk tijdens debuggen en testen maar niet in je applicatie.

SQL:

SELECT wp.text, wp.post_id, wp.titel, u.name, wc.name AS category, COUNT( wt.post_id ) AS num_comments
FROM weblog_posts wp, users u, weblog_categories wc
LEFT JOIN weblog_comments wt ON ( wp.id = wt.post_id )
WHERE wp.uid = u.uid AND wp.category = wc.id AND wp.uid =27
GROUP BY wp.text, wp.post_id, wp.titel, u.name, wc.name
ORDER BY wp.created DESC

Aanpassingen ^^^
* Extra WHERE statement weggehaald
* GROUP BY en SELECT conform SQL standaard gemaakt. Kolom namen wel ff aanpassen naar eigen gebruik.

Reveller schreef op donderdag 28 juli 2005 @ 10:14:
Ik moet de database ueberhaupt nog optimaliseren - heb nog geen enkele rekening gehouden met indexen, keys etc. Dus ik moet niet zeuren idd

Wellicht handig dat je eerst een goede opzet maakt. Ik ben er zelf ook achter gekomen dat het wel makelijk is. Zeker als je tabel naar tabel maakt kom je er achter dat het niet altijd even handig is. Enkel zaken die je bij het ontwerp in de gaten moet/kan/mag houden.

* Goede normalisatie
* Standaard tabelnamen en kolomnamen en of prefixen. Bijvoorbeel ID + TABELNAAM enz
* Bepaal wat je wil laten zien. Dus ik wil een lijst van gebruikers op achternaam. Dan is een index opachternaam wellicht handig. Niet altijd trouwens maar met goede logica kom je een eind.

[ Voor 22% gewijzigd door ripexx op 28-07-2005 10:26 ]

[Reveller]

ripexx schreef op donderdag 28 juli 2005 @ 10:21:
[...]
veel uitleg
[...]

Dank je, ripexx! Ik heb je query snel aangepast aan mijn kolomnamen en hij werkt perfect. Nu is het zaak die query stap voor stap door te lopen en te begrijpen. Ik zal er eens wat (w3schools ) tutorials op naslaan.

Je hebt gelijk wat betreft database / applicatie design - je weet alleen ws. zelf hoe het is: je bent bezig met iets en denkt "dat en dat toevoegen zou ook mooi zijn! Ff snel een tabelletje aanmaken". Ik had het idee om als mijn applicatie (eenvoudig cms) klaar is, hem opnieuw te gaan maken, maar dan met een goed design (omdat ik dan weet welke functionaliteit ik er allemaal in bouw). Alleen: het blijkt dat een applicatie eigenlijk nooit af is en zo raak je steeds verder van huis. Misschien na het afronden van de weblog module toch maar eens een tijdje evaluatie inlassen

[ripexx]

Reveller schreef op donderdag 28 juli 2005 @ 10:44:
[...]
Dank je, ripexx! Ik heb je query snel aangepast aan mijn kolomnamen en hij werkt perfect. Nu is het zaak die query stap voor stap door te lopen en te begrijpen. Ik zal er eens wat (w3schools ) tutorials op naslaan.

Ik vraag me af of W3Schools hier wel zo goed voor is. Gewoon regelamtig mee stoeien en flink zoeken op GoT, Google etc en je komt een aardig eind.

Je hebt gelijk wat betreft database / applicatie design - je weet alleen ws. zelf hoe het is: je bent bezig met iets en denkt "dat en dat toevoegen zou ook mooi zijn! Ff snel een tabelletje aanmaken". Ik had het idee om als mijn applicatie (eenvoudig cms) klaar is, hem opnieuw te gaan maken, maar dan met een goed design (omdat ik dan weet welke functionaliteit ik er allemaal in bouw). Alleen: het blijkt dat een applicatie eigenlijk nooit af is en zo raak je steeds verder van huis. Misschien na het afronden van de weblog module toch maar eens een tijdje evaluatie inlassen

Ja ik weet hoe het is en ik kom er nu ook achter hoe vervelend het is. Op mijn werk hebben we mooie coding afspraken etc dus dat is ideaal. Terwijl thuis een hobby project is wat begonnen is met 20 tabellen, nu al 60 heeft en ik verwacht dat de 80 makelijk haalbaar zijn. Metname door de grote hoeveelheid koppeltabellen. Tja en dan zijn vastgestelde kolomnamen handig. Dus op dit moment worden alle neiuwe tabelen aangepast. De oude laat ik voorlopig zo omdat ik dan alle queries na moet lopen die in de code zitten en daar wil je niet aan beginnen.

[Reveller]

ripexx schreef op donderdag 28 juli 2005 @ 11:09:
[...]
De oude laat ik voorlopig zo omdat ik dan alle queries na moet lopen die in de code zitten en daar wil je niet aan beginnen.

Niet dat ik er zelf mee werk, maar ik heb nog eens bij een bedrijfje gewerkt waar ze alle queries in een apart bestand (router.php) hadden staan. Via functies kon je deze dan aanroepen, bijvoorbeeld (en ongeveer, want is lang geleden):

PHP:

// Ergens in jouw code:
$result = db_query('insert_user', $name, $password);

// router.asp
$sql['insert_user'] = "INSERT INTO {users} (uid, name, password) VALUES ('', '%s', '%s')";
$sql['update_user'] = "UPDATE {users} SET password = '%s' WHERE uid = %d";
$sql['select_user'] = "SELECT * FROM {users} WHERE uid = %d ";

function db_query($query, $arg1 = null, $arg2 = null, $arg3 = null, $arg4 = null)
{
  global $sql;

  $_query = sprintf($sql, $arg1, $arg2, $arg3, $arg4);
  
  return mysql_db_query($_query);
}

Niet volledig en er zullen vast fouten in zitten, maar het idee is duidelijk. De $sql array bestand dan uit een paar honderd queries. Misschien brengt dat je nog op ideeen

[ Voor 27% gewijzigd door Reveller op 28-07-2005 16:19 ]