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)

  • 1
    Show the solution you've found and what is exactly the problem with adding quantity to comparing sets. – Serg Jun 2 '16 at 14:59
  • dba.stackexchange.com/questions/45512/… "there is a UNIQUE constraint on (wordid, docid)" – user3416510 Jun 2 '16 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 '16 at 17:37
  • so like one join per item ? – user3416510 Jun 2 '16 at 17:50
  • 1
    Please provide sample data in Mysql consumable format to show it in action. – Serg Jun 2 '16 at 17:57
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.

  • thanks, however that looks not efficient. Any way to search without comparing strings ? – user3416510 Jun 3 '16 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. – Ziggy Crueltyfree Zeitgeister Jun 3 '16 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 ... – user3416510 Jun 3 '16 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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