Lastige query over drie tabellen
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:
Code (php)
1
2
3
4
5
2
3
4
5
$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";
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.
Hoe zou ik dat het best kunnen doen?
Alvast heel erg bedankt,
Robbert
Code (php)
1
GROUP BY orders.id
komt er nog achteraan. Het komt er allemaal goed uit, maar waar ik benieuwd naar ben is hoe ik de totale waarde van de order laat zien.
Dus toch maar het advies van Klaasjan opvolgen
http://www.w3schools.com/sql/sql_groupby.asp
Gewijzigd op 01/01/1970 01:00:00 door Noppes
Een niet goed ingestelde MySQL database doet vermoeden dat de query wel goed uitgevoerd wordt, maar in werkelijkheid worden er gewoon willekeurige waarden voor die velden geretourneerd. Zie ook deze handleiding over het juiste gebruik van GROUP BY.
Het komt er dus op neer dat je aparte queries uit zult moeten voeren voor het opvragen van de orderdetails (id, orderregels en bijbehorende producten) en voor het berekenen van aantallen en totalen.
Nop niet noodzakelijkerwijs nodig... het kan in 1 query
Noppes schreef op 07.06.2009 22:41:
De query zoals hij daar staat levert geen correcte resultaten op. Zodra je groepeert op orders.id is het onmogelijk om bijvoorbeeld nog alle orderregels.product_id apart op te halen. Als je de missende velden dan aan je GROUP BY zou toevoegen (ie. orderregels.product_id), leveren de aggregate functies niet meer de gewenste resultaten.Nop niet noodzakelijkerwijs nodig... het kan in 1 query
ps. Net zo min is het bepalen van het totaal van alle orders mogelijk in deze query, aangezien je dan alle orders als 1 groep beschouwt en je dus geen afzonderlijke gegevens per order kunt selecteren.
pps. Wat betreft de totaalprijs van de orders is er trouwens nog een belangrijk feit waar je rekening mee moet houden. Zodra een order geplaatst wordt, ligt de prijs daarvan vast. Prijzen van producten afzonderlijk kunnen in de toekomst echter wijzigen waardoor in de huidige opzet de orderprijs ook zou wijzigen. Dat is ongewenst en dus zou je ervoor moeten kiezen om de prijs van de order ook in de orders tabel op te nemen. Het berekenen van een totaalprijs van alle orders is in dat geval een fluitje van een cent!
Gewijzigd op 01/01/1970 01:00:00 door Joren de Wit
Bedankt voor je reactie, maar volgens mij is er niets mis met mijn query, het geeft juiste resultaten, er groepeert mooi op orders.id.
En volgens mij worden er ook geen 'willekeurige waarden' geretourneerd.
Ik zat ook te denken aan aparte queries, maar toen dacht ik dat het vast wel in 1 query moet kunnen, ik weet alleen niet hoe.
Omdat je moet vermenigvuldigen en optellen.
Je moet de prijs nemen, die in de tabel PRODUCTEN staat, en die vermenigvuldigen met het aantal dat in de tabel ORDERREGELS staat, wanneer deze twee tabellen met elkaar gelinkt zijn dmv de id's (in ORDERREGELS staat producten_id).
En dat moet dan voor ieder product dat in de order is opgenomen.
Ik loop al vast als ik eraan denk. Noppes, jij zegt dat het in één query kan, hoe zou ik dat kunnen doen?
Groeten,
Robbert
Ik zal de prijs van het moment opnemen in de ORDERREGELS-entry, zodat wanneer de prijs verandert in PRODUCTEN een eerdere order niet wordt aangetast.
Blanche, zou je dan weten hoe ik dan die informatie die ik graag wil hebben, zoals welke producten zijn opgenomen in een order, en wat voor totale waarde die dan hebben, kan opvragen met een query?
Quote:
Zoals je zelf al ziet, zijn dat twee verschillende vragen dus zul je daar ook twee verschillende queries voor moeten gebruiken.welke producten zijn opgenomen in een order, en wat voor totale waarde die dan hebben
In eerste instantie 1 query waarmee je alle productinformatie per order ophaalt en ten tweede een waarmee je de totale waarde van alle orderregels (de som van de nieuwe waardes die je per orderregel opslaat) bepaalt.
Dus iets als:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
SELECT
p.naam,
p.omschrijving,
o.aantal
FROM
orderregels AS o
INNER JOIN
producten AS p
ON p.id = o.product_id
WHERE
o.order_id = 123
p.naam,
p.omschrijving,
o.aantal
FROM
orderregels AS o
INNER JOIN
producten AS p
ON p.id = o.product_id
WHERE
o.order_id = 123
Dit haalt alle van alle producten van order 123 de naam, omschrijving en aantal op.
Code (php)
1
2
3
4
5
6
2
3
4
5
6
SELECT
SUM(totaal) AS order_totaal
FROM
orderregels
WHERE
o.order_id = 123
SUM(totaal) AS order_totaal
FROM
orderregels
WHERE
o.order_id = 123
En dit bepaalt de totaalprijs van order 123.
Maar... ik begrijp het niet helemaal.
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.
In dat geval zou ik een subquery gebruiken om per order de totaalprijs uit de orderregels tabel te bepalen:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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
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.
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
$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;
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?
a = b is namelijk ook een INNER JOIN
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.
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.
Adviezen?
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?
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
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;
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;
Mvg,
Robbert
Ontzettend bedankt voor je hulp!
Klaasjan en Noppes ook bedankt :)
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....