[MySQL] Probleemeffect met Join + Group + Order

[pierre-oord]

Ik heb een grote factuurdatabase waarvan ik aan de hand van het aantal verstreken dagen na de uiterste betaaldatum, bepaalde acties wil laten nemen. Ik maak hier het voorbeeld even zo simpel mogelijk:

Tabel met gebruikers

id	payments_ok
10	1
11	1

En, niet te vergeten: de tabel met facturen:

id	userid	timestamp
1	10	beetje_telaat
2	10	veel_telaat

De 2e factuur is dus later aan de database toegevoegd. Nu wil ik 1 rij terugkrijgen waarin de userid aan de id van de user tabel is gekoppeld, en alleen de factuur die het meest te laat betaald is word weergegeven. Zo bepaal ik dan een status voor de gebruiker.

Ik ben op de volgende query gekomen:
SELECT
`users`.`id`,
`users`.`email`,
`users`.`payments_ok`,
`invoices`.`mail_not_payed`,
`invoices`.`date_pay_before`

FROM `users` AS `users`

LEFT JOIN `invoices` AS `invoices` ON(`users`.`id`=`invoices`.`userid`)

WHERE
`invoices`.`date_payed`='0' AND
`invoices`.`date_pay_before`<'1156263519'

GROUP BY `users`.`id`

ORDER BY`invoices`.`date_pay_before` ASC

Het probleem zit in de order by: MySQL voert eerst de group by uit, en dan pas de order by. Het gewenste resultaat dat ik wil terugkrijgen is natuurlijk factuurID 2, zodat ik de status "veel_telaat" vind, en vervolgens een handeling kan doen.
Echter, in dit voorbeeld krijg ik factuurID 1 terug! Dat komt simpelweg door de volgorde van de facturen in de database. Als ik factuurid 2 op "weinig_telaat" zet, en "factuurid1 op "veel_telaat" klopt het (doh ).

Ik wil dus voordat group by iets onderneemt, dat eerst alles netjes wordt gesorteerd op "telaat" en daarna een group by, zodat de aller-te-laatste factuur van een gebruiker overblijft.

Hoe kan ik deze query het beste maken? Alvast bedankt weer zoals altijd!

[Feyd-Rautha]

In plaats van in de WHERE clause je vergelijking te zetten met orderdatum, moet je een HAVING clause toevoegen en daarin orderdatum vergelijken met een waarde.

code:

WHERE invoices.date_payed = 0
GROUP BY ...
HAVING invoices.date_pay_before < ...

[ Voor 169% gewijzigd door Feyd-Rautha op 22-08-2006 18:55 ]

[ACM]

Feyd-Rautha schreef op dinsdag 22 augustus 2006 @ 18:46:
Je zal HAVING moeten gebruiken in plaats van GROUP BY

Euh... having kan per definitie alleen maar samen met group by...

edit:

Ah, dat zag je zelf ook in

Maar jouw having-oplossing is waarsch nog altijd niet de oplossing, omdat je dan niet meer weet bij welk factuur de gegevens hoort die je hebt.

Je zal waarschijnlijk met subqueries en/of temporary tables moeten werken, maar ik begrijp eerlijk gezegd niet aan de hand van de uitleg wat je nou precies terug wilt krijgen. Bovendien snap ik de functie van de group by niet in deze query, die voegt niks toe en zorgt waarsch alleen voor het op vieze mysql-stylee ongecontroleerd weglaten van delen van records.

Als je enkel "de laatste factuur per gebruiker" wilt, zou je zoiets kunnen doen:
select user..., factuur...
from users join factuur ...
where factuur.id = (select max(i.id) from factuur i where i.userid = user.id)

En daar kan je dan uiteraard nog meer op filteren.

[ Voor 9% gewijzigd door ACM op 22-08-2006 18:59 ]

[pierre-oord]

Het gaat mij er dus om dat ik de factuur krijg met de laagste date_pay_before behorende bij een user. Indien de user 0 facturen heeft, wil ik alsnog wel het user-record, en dat dan de factuur-info gewoon leeg is, vandaar de left-join.

Ik heb even deze query gemaakt met het idee van een subquery, maar deze lukt helaas niet, de fout heb ik nog niet gevonden:

SQL:

SELECT `users`.`id`, `users`.`email`, `users`.`payments_ok`, `invoices`.`mail_not_payed`, `invoices`.`date_pay_before` 
FROM `users` AS `users` 

LEFT JOIN `invoices` AS `invoices` ON(`users`.`id`=`invoices`.`userid`) 
WHERE `invoices`.`id`=

( SELECT min(`invoices`.`date_pay_before`) 
FROM `invoices` AS `invoices` 
`users` AS `users`
WHERE `invoices`.`userid`=`users`.`id` AND 
`invoices`.`date_payed`='0' AND 
`invoices`.`date_pay_before`<'1156266562' ) 

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 'SELECT min(`invoices`.`date_pay_before`) FROM `invoices`

(Note: De dubbele `invoices` is omdat ik intern met variabelen voor de tabelnamen werk, daarom selecteer ik vaak met AS, en soms is't ook korter - het rode pay_before getal is de dag van vandaag, oftewel: facturen die te laat zijn. Ik wil weten wat de factuur is die het langst al over betaaldatum is ^^

edit:
resultaat dat ik dus wil zien is iets als:

id (user)	date_pay_before
10	Factuur met laagste date_pay_before van user 10

en als er geen factuur is voor die gebruiker, dan moet date_pay_before gewoon leeg zijn, zodat als ik met empty() check met PHP erachter kom dat de gebruiker geen facturen te laat heeft betaald, en dus ook weer een actie kan doen.

[ Voor 13% gewijzigd door pierre-oord op 22-08-2006 19:23 ]

[MBV]

welke versie MySQL gebruik je? de oude versies ondersteunen geen subqueries, ik dacht dat dat pas vanaf MySQL 5 was.

Wat ik me van mijn lessen databases herinner kon je dit met een HAVING oplossen. Iets van

code:

HAVING `kolom` = min(`kolom`)

Scheelt weer een query = snelheid

[Verwijderd]

De melding is vrij duidelijk: you have an error in your SQL syntax.
Als je tussen "FROM `invoices` AS `invoices`" en "`users` AS `users`" een komma zet, zal 't allicht ietsje beter werken...

[pierre-oord]

Verwijderd schreef op dinsdag 22 augustus 2006 @ 21:15:
De melding is vrij duidelijk: you have an error in your SQL syntax.
Als je tussen "FROM `invoices` AS `invoices`" en "`users` AS `users`" een komma zet, zal 't allicht ietsje beter werken...

Die had ik mis gecopy-paste, die stond er wel

Maar ik heb wat met having geprobeerd, maar dat wil nog niet lukken. Ik kreeg wel een aantal resultaten, maar plotseling miste ik de resultaten waarmee ik aan het testen was, iets zit er nog niet goed. Kan iemand voor de zekerheid de volledige query geven? Het lastige is denk ik dat ik met zo'n join werk, daardoor alles net even anders gaat?

[MBV]

Misschien helpt het als je zelf post wat je hebt gemaakt? Wat is nu je HAVING argument bijv?

[pierre-oord]

MBV schreef op dinsdag 22 augustus 2006 @ 22:29:
Misschien helpt het als je zelf post wat je hebt gemaakt? Wat is nu je HAVING argument bijv?

Oke, ik heb dit geprobeerd (query werkt, maar ik krijg de gemaakte test-facturen niet meer in het resultaat te zien)

SQL:

SELECT `users`.`id`,
`users`.`email`,
`users`.`payments_ok`,
`invoices`.`mail_not_payed`,
`invoices`.`date_pay_before`,
`invoices`.`userid`
FROM `users` AS `users`
LEFT JOIN `invoices` AS `invoices`
ON(`users`.`id`=`invoices`.`userid`)
WHERE `invoices`.`date_payed`='0' AND 
`invoices`.`date_pay_before`<'1156278950'
GROUP BY `users`.`id`
HAVING `invoices`.`date_pay_before`=min(`invoices`.`date_pay_before`)
ORDER BY `users`.`email`

Die laatste order by is gewoon voor mezelf zodat ik kan zoeken naar het e-mail adres dat hoort bij m'n test-factuur. Ik verwacht 1 rij terug te krijgen, ipv de bestaande 2 rijen, en dan de rij met het laagste date_pay_before getal. Maar ik krijg helaas niets terug (al krijg ik wel andere facturen terug, het is een bestaand systeem).

Ik heb ook nog een subquery gehad die dacht ik werkte, maar eenzelfde resultaat gaf (de betreffende factuur kwam dus niet voor). Ik probeer die nu weer te maken, maar dat wil even niet lukken, ik hoop hem zo te kunnen editten daarmee.

Ik wil dus: een left join doen op m'n users, en de invoice die aan een user word gekoppeld is de invoice van die user met de laagste date_pay_before, en als er geen invoice is, dan moet het veld gewoon leeg blijven, met wel de user erbij. Daarom de left-join.

Bedankt voor je hulp alvast! Ik breek er al uren m'n hoofd op!

edit:
Dit wil dus niet werken:

SQL:

SELECT `users`.`id`,
`users`.`email`,
`users`.`payments_ok`,
`invoices`.`mail_not_payed`,
`invoices`.`date_pay_before`,
`invoices`.`userid`
FROM `users` AS `users`
LEFT JOIN `invoices` AS `invoices`
ON(`users`.`id`=`invoices`.`userid`)
WHERE 
`invoices`.`number` = (
SELECT `invoices`.`number` 
FROM `invoices` AS `invoices`
WHERE`invoices`.`userid`=`users`.`id` AND 
`invoices`.`date_payed`='0' AND 
`invoices`.`date_pay_before`<'1156279323'
HAVING `invoices`.`date_pay_before`=min(`invoices`.`date_pay_before`)
)


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 'SELECT `invoices`.`number` FROM `invoices` AS `invoices` 

Zelfs als ik die subquery tussen haakjes zo klein mogelijk maak, alles selecteer bijvoorbeeld en gewoon `invoices`.`number` IN gebruikt ipv = , krijg ik nog een SQL fout.

Mijn MySQL versie is overigens MySQL 4.0.20 zie ik net, ik werk even niet op m'n eigen server op dit moment, waar ik 4.1 draai. En opeens vind ik op google dat subqueries pas vanaf 4.1 zijn.

Dus nu hoop ik dat iemand weet of het ook zonder zo'n subquery is te doen!

edit2:
Dit lijkt er al een beetje op:

SQL:

SELECT `users`.`id`,
`users`.`email`,
`users`.`payments_ok`,
`invoices`.`userid`,
`invoices`.`mail_not_payed`,
`invoices`.`date_pay_before`,
`invoices`.`userid`,
`invoices`.`date_payed`,
min(`invoices`.`date_pay_before`)
FROM `users` AS `users`
LEFT JOIN `invoices` AS `invoices`
ON(`users`.`id`=`invoices`.`userid`)
WHERE
`invoices`.`date_payed`='0' AND
`invoices`.`date_pay_before`<'1156280614'
GROUP BY `users`.`id`
ORDER BY `users`.`email`

Ik krijg nu een rij terug:

id	email	payments_ok	userid	mail_not_payed	date_pay_before	userid	date_payed	min(`invoices`.`date_pay_before`)
25	[e-mail]	2	25	1	1156024800	25	0	1154469600

Dat veld met min(iets) geeft inderdaad de laagste date_pay_before van deze user aan (deze user heeft 2 facturen), maar de hoogste word gelist als ik de GROUP BY gebruik. Zonder group by kan ik ze alle 2 laten zien.

In de where clause krijg ik een error als ik min() erbij gebruik. In de having clause verdwijnen beide factuurvelden in het niets, ik zou niet weten waarom

[ Voor 43% gewijzigd door pierre-oord op 22-08-2006 23:07 ]

[pierre-oord]

De enige oplossing was met een temporary table bij mysql 4.0. Eerst selecteren en sorteren, daarna een group by op de temp table.

Echter gaf dit een performance verlies. Door in 1x alle 2000 entries door PHP door te laten kijken, kon ik nog tijdswinst boeken. Resultaten (3x uitgevoerd):
// Runtime MySQL: 5.549 5.418 5.406
// Runtime PHP : 5.313 5.306 5.304

Waarbij in het laatste geval dus ipv dat de eerste query word opgeslagen in een temporary table, deze gelijk aan PHP wordt gestuurd, waarna PHP alleen de juiste data ervan pakt. Scheelt tot een miliseconde zo te zien

[jochemd]

pierre-oord schreef op dinsdag 22 augustus 2006 @ 22:39:
Ik breek er al uren m'n hoofd op!

En dat terwijl het gewoon in de handleiding staat.

[Verwijderd]

pierre-oord schreef op woensdag 23 augustus 2006 @ 02:57:

Waarbij in het laatste geval dus ipv dat de eerste query word opgeslagen in een temporary table, deze gelijk aan PHP wordt gestuurd, waarna PHP alleen de juiste data ervan pakt. Scheelt tot een miliseconde zo te zien

Dat zal ook alleen gelden als de MySQL server op dezelfde machine is geinstalleerd, of als de MySQL server aan een snelle netwerkverbinding hangt. Voor die 5% verschil laat je het normaal gesproken wel aan MySQL over, aangezien je mag aannemen dat die er het best voor geoptimaliseerd is. Zeker bij grotere tabellen gok (en ja, ik zeg gok) ik erop dat MySQL sneller zal zijn.