[MySQL] Goedkoopste selecteren

[royduin]

Beste tweakers,

een waarschijnlijk simpele vraag, maar kom er niet uit. Ik heb als voorbeeld de volgende query:

SQL:

SELECT id, name, price, product_id 
FROM products
WHERE id IN (1,2,3,4,5,6) 
ORDER BY name ASC

Er staan soms dubbele producten in de database (waar product_id gelijk is) en waarvan de prijs verschilt. Nu wil ik deze query zo aanpassen dat de goedkoopste geselecteerd worden.

DISTINCT kan hier niet gebruikt worden?
GROUP BY i.c.m. MIN() zou een oplossing moeten bieden. Helaas kom ik er niet uit.
Kan voorbeelden geven van wat ik allemaal geprobeerd heb, maar het zal een kleinigheidje zijn.

Wie heeft de oplossing voor mij?

Alvast bedankt!

[Onbekend]

Met een group by mag je alleen de group by kolommen selecteren en totalen zoals count, min, max e.d.
Dus in jouw select mag alleen product_id voorkomen als je wilt groeperen.

[RobIII]

royduin schreef op woensdag 18 mei 2011 @ 22:34:
GROUP BY i.c.m. MIN() zou een oplossing moeten bieden. Helaas kom ik er niet uit.

Hoe werkt dat GROUP BY nu eigenlijk?

royduin schreef op woensdag 18 mei 2011 @ 22:34:
Helaas kom ik er niet uit.

Wat heb je dan? Laat eens de SQL query zien zoals je die had met een Group By?

Onbekend schreef op woensdag 18 mei 2011 @ 22:37:
Met een group by mag je alleen de group by kolommen selecteren en totalen zoals count, min, max e.d.

Aggregates to be precise

[Voor 48% gewijzigd door RobIII op 18-05-2011 22:42]

[royduin]

Dat is mij nooit verteld! Goed om te weten en de FAQ maakt een hoop duidelijk. Stukken beter als alle tutorials die ik tot nu toe gevolgd heb om achter het probleem te komen.

Hoe ga ik dit dan oplossen / wat is het makkelijkste? Met een join om te kijken of er nog een product aanwezig is met hetzelfde product_id? Dan de prijs vergelijken?

[Voor 15% gewijzigd door royduin op 18-05-2011 22:55]

[RobIII]

royduin schreef op woensdag 18 mei 2011 @ 22:54:
Hoe ga ik dit dan oplossen / wat is het makkelijkste?

Again: wat heb je zelf al geprobeerd? Waar kom je precies niet uit? Hoe ziet je query er nu uit?
Met een kleine subquery of een having clause moet je een heel eind komen volgens mij (mits ik je datamodel goed zie in mijn glazen bol en goed heb afgeleid van de summiere, tot bijna afwezige, info in je TS)

[Voor 34% gewijzigd door RobIII op 18-05-2011 23:03]

[Dramatic]

royduin schreef op woensdag 18 mei 2011 @ 22:54:
Dat is mij nooit verteld! Goed om te weten en de FAQ maakt een hoop duidelijk. Stukken beter als alle tutorials die ik tot nu toe gevolgd heb om achter het probleem te komen.

Hoe ga ik dit dan oplossen / wat is het makkelijkste? Met een join om te kijken of er nog een product aanwezig is met hetzelfde product_id? Dan de prijs vergelijken?

probeer en post de resultaten hier ..

[letsa]

Zou de join 'goedkoper' zijn in performance dan een group by of zelfs een subquery?

[RobIII]

letsa schreef op woensdag 18 mei 2011 @ 22:59:
Zou de join 'goedkoper' zijn in performance dan een group by of zelfs een subquery?

Een join != group by != subquery. In de gevallen waar je met wat creatief queryen het één door het ander kunt vervangen geldt maar 1 regel: meten == weten. En dan is het nog altijd afhankelijk van indices en andere zaken die meespelen. Je kunt hier geen "altijd geldende" uitspraak over doen.

[Dramatic]

RobIII schreef op woensdag 18 mei 2011 @ 23:01:
[...]

Een join != group by != subquery. In de gevallen waar je met wat creatief queryen het één door het ander kunt vervangen geldt maar 1 regel: meten == weten. En dan is het nog altijd afhankelijk van indices en andere zaken die meespelen. Je kunt hier geen "altijd geldende" uitspraak over doen.

Optimaliseren is sowieso een brug te ver zolang hij het niet werkend krijgt.

[royduin]

Bij deze de code.

PHP:

<?php
//*snip*

//Alle product id's selecteren die voldoen aan de categorie en deze in $query5_ids zetten.
$query5 = mysql_query("SELECT id FROM producten_new WHERE icecat_cat='".secure_in($obj->icecat)."'".$selecteren."");
if(mysql_num_rows($query5)){
    while($row5 = mysql_fetch_assoc($query5)){
        $query5_ids .= $row5['id'].", ";
    }
    //Laatste , weg halen
    $query5_ids = substr($query5_ids, 0, -2);
}

//*snip*

//Een query om de producten te tellen wat uiteindelijk gebruikt wordt om het aantal producten per pagina te regelen.
$query13 = mysql_query("SELECT COUNT(id) as aantal FROM producten_new WHERE id IN (".$query5_ids.")");

//*snip*

//Dit is de "uiteindelijke" query. Hierna worden de producten ge'echo'ed
$query13 = mysql_query("SELECT 
id, 
fabrikant, 
naam, 
afbeelding_thumb, 
prijs, 
omschrijving_kort, 
afbeelding_low, 
aanbieding, 
icecat_id 
FROM producten_new 
WHERE id IN (".$query5_ids.") 
ORDER BY fabrikant ASC,".$sorteren." LIMIT
 ".$offset.", ".$items_per_pagina);

//*snip*
?>

Wat ik onder andere geprobeerd heb is de laatste query aanpassen met een GROUP BY erin. Zoals zojuist vernomen is dat helemaal niet mogelijk in deze query omdat er meerdere velden geselecteerd worden:

PHP:

<?php
$query13 = mysql_query("SELECT 
id, 
fabrikant, 
naam, 
afbeelding_thumb, 
MIN(prijs), 
omschrijving_kort, 
afbeelding_low, 
aanbieding, 
icecat_id 
FROM producten_new 
WHERE id IN (".$query5_ids.") 
GROUP BY icecat_id 
ORDER BY fabrikant ASC,".$sorteren." LIMIT
 ".$offset.", ".$items_per_pagina);
?>

Het is dus ook niet mogelijk om de eerste query aan te passen met een GROUP BY erin? Hier wil ik het veld id van hebben. Deze wordt niet opgenomen in de GROUP BY.

Tips?

[RobIII]

royduin schreef op donderdag 19 mei 2011 @ 12:32:

Wat ik geprobeerd heb is de laatste query aanpassen met een GROUP BY erin.

En dat heb je niet goed gedaan for one
Hoe werkt dat GROUP BY nu eigenlijk?

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.

Je moet sowieso alle velden die niet in aggregates zijn opgenomen in de GROUP BY zetten. Dat doe je, los van eventuele andere problemen met je query, al verkeerd

royduin schreef op donderdag 19 mei 2011 @ 12:32:
Zoals zojuist vernomen is dat helemaal niet mogelijk in deze query omdat er meerdere velden geselecteerd worden:

Onzin dus; je moet een GROUP BY gewoon toepassen zoals het hoort.

royduin schreef op donderdag 19 mei 2011 @ 12:32:
Het is dus ook niet mogelijk om de eerste query aan te passen met een GROUP BY erin?

Probeer het nog eens nu je weet wat je nu weet

royduin schreef op donderdag 19 mei 2011 @ 12:32:
Tips?

Ja; we doen hier niet aan handjes houden of voorkauwen De manier waarop je 't nu vraagt is 't nogal een "fix mijn query effe voor me". Misschien bedoel je het niet zo, zo komt het wel over

[Voor 4% gewijzigd door RobIII op 19-05-2011 12:37]

[royduin]

Het lijkt mij het makkelijkste om de eerste query aan te passen.
Dan kom ik op het volgende uit:

PHP:

$query5 = mysql_query("SELECT id, MIN(prijs), icecat_id FROM producten_new WHERE icecat_cat='".secure_in($obj->icecat)."'".$selecteren." GROUP BY icecat_id");

En dit lijkt te werken Nog even verder stoeien en testen, maar lijkt tot nu goed te gaan.

EDIT: MIN gewijzigd naar MAX, de duurste werd weergegeven? En weer verder testen.

EDIT2: Gaat niet echt lekker, min/max lijkt niet goed te werken. Gegevens zijn opgeslagen als decimal(15,2). Gaat dit wel goed samen?

[Voor 24% gewijzigd door royduin op 19-05-2011 13:38]

[RobIII]

royduin schreef op donderdag 19 mei 2011 @ 13:29:

PHP:

$query5 = mysql_query("SELECT id, MIN(prijs), icecat_id FROM producten_new WHERE icecat_cat='".secure_in($obj->icecat)."'".$selecteren." GROUP BY icecat_id");

Ik herhaal mezelf maar weer:

RobIII schreef op donderdag 19 mei 2011 @ 12:36:
Je moet sowieso alle velden die niet in aggregates zijn opgenomen in de GROUP BY zetten. Dat doe je, los van eventuele andere problemen met je query, al verkeerd

En de FAQ waar ik je op wees:

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.

In je Select: id, MIN(prijs), icecat_id
In je Group by: icecat_id

Hoeveel aggregated velden zie je in de select? En hoeveel niet-aggregated velden?
Hoeveel velden zie je in de Group by? En hoeveel zouden dat er moeten zijn?

spoiler:

1,2,1,2. Bedenk zelf maar welke.

offtopic:
"secure_in" Is dat een eigen versie van mysql_real_escape_dinges?

[Voor 69% gewijzigd door RobIII op 19-05-2011 13:53]

[royduin]

Dan krijg ik juist weer dubbele producten te zien:

PHP:

<?php
$query5 = mysql_query("SELECT id, MIN(prijs), icecat_id FROM producten_new WHERE icecat_cat='".secure_in($obj->icecat)."'".$selecteren." GROUP BY icecat_id, id");
?>

Ook icecat_id en id al omgedraaid..



En idd is secure_in gelijk aan mysql_real_escape_string, zo gebruik ik ook secure voor htmlentities.

P.S. Denk er tevens aan om de database structuur te veranderen. Zorgen dat er geen dubbele producten meer in staan. Nieuw tabel erbij met daarin de voorraad en prijs van leveranciers. Lijkt me makkelijker.

[Voor 42% gewijzigd door royduin op 19-05-2011 14:04]

[RobIII]

royduin schreef op donderdag 19 mei 2011 @ 13:55:
Dan krijg ik juist weer dubbele producten te zien:

Dan moet je eens goed bedenken wat de group by doet en welke velden je werkelijk nodig hebt en wat de group nou "uniek" maakt of juist "2 producten" veroorzaakt.

[royduin]

Ik heb weer veel geleerd van dit topic! Zoals in mijn vorige bericht bij de ps vermeld, ga ik de structuur maar aanpassen zodat dit soort taferelen niet nodig zijn.

Hartelijk dank voor het mee denken en de inzet!! Nogmaals heb er weer veel van opgestoken.

[Mercatres]

royduin schreef op donderdag 19 mei 2011 @ 12:32:
Tips?

Een mysql_error(); erachter kan ook wel eens helpen.

PHP:

$sql = "...";
mysql_query($sql) or die(mysql_error());

[KopjeThee]

code:

SELECT id, name, price, product_id 
FROM products
GROUP BY id
HAVING price = min(price)

Mogelijk werkt zoiets.

[RobIII]

KopjeThee schreef op donderdag 19 mei 2011 @ 20:26:

code:

SELECT id, name, price, product_id 
FROM products
GROUP BY id
HAVING price = min(price)

Mogelijk werkt zoiets.

Heb je nou voor de gein 't topic al eens doorgelezen? En enig idee wat er dan mankeert aan je query? Ik snap werkelijk waar niet hoe je na 15+ replies in dit topic over hoe een Group By werkt nog met zoiets op de proppen kunt komen.

[Voor 15% gewijzigd door RobIII op 19-05-2011 20:32]

[KopjeThee]

RobIII schreef op donderdag 19 mei 2011 @ 20:30:
[...]

Heb je nou voor de gein 't topic al eens doorgelezen? En enig idee wat er dan mankeert aan je query? Ik snap werkelijk waar niet hoe je na 15+ replies in dit topic over hoe een Group By werkt nog met zoiets op de proppen kunt komen.

Hangt een beetje van je database af. Ik ken systemen waarin dit werkt... En ja, ik begrijp dat het niet de meest strikte toepassing van een group by is.

[RobIII]

KopjeThee schreef op donderdag 19 mei 2011 @ 20:34:
Ik ken systemen waarin dit toevallig, soms, wel eens werkt ondanks dat 't simpelweg onmogelijk is er iets zinnigs van te maken omdat de grouping te weinig informatie heeft en dus maar moet gaan gokken...

FTFY

[Voor 28% gewijzigd door RobIII op 19-05-2011 20:38]

[KopjeThee]

RobIII schreef op donderdag 19 mei 2011 @ 20:35:
[...]

FTFY

Ja, ja. Nee hoor, dat werkt daar altijd Je krijgt dan alle records waarvoor dat geldt.

Zoiets dan liever?

code:

SELECT
  p.* 
FROM 
  products AS p 
  INNER JOIN
  (SELECT min(price) AS min_price, id FROM products GROUP BY id) AS m
  ON p.id = m.id AND
        p.price = m.min_price

[Voor 5% gewijzigd door KopjeThee op 19-05-2011 20:45]

[RobIII]

KopjeThee schreef op donderdag 19 mei 2011 @ 20:43:
Zoiets dan liever?

Vast... Maar ik heb eigenlijk liever dat TS iets opsteekt i.p.v. dat het op een zilveren presenteerblaadje wordt aangereikt; want dan staat er volgende week wéér een (soortgelijk) topic van TS in dit forum en blijven we aan de gang

Give a man a fish and feed him for a day. Teach a man how to fish and feed him for a lifetime.

Dat is precies de reden waarom ik alleen maar met linkjes en uitleg strooi(de) in dit topic

royduin schreef op donderdag 19 mei 2011 @ 14:43:
Ik heb weer veel geleerd van dit topic!
...
Nogmaals heb er weer veel van opgestoken.

See? Maar ondanks die reply van TS had je query van mij niet in dit topic gehoeven; hij zegt namelijk wel iets opgestoken te hebben maar dat weten we pas als we 'm niet meer zien in 't topic. En dat is nu je voorgekauwde query er voor TS voor 't oprapen ligt per definitie natuurlijk zo

[Voor 30% gewijzigd door RobIII op 19-05-2011 20:58]

[Creepy]

En daarbij: Dat soort zaken iemand aanleren is echt onzinning omdat het alleen, en dan ook alleen in MySQL lijkt te werken. Maar er zijn zeker gevallen waarin MySQL je een random stukje resultaat teruggeeft omdat niet altijd te bepalen is wat de inhoud van de overige velden moet zijn die niet in je group my zijn opgenomen. Daar zijn echt legio topics hier te vinden die precies dat probleem hebben. Iemand zaken op de juiste manier aanleren/weggeven is dan net ff iets handiger

[KopjeThee]

Creepy schreef op donderdag 19 mei 2011 @ 20:58:
En daarbij: Dat soort zaken iemand aanleren is echt onzinning omdat het alleen, en dan ook alleen in MySQL lijkt te werken. Maar er zijn zeker gevallen waarin MySQL je een random stukje resultaat teruggeeft omdat niet altijd te bepalen is wat de inhoud van de overige velden moet zijn die niet in je group my zijn opgenomen. Daar zijn echt legio topics hier te vinden die precies dat probleem hebben. Iemand zaken op de juiste manier aanleren/weggeven is dan net ff iets handiger

Binnen de database die ik soms gebruik, is dit absoluut niet random. Ik moest er even aan denken, omdat ik het juist een erg handige constructie vind, die prima gedefinieerd is. Het zou me trouwens verbazen als dit in MySQL werkt. Gebruik ik vrijwel nooit. Ik heb trouwens wel wat twijfels bij een opmerking als "de juiste manier". Ik vat dat nu maar op als "behorend tot de grootste gemene deler van functionaliteit van alle database systemen". Dat zou me wat te beperkend zijn. Zo geweldig is SQL nu ook weer niet.

[RobIII]

KopjeThee schreef op donderdag 19 mei 2011 @ 21:19:
Binnen de database die ik soms gebruik, is dit absoluut niet random.

Welke is dat dan? En hoe is het gedrag dan gedefinieerd (en: gedocumenteerd)?

KopjeThee schreef op donderdag 19 mei 2011 @ 21:19:
Ik moest er even aan denken, omdat ik het juist een erg handige constructie vind

Want Minder typewerk? Sinds wanneer is dat een goed excuus?

KopjeThee schreef op donderdag 19 mei 2011 @ 21:19:
die prima gedefinieerd is.

Show me

KopjeThee schreef op donderdag 19 mei 2011 @ 21:19:
Het zou me trouwens verbazen als dit in MySQL werkt.

Het werkt wel als je doelt op het achterwege laten van non-aggregated velden in de Group By (zie de FAQ waar ik in dit topic steeds op wijs) (mits MySQL niet in strict-mode oid staat; ik ken de details niet)

KopjeThee schreef op donderdag 19 mei 2011 @ 21:19:
Ik heb trouwens wel wat twijfels bij een opmerking als "de juiste manier". Ik vat dat nu maar op als "behorend tot de grootste gemene deler van functionaliteit van alle database systemen". Dat zou me wat te beperkend zijn. Zo geweldig is SQL nu ook weer niet.

SQL is anders gewoon vastgelegd in een standaard hoor Dat er vendor-specifieke uitbreidingen, interpretatieverschillen, implementatieverschillen, implementatieproblemen/bugs etc. zijn staat daar los van. Dat je er op gaat bouwen/vertrouwen; dat is pas gevaarlijk.

[Voor 7% gewijzigd door RobIII op 19-05-2011 21:25]

[KopjeThee]

RobIII schreef op donderdag 19 mei 2011 @ 21:23:
[...]
SQL is anders gewoon vastgelegd in een standaard hoor

O een standaard, nou dan is het goed hoor. Dat weet ik natuurlijk ook wel, maar ik vind die standaard niet zo geweldig. Mag dat?

[RobIII]

KopjeThee schreef op donderdag 19 mei 2011 @ 21:25:
Mag dat?

Dat mag Jammer dat je niet op de rest van mijn post reageert though.

[Voor 18% gewijzigd door RobIII op 19-05-2011 21:26]

[Creepy]

Ik ben ook wel benieuwd welke database je precies bedoelt en hoe de regels precies gedefinieerd zijn als je niet alle velden in je group by opneemt en groepeerd op iets dat niet uniek is.

[royduin]

RobIII schreef op donderdag 19 mei 2011 @ 20:55:
See? Maar ondanks die reply van TS had je query van mij niet in dit topic gehoeven; hij zegt namelijk wel iets opgestoken te hebben maar dat weten we pas als we 'm niet meer zien in 't topic. En dat is nu je voorgekauwde query er voor TS voor 't oprapen ligt per definitie natuurlijk zo

Kom toch nog even terug, probleem is inmiddels uit de weg geholpen. Hele structuur aangepast, echt alles over de kop gegooid maar het werkt!
En heb er zeker wat van opgestoken, mede door de duidelijke FAQ. Heb veel rond gezocht maar niks kan qua uitleg tippen aan die FAQ.

Nog een keer een bedankje!