Query uitbreiden
Ik heb een query met Select.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
SELECT
IF (SUBSTRING(prstshp_product_lang.name, 1, 2) = '1 ',
LOWER( SUBSTRING_INDEX(
SUBSTRING(
prstshp_product_lang.name,
LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) ) + 2,
LENGTH( prstshp_product_lang.name ) - LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) )
), ' - ', 1 ) ),
LOWER( SUBSTRING_INDEX( prstshp_product_lang.name, ' - ', 1 ) )
) AS soort,
SUM(
IF (prstshp_product.product_type = 'pack',
prstshp_order_detail.product_quantity * prstshp_pack.quantity,
prstshp_order_detail.product_quantity
)
) AS AantalInSoort
FROM prstshp_orders
LEFT JOIN prstshp_order_detail
on prstshp_order_detail.id_order = prstshp_orders.id_order
LEFT JOIN prstshp_pack
on prstshp_pack.id_product_pack = prstshp_order_detail.product_id
LEFT JOIN prstshp_product
ON (prstshp_product.id_product = prstshp_order_detail.product_id AND prstshp_product.product_type = '')
OR (prstshp_product.id_product = prstshp_pack.id_product_item )
LEFT JOIN prstshp_product_lang
on prstshp_product_lang.id_product = prstshp_product.id_product
AND prstshp_product_lang.id_lang = 1
AND prstshp_product_lang.id_shop = prstshp_orders.id_shop
WHERE ".$current_status."
GROUP BY
prstshp_orders.id_order, soort;
IF (SUBSTRING(prstshp_product_lang.name, 1, 2) = '1 ',
LOWER( SUBSTRING_INDEX(
SUBSTRING(
prstshp_product_lang.name,
LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) ) + 2,
LENGTH( prstshp_product_lang.name ) - LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) )
), ' - ', 1 ) ),
LOWER( SUBSTRING_INDEX( prstshp_product_lang.name, ' - ', 1 ) )
) AS soort,
SUM(
IF (prstshp_product.product_type = 'pack',
prstshp_order_detail.product_quantity * prstshp_pack.quantity,
prstshp_order_detail.product_quantity
)
) AS AantalInSoort
FROM prstshp_orders
LEFT JOIN prstshp_order_detail
on prstshp_order_detail.id_order = prstshp_orders.id_order
LEFT JOIN prstshp_pack
on prstshp_pack.id_product_pack = prstshp_order_detail.product_id
LEFT JOIN prstshp_product
ON (prstshp_product.id_product = prstshp_order_detail.product_id AND prstshp_product.product_type = '')
OR (prstshp_product.id_product = prstshp_pack.id_product_item )
LEFT JOIN prstshp_product_lang
on prstshp_product_lang.id_product = prstshp_product.id_product
AND prstshp_product_lang.id_lang = 1
AND prstshp_product_lang.id_shop = prstshp_orders.id_shop
WHERE ".$current_status."
GROUP BY
prstshp_orders.id_order, soort;
De uitkomst van deze query zijn 2 velden: soort en AantalInSoort
Ik wil de regels die hierin voorkomen, vergelijken met een database tabel: prstshp_collect_productstype_packing
Deze tabel bevat:
- id (autom.nummer)
- producttype (text)
- quantity (int)
- id_packing (maar deze is even niet van belang)
De regels die er nog niet in voorkomen, moeten er aan worden toegevoedgd.
Ik ben benieuwd of iemand een oplossing weet.
Alvast bedankt,
Frits
Gewijzigd op 04/10/2023 22:32:13 door - Ariën -
Code (php)
1
2
3
2
3
INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, SUM(AantalInSoort)
FROM (.............
SELECT soort, SUM(AantalInSoort)
FROM (.............
En dit op het einde:
Code (php)
1
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);
Gewijzigd op 04/10/2023 22:31:35 door - Ariën -
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
WITH nieuw AS ( -- rekent tussentijds resultaat uit
SELECT soort, AantalInSoort
FROM (<je query>) AS bron
LEFT JOIN prstshp_collect_productstype_packing AS doel
ON bron.soort = doel.producttype
WHERE doel.id IS NULL -- rij is niet aanwezig in doeltabel
)
INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, AantalInSoort
FROM nieuw
SELECT soort, AantalInSoort
FROM (<je query>) AS bron
LEFT JOIN prstshp_collect_productstype_packing AS doel
ON bron.soort = doel.producttype
WHERE doel.id IS NULL -- rij is niet aanwezig in doeltabel
)
INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, AantalInSoort
FROM nieuw
Dit voegt de regels toe die nog niet voorkomen zoals je vroeg.
Het tussentijdse resultaat is van belang omdat je niet tegelijkertijd data kan selecteren uit de tabel die je gaat wijzigen.
Gewijzigd op 05/10/2023 10:13:07 door Ad Fundum
En ja ik gebruik Prestashop. Maar ik heb er wel wat tabellen naast gebouwd en wat php pagina's om onze verzending beter te stroomlijnen. Ik wil nu per order bekijken welke verpakking in nodig heb. Het vullen van de eerder genoemde tabel is daar ook een onderdeel van.
producttype is van het type text met utf8_general_ci
quantity is van het type int(11)
Ik weet niet of dat dat juist is als ik het vergelijk met de query. Maar ik denk van wel.
Toevoeging op 05/10/2023 13:32:59:
Ik probeer hem eerst uit voor ik het in php code ga zetten.
In PHPMyAdmin krijg ik de volgende foutmelding:
Code (php)
1
2
2
#1064 - Er is iets fout in de gebruikte syntax bij 'INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SEL...' in regel 55
SEL...' in regel 55
de code zoals ik die nu heb is als volgt:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
WITH nieuw AS ( -- rekent tussentijds resultaat uit
SELECT soort, AantalInSoort
FROM (
SELECT
IF (SUBSTRING(prstshp_product_lang.name, 1, 2) = '1 ',
LOWER( SUBSTRING_INDEX(
SUBSTRING(
prstshp_product_lang.name,
LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) ) + 2,
LENGTH( prstshp_product_lang.name ) - LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) )
), ' - ', 1 ) ),
LOWER( SUBSTRING_INDEX( prstshp_product_lang.name, ' - ', 1 ) )
) AS soort,
SUM(
IF (prstshp_product.product_type = 'pack',
prstshp_order_detail.product_quantity * prstshp_pack.quantity,
prstshp_order_detail.product_quantity
)
) AS AantalInSoort
FROM prstshp_orders
LEFT JOIN prstshp_order_detail
on prstshp_order_detail.id_order = prstshp_orders.id_order
LEFT JOIN prstshp_pack
on prstshp_pack.id_product_pack = prstshp_order_detail.product_id
LEFT JOIN prstshp_product
ON (prstshp_product.id_product = prstshp_order_detail.product_id AND prstshp_product.product_type = '')
OR (prstshp_product.id_product = prstshp_pack.id_product_item )
LEFT JOIN prstshp_product_lang
on prstshp_product_lang.id_product = prstshp_product.id_product
AND prstshp_product_lang.id_lang = 1
AND prstshp_product_lang.id_shop = prstshp_orders.id_shop
WHERE current_state=2
OR current_state=3
OR current_state=11
OR current_state=20
GROUP BY
prstshp_orders.id_order, soort
) AS bron
LEFT JOIN prstshp_collect_productstype_packing AS doel
ON bron.soort = doel.producttype
WHERE doel.id IS NULL -- rij is niet aanwezig in doeltabel
)
INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, AantalInSoort
FROM nieuw;
SELECT soort, AantalInSoort
FROM (
SELECT
IF (SUBSTRING(prstshp_product_lang.name, 1, 2) = '1 ',
LOWER( SUBSTRING_INDEX(
SUBSTRING(
prstshp_product_lang.name,
LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) ) + 2,
LENGTH( prstshp_product_lang.name ) - LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) )
), ' - ', 1 ) ),
LOWER( SUBSTRING_INDEX( prstshp_product_lang.name, ' - ', 1 ) )
) AS soort,
SUM(
IF (prstshp_product.product_type = 'pack',
prstshp_order_detail.product_quantity * prstshp_pack.quantity,
prstshp_order_detail.product_quantity
)
) AS AantalInSoort
FROM prstshp_orders
LEFT JOIN prstshp_order_detail
on prstshp_order_detail.id_order = prstshp_orders.id_order
LEFT JOIN prstshp_pack
on prstshp_pack.id_product_pack = prstshp_order_detail.product_id
LEFT JOIN prstshp_product
ON (prstshp_product.id_product = prstshp_order_detail.product_id AND prstshp_product.product_type = '')
OR (prstshp_product.id_product = prstshp_pack.id_product_item )
LEFT JOIN prstshp_product_lang
on prstshp_product_lang.id_product = prstshp_product.id_product
AND prstshp_product_lang.id_lang = 1
AND prstshp_product_lang.id_shop = prstshp_orders.id_shop
WHERE current_state=2
OR current_state=3
OR current_state=11
OR current_state=20
GROUP BY
prstshp_orders.id_order, soort
) AS bron
LEFT JOIN prstshp_collect_productstype_packing AS doel
ON bron.soort = doel.producttype
WHERE doel.id IS NULL -- rij is niet aanwezig in doeltabel
)
INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, AantalInSoort
FROM nieuw;
Frits van Leeuwen op 05/10/2023 12:16:26:
producttype is van het type text met utf8_general_ci
quantity is van het type int(11)
quantity is van het type int(11)
Kan je negeren tot het misgaat; MySQL / MariaDB doen net als PHP aan impliciete type casting, dwz. ze passen het datatype aan naar gelang de producten denken dat het goed is, ook al is dat soms niet precies wat je bedoelt (en dan pas moet je bijsturen).
Als je op mijn spoor zit en liever voorkomt dan geneest, moet je een andere database als PostgreSQL gebruiken, of zelf heel scherp in de gaten houden wat MySQL / MariaDB aan het doen is. En dat is geen doen.
Ik ben nog niet zo thuis in SQL dat ik deze variant ken, maar aangezien het "nieuw" als een tabel in het geheugen wordt gemaakt en vervolgens in de INSERT wordt gebruikt, lijkt mij dat hier geen ; moet. Maar misschien dat "Ad Fundum" dit beter kan verklaren.
Gewijzigd op 06/10/2023 12:22:34 door Frits van Leeuwen
De syntax in mijn vorige vorige post heet CTE (Common Table Expressions), en kan je gebruiken om queries te versimpelen en om zogenaamde recursieve queries te maken.
In mijn post gebruik ik het, omdat het resultaat van een CTE eerst wordt uitgerekend door de database, waarna je het resultaat kan gebruiken in een andere query. Zonder dat het tussentijdse resultaat nog verandert.
Zie ook:
- https://www.mysqltutorial.org/mysql-cte/
- https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions
- https://stackoverflow.com/questions/15574281/using-the-with-clause-in-an-insert-statement
Gewijzigd op 06/10/2023 14:09:16 door Ad Fundum
Waar ik nu over struikel is regel 55. Er zou iets fout zijn, maar ik heb geen idee wat. Want als ik die regel weg laat, vervolgt hij met de SELECT regel en dan krijg ik keurig een lijstje. Maar ik wil natuurlijk de INSERT INTO regel wel er in hebben. Anders krijg ik mijn doel niet. of ik moet het met PHP gaan oplossen. Dat zou jammer zijn.
Dus mijn suggestie werkt niet in MySQL.
Ik zou nog kunnen opperen om het het herschrijven zonder WITH:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, AantalInSoort
FROM (
SELECT soort, AantalInSoort
FROM (<je query>) AS bron
LEFT JOIN prstshp_collect_productstype_packing AS doel
ON bron.soort = doel.producttype
WHERE doel.id IS NULL
) AS nieuw
SELECT soort, AantalInSoort
FROM (
SELECT soort, AantalInSoort
FROM (<je query>) AS bron
LEFT JOIN prstshp_collect_productstype_packing AS doel
ON bron.soort = doel.producttype
WHERE doel.id IS NULL
) AS nieuw
Maar ik kan daarvan niet inschatten of dat het gewenste resultaat oplevert omdat je tegelijkertijd leest en schrijft naar dezelfde tabel. Dus het beste dat ik je kan aanraden is om het in PHP te doen omdat MySQL het niet kan.
Toevoeging op 06/10/2023 17:10:10:
Oké, dat is wel pessimistisch gesteld. Je kunt (zoals alles in MySQL) het werk zelf doen en zelf een tijdelijke tabel aanmaken met het tussenresultaat.
Code (php)
1
2
3
4
5
6
2
3
4
5
6
CREATE TEMPORARY TABLE nieuw AS
SELECT soort, AantalInSoort
FROM (<je query>) AS bron
LEFT JOIN prstshp_collect_productstype_packing AS doel
ON bron.soort = doel.producttype
WHERE doel.id IS NULL;
SELECT soort, AantalInSoort
FROM (<je query>) AS bron
LEFT JOIN prstshp_collect_productstype_packing AS doel
ON bron.soort = doel.producttype
WHERE doel.id IS NULL;
En daarna:
Code (php)
1
2
2
INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, AantalInSoort FROM nieuw;
SELECT soort, AantalInSoort FROM nieuw;
Om veilig te zitten zou je beide queries in een transactie moeten zetten:
Code (php)
1
2
3
4
2
3
4
START TRANSACTION;
<eerste query>
<tweede query>
COMMIT;
<eerste query>
<tweede query>
COMMIT;
Maar het was leuker geweest als MySQL (net als alle andere databases) gewoon WITH ... INSERT INTO had ondersteund.
Gewijzigd op 06/10/2023 15:52:52 door Ad Fundum
Ik kan me voorstellen (na jouw uitleg) dat dit ook niet gaat werken. Dat moet dan inderdaad zijn omdat je schrijft en leest in de zelfde tabel.
Ik had eerder ook aan deze oplossing gedacht, maar dat lukte toen ook niet. Maar dat kan zijn omdat mijn insteek fout was.
Nu lukt het wel. Hartelijk dank.
De code die uiteindelijk werkt is:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
INSERT INTO prstshp_collect_productstype_packing (producttype, quantity)
SELECT soort, aantalinsoort
FROM (
SELECT soort, aantalinsoort
FROM (
-- start bron
SELECT
IF (SUBSTRING(prstshp_product_lang.name, 1, 2) = '1 ',
LOWER( SUBSTRING_INDEX(
SUBSTRING(
prstshp_product_lang.name,
LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) ) + 2,
LENGTH( prstshp_product_lang.name ) - LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) )
), ' - ', 1 ) ),
LOWER( SUBSTRING_INDEX( prstshp_product_lang.name, ' - ', 1 ) )
) AS soort,
SUM(
IF (prstshp_product.product_type = 'pack',
prstshp_order_detail.product_quantity * prstshp_pack.quantity,
prstshp_order_detail.product_quantity
)
) AS aantalinsoort
FROM prstshp_orders
LEFT JOIN prstshp_order_detail
on prstshp_order_detail.id_order = prstshp_orders.id_order
LEFT JOIN prstshp_pack
on prstshp_pack.id_product_pack = prstshp_order_detail.product_id
LEFT JOIN prstshp_product
ON (prstshp_product.id_product = prstshp_order_detail.product_id AND prstshp_product.product_type = '')
OR (prstshp_product.id_product = prstshp_pack.id_product_item )
LEFT JOIN prstshp_product_lang
on prstshp_product_lang.id_product = prstshp_product.id_product
AND prstshp_product_lang.id_lang = 1
AND prstshp_product_lang.id_shop = prstshp_orders.id_shop
WHERE current_state=2
OR current_state=3
OR current_state=11
OR current_state=20
GROUP BY
prstshp_orders.id_order, soort
) AS bron
LEFT JOIN prstshp_collect_productstype_packing AS doel
ON bron.soort = doel.producttype
WHERE doel.id IS NULL
GROUP BY soort, aantalinsoort
) AS nieuw
SELECT soort, aantalinsoort
FROM (
SELECT soort, aantalinsoort
FROM (
-- start bron
SELECT
IF (SUBSTRING(prstshp_product_lang.name, 1, 2) = '1 ',
LOWER( SUBSTRING_INDEX(
SUBSTRING(
prstshp_product_lang.name,
LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) ) + 2,
LENGTH( prstshp_product_lang.name ) - LENGTH( SUBSTRING_INDEX( prstshp_product_lang.name, ' ', 2 ) )
), ' - ', 1 ) ),
LOWER( SUBSTRING_INDEX( prstshp_product_lang.name, ' - ', 1 ) )
) AS soort,
SUM(
IF (prstshp_product.product_type = 'pack',
prstshp_order_detail.product_quantity * prstshp_pack.quantity,
prstshp_order_detail.product_quantity
)
) AS aantalinsoort
FROM prstshp_orders
LEFT JOIN prstshp_order_detail
on prstshp_order_detail.id_order = prstshp_orders.id_order
LEFT JOIN prstshp_pack
on prstshp_pack.id_product_pack = prstshp_order_detail.product_id
LEFT JOIN prstshp_product
ON (prstshp_product.id_product = prstshp_order_detail.product_id AND prstshp_product.product_type = '')
OR (prstshp_product.id_product = prstshp_pack.id_product_item )
LEFT JOIN prstshp_product_lang
on prstshp_product_lang.id_product = prstshp_product.id_product
AND prstshp_product_lang.id_lang = 1
AND prstshp_product_lang.id_shop = prstshp_orders.id_shop
WHERE current_state=2
OR current_state=3
OR current_state=11
OR current_state=20
GROUP BY
prstshp_orders.id_order, soort
) AS bron
LEFT JOIN prstshp_collect_productstype_packing AS doel
ON bron.soort = doel.producttype
WHERE doel.id IS NULL
GROUP BY soort, aantalinsoort
) AS nieuw
Gewijzigd op 06/10/2023 23:59:21 door Frits van Leeuwen