1

I need to fetch all orders which have the same cart (same list of order items and quantity).

Here are my tables:

orders (I think columns are not important, the primary key is id)

| id |
| -- |
| 1  |
| 2  |
| 3  |
| ... |

order_items

id order_id product_id quantity
1 100 1 20.00
2 100 2 22.00
3 107 1 20.00
4 110 1 20.00
5 110 2 22.00
... ... ... ...

My goal is to use SQL to get orders 100 and 110 in this case. (They need to have the same rows containing the same product_id and quantity so order items number can vary, this is just an example). As you can see order 107 have the same order item but it's missing product id 2 with the quantity of 22.00 so it doesn't need to be included.

It's important to mention that this table will be large and probably it will contain 1M+ rows, maybe even more.

The ultimate goal is to have those groups of orders, for example, [100-110] but in reality, it would be like [100-110-...-n, x-y-z-...-n, ...]. The number of the same orders is not limited, that's why I used n in the example. My example only contains 2 orders that are the same.

The only thing I have achieved so far is to get the first group of orders with the query I've found on Stackoverflow:

SELECT DISTINCT O2.id
FROM orders O1
         INNER JOIN order_items I1 ON O1.id = I1.order_id
         INNER JOIN order_items I2 ON I1.product_id = I2.product_id AND I1.quantity = I2.quantity
         INNER JOIN orders O2 ON O2.id = I2.order_id
WHERE O1.id =
      (SELECT id FROM orders ORDER BY created_at LIMIT 1)
  AND (SELECT COUNT(*) FROM order_items WHERE order_id = O1.id)
    = (SELECT COUNT(*) FROM order_items WHERE order_id = O2.id)
ORDER BY O2.id;

I tried to change it in order to return all orders but with no luck.

5
  • Do you need in completely equal orders pairs? Aggregate the order data then compare these aggregated values. PS. If one order contains item=1 amount=10 and another one contains 2 rows for item=1 with anounts 4 and 6 - does these orders are equal?
    – Akina
    Oct 19, 2022 at 14:30
  • @Akina The order cannot have 2 items with different amounts. It would be 10 instead of 2 separate rows for the same item. Oct 19, 2022 at 14:34
  • The order cannot have 2 items with different amounts Does UNIQUE index by order_items (order_id, product_id) exists? if not then those duplicates may exist, nothing in the structure prevents this. Simply add complete CREATE TABLE for this table to the question.
    – Akina
    Oct 19, 2022 at 14:38
  • There is no unique index on (order_id, product_id) but I can add it. Oct 19, 2022 at 16:05
  • @AleksandarMilivojsa - you should have a UNIQUE index on (order_id, product_id) - it makes no sense to have the same item twice in the same order when you should be adding the totals!
    – Vérace
    Oct 19, 2022 at 16:43

2 Answers 2

1

I believe all you need to do is use a window function to COUNT() how many products there are in every order, then join it to itself by product_id, quantity, and that product_count. That'll get you potential matches (which would include subset matches like two different orders with the same counts but each have one different product). Then with a window function again count how many rows actually matched between two orders and if the count of the matched rows is the same as the count of products in those orders then you know it's an exact match.

Something like this may work:

WITH order_items_counted AS
(
    SELECT
        id,
        order_id,
        product_id,
        quantity,
        COUNT(product_id) OVER (PARTITION BY order_id) AS product_count -- Counts how many products are in each order
    FROM order_items
),
potential_matches AS
(
    SELECT DISTINCT
        O1.order_id,
        O1.product_count,
        COUNT(O1.id) OVER (PARTITION BY O1.order_id, O2.order_id) AS matched_row_count -- Counts how many order_line rows matched
    FROM order_items_counted AS O1
    INNER JOIN order_items_counted AS O2
        ON O1.order_id <> O2.order_id -- Different Orders
        AND O1.product_count = O2.product_count -- With the same # of Products
        AND O1.product_id = O2.product_id -- Which are the same Products
        AND O1.quantity = O2.quantity -- With the same Quantities
)

-- Finally get the exact matches by filtering out any partial matches (the matched row count <> the product count)
SELECT DISTINCT
    order_id
FROM potential_matches AS exact_matches
WHERE product_count = matched_row_count

I don't have a way to test it at the moment, so please let me know if you have any issues and I'll address them accordingly.

2
  • Thanks! It works without any changes to your query Oct 23, 2022 at 17:04
  • @AleksandarMilivojsa Great! No problem!
    – J.D.
    Oct 23, 2022 at 17:04
1
WITH
cte AS (
    SELECT order_id, 
           GROUP_CONCAT(product_id, quantity ORDER BY product_id, quantity) order_content
    FROM order_items
    GROUP BY 1
)
SELECT t1.order_id, t2.order_id
FROM cte t1 
JOIN cte t2 USING (order_content)
WHERE t1.order_id < t2.order_id;

https://dbfiddle.uk/-mMek3QT

4
  • Can you please explain your approach and what your query does? @Akina Oct 19, 2022 at 14:34
  • @AleksandarMilivojsa In CTE the whole order content is combined into one value with definite ordering. In outer query I simply compare this combined value for 2 different orders, and return only those pairs for which the values are completely the same. PS. I rename the column for the algo to be more clear.
    – Akina
    Oct 19, 2022 at 14:40
  • I tried your solution but it does not return anything. It looks like you are only retrieving ids for 2 orders if I understand it? I need ids from all orders which have the same order items. Oct 19, 2022 at 16:03
  • @AleksandarMilivojsa You do something wrongly. Answer updated, fiddle added. I need ids from all orders which have the same order items. Add desired output for shown data into your question.
    – Akina
    Oct 19, 2022 at 16:11

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.