Ik ben bezig aan een webwinkel. Heb al eens eerder een gemaakt, maar deze is lastiger.
De query moet een tabel opleveren die alle binnengekomen orders op een rijtje zet.
Ik heb 4 tabellen waar de informatie vandaan moet komen:
KLANTEN
id | voornaam | achternaam | etc...
ORDERS
id | klanten_id | datum
ORDERREGELS
orders_id | producten_id | aantal
PRODUCTEN
id | naam | prijs
Wat ik wil maken is een overzicht (een tabel) van de binnengekomen orders.
Ik heb al een vrij lange query; die gaat als volgt:
$sql = "SELECT orders.id, orders.datum, orders.status, klanten.voornaam, klanten.achternaam, count(producten.id) as aantal, sum(producten.prijs) as waarde, orderregels.orders_id, orderregels.producten_id, sum(orderregels.aantal) as totaalaantal
FROM orders, klanten, orderregels, producten
WHERE orderregels.orders_id = orders.id
AND producten.id = orderregels.producten_id
AND klanten.id = orders.klanten_id";
Het is een query die goed werkt. Het levert veel waardevolle informatie. Wat ik echter nog graag zou willen zien is de totale waarde van zo'n order. Als er zeg maar 2 producten van 50 euro zijn besteld, en 3 producten van 100 euro, dat er dan staat: Totale waarde: 400 euro.
Dat die GROUP BY niet in de hoofdquery thuis hoort ben ik helemaal met Noppes eens. Die heb ik in eerste instantie over het hoofd gezien, maar als je er even over nadenkt klopt het inderdaad niet. Waarom zou je immers in de hoofdquery moeten groeperen?
Noppes
En joinen in de select niet echt aan te bevelen....
Misschien verduidelijkt het de boel een beetje, maar volgens mij begrijpen jullie me al erg goed. Als de query die ik gebruik niet goed is, wat zou dan wel een goede query zijn om deze data terug te krijgen?
Gelieve Niet Bumpen:
Twee of meer keer achter elkaar in een topic posten heet bumpen. Bumpen is pas na 24 uur toegestaan en kan een reden zijn voor de admins en moderators om een topic te sluiten. Gebruik indien nodig de knop om je tekst aan te passen.
Ik kwam erachter dat de subqueries binnen mijn query niet nodig zijn.
SELECT orders.id, date_format(orders.datum, '%d-%m-%Y - %H:%i') AS geplaatst, orders.status, CONCAT(klanten.achternaam, ', ', klanten.voornaam) as naam, SUM(orderregels.aantal * orderregels.prijs) AS order_totaal, SUM(orderregels.aantal) AS aantal_totaal
FROM orders, klanten, orderregels, producten
WHERE orderregels.orders_id = orders.id
AND orderregels.producten_id = producten.id
AND orders.klanten_id = klanten.id
GROUP BY orders.id
ORDER BY orders.id DESC;
Doet precies hetzelfde. Is ie nu dan misschien goed?
----------- update:
Ik heb me na jullie kritiek eens verder verdiept in de Inner Join, de GROUP BY methode en waarom je aggregate functies moet gebruiken.
Als ik het samen mag vatten is dit de kwestie:
Je kunt alles zonder problemen uit de tabel orders halen, omdat je daarop groepeert. Maar het probleem is hoe die dan met de gegevens uit de andere tabellen moet omgaan, omdat deze min of meer 'in elkaar zijn geperst'. Dat is wat ik nu begrijp :)
Ik zie in het SELECT-gedeelte van mijn query...
SELECT orders.id, date_format(orders.datum, '%d-%m-%Y - %H:%i') AS geplaatst,
orders.status, CONCAT(klanten.achternaam, ', ', klanten.voornaam) as naam,
SUM(orderregels.aantal * orderregels.prijs) AS order_totaal,
SUM(orderregels.aantal) AS aantal_totaal
... dat ik dat met de velden die ik ophaal uit ORDERREGELS ook doe :) Geen probleem daar, lijkt me. Wat ik denk waar de verwarring in zit is dat ik niet heb uitgelegd dat in mijn gebouwde systeem een klant niet meer dan 1 order kan hebben.
Dit komt omdat gebruikers zich niet kunnen registeren. "Waarom laat je die tabel KLANTEN dan niet gewoon weg en zet je naam, adres, postcode et cetera niet in ORDERS?", hoor ik jullie vragen.
Welnu, ik heb dit gedaan met het oog op de toekomst. Mocht ik ooit nog een account-functie willen maken voor de gebruikers, dan is de tabellen-structuur in ieder geval goed (Ik weet ook dat, mocht ik zo'n functie gaan maken, dat de query alsnog aangepast moet worden).
Maar voor nu is de relatie KLANTEN - ORDERS 1 op 1. Dan hoeft er op de data uit de tabel KLANTEN toch geen aggregate functie worden toegepast? (omdat de data uit KLANTEN niet 'in elkaar' wordt 'geperst')
Ik ben benieuwd naar jullie reactie. Ik heb in ieder geval het gevoel dat ik het begin te begrijpen ;)
Wat ik niet helemaal snap, want als ik op ID groepeer, wat een unieke waarde heeft, waarom zou je dan nog op andere velden moeten groeperen?
De kolom orders.id zal in de orders tabel best uniek zijn en als je alleen velden uit die tabel selecteert zal er inderdaad niet veel aan de uitkomst veranderen. Maar dat neemt niet weg dat je niet op die kolommen moet groeperen, dat is namelijk de standaard en je voorkomt ermee dat er later bugs in je query sluipen.
Een ander verhaal is het als je ook kolommen uit andere tabellen selecteert. Dan hoeft orders.id niet per se meer unieke records op te leveren aangezien er bijvoorbeeld meerdere orderregels per order zijn.
Kortom, onthoud de vuistregel dat je altijd groepeert op alle kolommen in de SELECT waarop geen aggregatie is toegepast. Dan zit je altijd goed. (Andere databases dan MySQL dwingen dit trouwens standaard af).
knop om je tekst aan te passen.