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.
Dit zijn twee queries die uitgevoerd kunnen worden als het id bekend is.
Dan kan ik 'mijn eigen' query blijven gebruiken, en per rij ($row = mysql_fetch_array($result)) een nieuwe query beginnen die gebaseerd is op $row['id']?
Ik hoop dat je het nog begrijpt. Mijn bedoeling is namelijk nog steeds om een overzicht te krijgen.
Maar ik neem aan dat als je een overzicht van alle orders wilt geven, je niet ook nog eens alle producten per order weer wilt geven?
In dat geval zou ik een subquery gebruiken om per order de totaalprijs uit de orderregels tabel te bepalen:
SELECT
o.id,
o.datum,
o.status,
k.voornaam,
k.achternaam,
( SELECT SUM(totaal)
FROM orderregels
WHERE order_id = o.id
) AS order_totaal
FROM
orders AS o
INNER JOIN
klanten AS k
ON k.id = o.klanten_id
Ik kom nu uit op 1 grote query, ik heb jouw subquery gebruikt in de hoofdquery. (maar dat is volgens mij ook de bedoeling van een subquery/dat is ook jouw bedoeling)
Wil je er naar kijken, want je had kritiek op mijn query.
$sql = "SELECT orders.id, orders.datum, orders.status, klanten.voornaam, klanten.achternaam, count(producten.id) as aantal, orderregels.orders_id, orderregels.producten_id, (SELECT SUM(aantal * prijs) FROM orderregels WHERE orderregels.orders_id = orders.id) AS order_totaal
FROM orders, klanten, orderregels, producten
WHERE orderregels.orders_id = orders.id
AND producten.id = orderregels.producten_id
AND klanten.id = orders.klanten_id";
GROUP BY orders.id
ORDER BY orders.id DESC;
Waarom gebruik jij bijvoorbeeld INNER JOIN, en ik niet?
De query die je nu gebruikt klopt nog steeds niet helemaal. Je kunt namelijk nooit de juiste waarden voor orderregels.orders_id en orderregels.producten_id selecteren zolang je groepeert op orders.id. Dat MySQL zo'n fout wel toestaat, is een tweede.
Voor het resultaat van deze query is het verder ook helemaal niet nodig om deze velden op te halen, dus ik zou dat gewoon achterwege laten.
Je hebt gelijk Blanche, ik dacht dat 'ie goed werkte. Maar er komt alleen een juist totaalbedrag/totaal aantal uit wanneer er maar 1 product is opgenomen in de order. Met andere woorden, de query levert alleen maar het resultaat van de eerste orderregel, en niet van alle orderregels die zijn opgenomen in een order.
Omdat ik op order (orders.id) groepeer kan ik niet data uit verschillende orderregels ophalen, begrijp ik.
Maar ik zou het toch erg mooi vinden om het order_totaal op te nemen in het overzicht, zodat ik de query er ook op kan sorteren (ASC/DESC). Daarvoor is 1 query nodig, maar is dat uberhaupt mogelijk?
Anders zal ik toch een tweede query gebruiken die per $row die het terugkrijgt uit de hoofdquery een SELECT statement uitvoert om de orderregels op te halen uit de order mbv van $row['id'] (orders.id)
Misschien niet zo'n elegante oplossing, maar het is natuurlijk fijn voor de gebruiker om in het overzicht de totale waarde van een order te kunnen zien.
Nadeel is wel dat de tabel niet gesorteerd kan worden op de totale waarde, omdat deze niet uit de hoofdquery komt.
Jullie kunnen mijn vorige berichtje negeren. Ik had een foutje zitten (met de sleutel) in de orderregels-tabel, waardoor de output er niet goed uitkwam.
Het werkt, ik kan nu sorteren op totale waarde en totaal aantal artikelen in een order (mbv de subquery die Blanche me eerder gaf). Natuurlijk heb ik geen lijstje van bestelde producten in het overzicht, maar dat hoefde ik ook niet.
Ik heb orderregels.orders_id en orderregels.producten_id weggelaten, omdat dat inderdaad niet zal werken omdat ik op orders groepeer (ik heb deze gegevens ook niet nodig).
Zouden jullie kunnen controleren of deze zo goed is?
SELECT orders.id, orders.datum, orders.status, CONCAT(klanten.achternaam, ', ', klanten.voornaam) as naam, (SELECT SUM(aantal * prijs) FROM orderregels WHERE orders_id = orders.id) AS order_totaal, (SELECT SUM(aantal) FROM orderregels WHERE orders_id = orders.id) AS aantal_totaal
FROM orders, klanten, orderregels, producten
WHERE orderregels.orders_id = orders.id
AND producten.id = orderregels.producten_id
AND klanten.id = orders.klanten_id
GROUP BY orders.id
ORDER BY orders.id DESC;
Tja, als je me bedankt zal ik toch nog maar commentaar leveren op de onjuistheden in
SELECT orders.id, orders.datum, orders.status, CONCAT(klanten.achternaam, ', ', klanten.voornaam) as naam, (SELECT SUM(aantal * prijs) FROM orderregels WHERE orders_id = orders.id) AS order_totaal, (SELECT SUM(aantal) FROM orderregels WHERE orders_id = orders.id) AS aantal_totaal
FROM orders, klanten, orderregels, producten
WHERE orderregels.orders_id = orders.id
AND producten.id = orderregels.producten_id
AND klanten.id = orders.klanten_id
GROUP BY orders.id
ORDER BY orders.id DESC;
Kan me niet voorstellen dat dit in alle situaties correcte resultaten oplevert. Mijn inzicht is dat die GROUP BY orders.id niet in de hoofdquery thuis hoort en de group by is ook nog eens incompleet.
En joinen in de select niet echt aan te bevelen....
