0

Imagine I have a basket from an online shop, with id of item and the quantity. I have a list of items of quantity and I would like to find a basket with the exact same items and quantity for each.

I have found solutions to find matching sets, but not with duplicates or quantity field.

The basket could contain multiple times the same item id, or I could also choose to have a quantity field, depending on what's better

thanks

two solutions I see for database format :

id_basket id_product quantity
1           1           2
1           2           1
2           1           2
2           2           1
3           1           1
3           2           1

or

id_basket id_product 
1           1
1           1
1           2       
2           1
2           1       
2           2  
3           1
3           2

basket 1 and 2 contain the same thing, but not the third, and I would like to be able to find a basket that contains a list of product using "raw" data, so the idea is not to find duplicates on the database, but find one that matches a static list (like find a basket that contain 2x item 1 and 1x item 2)

6
  • 1
    Show the solution you've found and what is exactly the problem with adding quantity to comparing sets.
    – Serg
    Jun 2, 2016 at 14:59
  • dba.stackexchange.com/questions/45512/… "there is a UNIQUE constraint on (wordid, docid)" Jun 2, 2016 at 15:01
  • In MySql fist have baskets totals and basket-item subtotals . Then do triangle self join of baskets by equal totals and intesect basket's items subtotals. MySql has no intersect directly, use full join on <=> item_id, item_quantity.
    – Serg
    Jun 2, 2016 at 17:37
  • so like one join per item ? Jun 2, 2016 at 17:50
  • 1
    Please provide sample data in Mysql consumable format to show it in action.
    – Serg
    Jun 2, 2016 at 17:57

1 Answer 1

0

You could create a query that first lists or groups elements as in your first example. Then select on that (as a subquery) grouping by basket and crating a string per basket through GROUP_CONCAT(). Then you can use those results (perhaps as another subquery) grouping by the group_concat string and having count(*) > 1 to find baskets with equal contents.

3
  • thanks, however that looks not efficient. Any way to search without comparing strings ? Jun 3, 2016 at 10:29
  • @myquestionmysql I'd be surprised of anything that's substantially more efficient without being significantly more complicated. If your business logic requires doing this dozens of times a second (which would warrant looking for the most efficient solution), you'd benefit from using a maintained materialised view with an indexed summary in the form of an array in PostgreSQL. Jun 3, 2016 at 11:48
  • no I don't need to use this 10s of times per second, if noone gives a better idea I will use this solution. Here is something based on your solution that works EXPLAIN SELECT *, GROUP_CONCAT(id_product) AS thegroup FROM basket_item GROUP BY id_basket HAVING thegroup = '1,1,2' However I think that once the table will be huge, that would take a lot of time ... Jun 3, 2016 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.