[MYSQL] Productentabel met productcombinaties, combinatie

[Tijsje]

Ik heb een tabel Producten, in deze tabel zitten losse producten maar ook productcombinaties.
De tabel heeft dus een veel op veel relatie met zichzelf, dmv de koppeltabel Product_CombinatieBestaatUit.

Voorbeeldje:
ID 1: Playstation Fifa Set - Product_CombinatieBestaatUit 2,3,5
ID 2: Playstation
ID 3: Fifa
ID 4: Playstaion Gun
ID 5: Playstation controller

Is het met 1 query mogelijk om "ID 1: Playstation Fifa Set" vinden als ik ID 2,3 en 5 beschikbaar heb?

Als ik onderstaande query run krijg ik alle combinaties die 1 van de producten bevat. Maar ik wil dus alleen een combinatie die alle producten bevat, zonder extra producten.

code:

SELECT DISTINCT Product.ID FROM Product 
LEFT JOIN Product_CombinatieBestaatUit ON Product_CombinatieBestaatUit.ProductID = Product.ID 
WHERE  (Product_CombinatieBestaatUit.ChildID IN (2,4,5))

Ik de koppeltabel moet hij dus groeperen op het ProductID, en dan filteren op combinaties die ChildID 2,3 en 5 alleen bevatten.

Ik hoop dat het zo een beetje duidelijk is. Kan iemand mij verder helpen?

[Morax]

Wat je dus eigenlijk wilt is 2 sets met elkaar vergelijken. Je zou kunnen overwegen een group_by toe te voegen, en met een COUNT te tellen hoeveel matches daaruit voort komen. Is het aantal matches gelijk aan het aantal ID's in de IN-clause, dan zitten ze er allen in

SQL:

SELECT Product.ID, COUNT(Product_CombinatieBestaatUit.ChildID) AS combination_matches FROM Product 
LEFT JOIN Product_CombinatieBestaatUit ON Product_CombinatieBestaatUit.ProductID = Product.ID 
WHERE  (Product_CombinatieBestaatUit.ChildID IN (2,4,5))
GROUP BY Product.ID
HAVING combination_matches = 3

Let er dan dus wel op dat als je een combinatie van 5 producten zoekt, de HAVING ook zoekt op combination_matches = 5

[ Voor 48% gewijzigd door Morax op 29-09-2016 11:51 ]

[Tijsje]

Morax schreef op donderdag 29 september 2016 @ 11:47:
Wat je dus eigenlijk wilt is 2 sets met elkaar vergelijken. Je zou kunnen overwegen een group_by toe te voegen, en met een COUNT te tellen hoeveel matches daaruit voort komen. Is het aantal matches gelijk aan het aantal ID's in de IN-clause, dan zitten ze er allen in

SQL:

SELECT Product.ID, COUNT(Product_CombinatieBestaatUit.ChildID) AS combination_matches FROM Product 
LEFT JOIN Product_CombinatieBestaatUit ON Product_CombinatieBestaatUit.ProductID = Product.ID 
WHERE  (Product_CombinatieBestaatUit.ChildID IN (2,4,5))
GROUP BY Product.ID
HAVING combination_matches = 3

Let er dan dus wel op dat als je een combinatie van 5 producten zoekt, de HAVING ook zoekt op combination_matches = 5

Ik heb ondertussen dit werkend:

code:

SELECT Product.ID, Title, GROUP_CONCAT(`ChildID` ORDER BY `ChildID`) AS Combination FROM Product,Product_CombinatieBestaatUit
WHERE Product_CombinatieBestaatUit.ProductID = Product.ID
GROUP BY ProductID
HAVING Combination = '2,4,5

Ga jouw query ook eens testen.
Jouw query geeft ook resultaten terug van combinaties die maar 1 van de combinatieitems bevatten maar wel uit een net zoveel items bestaan.

[ Voor 6% gewijzigd door Tijsje op 29-09-2016 12:09 ]

[Tijsje]

Even ter volledigheid.
Onderstaande query doet wat ik wil. Het is wel van belang dat de volgorde in de Having Combination hetzelfde is als wat er uit de GROUP_CONCAT komt.

code:

SELECT Product.ID, Title, GROUP_CONCAT(`ChildID` ORDER BY `ChildID`) AS Combination FROM Product,Product_CombinatieBestaatUit WHERE Product_CombinatieBestaatUit.ProductID = Product.ID GROUP BY ProductID HAVING Combination = '2,4,5

Of heeft er iemand nog een betere efficiëntere methode?

[Guillome]

Ik zou toch met de methode van Morax verder, eventueel een distinct in de Count(). Maar waarom geeft dat ook combinaties met 1 van de 3 terug? Heb je dan 3 keer product 1 in de combinatie bijv?
In dat geval helpt de distinct wat ik noemde, maar hoe wil je dat dan terug gaan vinden als je 1 product vaker in een combinatie hebt zitten?

En ook: waarom wil je dit? Ben wel benieuwd

[ajakkes]

Het lijkt er op dat hij bijvoorbeeld de klant wil aanbieden de combinatie te kopen als alle drie de producten los in de winkelwagen liggen.

Vraag me af of hij ook de aanbieding wil doen wanneer er 2 van de 3 producten in de mand zitten. Om het derde product ook te kiezen, wat je veel ziet bij webwinkels.

[Morax]

Tijsje schreef op donderdag 29 september 2016 @ 12:01:
[...]


Ik heb ondertussen dit werkend:

code:

SELECT Product.ID, Title, GROUP_CONCAT(`ChildID` ORDER BY `ChildID`) AS Combination FROM Product,Product_CombinatieBestaatUit
WHERE Product_CombinatieBestaatUit.ProductID = Product.ID
GROUP BY ProductID
HAVING Combination = '2,4,5

Ga jouw query ook eens testen.
Jouw query geeft ook resultaten terug van combinaties die maar 1 van de combinatieitems bevatten maar wel uit een net zoveel items bestaan.

Dat zou niet moeten kunnen volgens mij, tenzij er situaties zijn waarbij een combinatie hetzelfde product meerdere keren bevat. Dat is overigens op te lossen door enkel de unieke product ids mee te nemen in de count.

SQL:

SELECT Product.ID, COUNT(DISTINCT Product_CombinatieBestaatUit.ChildID) AS combination_matches FROM Product 
LEFT JOIN Product_CombinatieBestaatUit ON Product_CombinatieBestaatUit.ProductID = Product.ID 
WHERE  (Product_CombinatieBestaatUit.ChildID IN (2,4,5))
GROUP BY Product.ID
HAVING combination_matches = 3

[Tijsje]

Het gaat om een bestel flow waarmee een basis product aangekleed wordt met upsells.
De bestelling moet uiteindelijk geëxporteerd worden naar een SAP systeem, in SAP is voor iedere combinatie een apart artikel aangemaakt schijnbaar.
Het is dus om de bestelling terug te converteren naar een artikelnummer in SAP.

[pedorus]

Het probleem met die andere query is dat je nog wat extra moet uitsluiten. Afhankelijk van hoe vaak die ChildIDs voorkomen, kan ik me voorstellen dat zoeken wel sneller is dan voor alles steeds een GROUP_CONCAT te moeten berekenen. Dan krijg je voor de uitsluiting iets als (naar Morax hierboven):

SQL:

SELECT Product.ID FROM Product 
JOIN Product_CombinatieBestaatUit A ON A.ProductID = Product.ID AND (A.ChildID IN (2,3,5))
LEFT JOIN Product_CombinatieBestaatUit B ON B.ProductID = Product.ID AND (NOT B.ChildID IN (2,3,5))
WHERE B.ProductID is null
GROUP BY Product.ID
HAVING COUNT(Product_CombinatieBestaatUit.ChildID) = 3

OF iets met een dubbele not exists is ook mogelijk om de having te voorkomen (en voor de variatie not exists doorgezet):

SQL:

select P.ID from Product P
where not exists (select * from Product_CombinatieBestaatUit A1 where A1.ChildID in (2,3,5) and not exists 
  (select * from Product_CombinatieBestaatUit A2 where A1.ChildID = A2.ChildID and A2.ProductID = P.ID))
  and not exists 
  (select * from Product_CombinatieBestaatUit B where B.ProductID = P.ID and (not B.ChildID in (2,3,5)))

In deze laatste zit een assumptie over Product_CombinatieBestaatUit A1, eigenlijk wil je daarvoor een tabel met alle mogelijke componenten gebruiken als ze niet allemaal voorkomen in Product_CombinatieBestaatUit..

[ Voor 8% gewijzigd door pedorus op 04-10-2016 21:43 ]