Sorting by pivot table results

Question

I had to create a report for a customer by most sold products in the last month and sorted by parameters they have assigned.

We have three parameters on products: Colour, Type and Collection.

The desired result is to sort products by most sold Collection, then most sold Colour, then most sold Type. The query I have written is monstrous, and I am sure it could be written efficiently.

SELECT a.ID, collection.value as title, colour.value as colour, type.value as type
FROM orderrows oa
JOIN order o ON o.ID = oa.orderID
JOIN products a ON oa.productid = a.ID
JOIN productparameter as collection ON collection.productid = a.ID AND collection.parameterid = 'collection_id'
JOIN productparameter as type ON type.productid = a.ID AND type.parameterid = 'type_id'
JOIN productparameter as colour ON colour.productid = a.ID AND colour.parameterid = 'colour_id'
JOIN
(
    SELECT @rowId:=@rowId+1 AS rowId, collection.title
    FROM
    (
        SELECT collection.value as title
        FROM orderrows oa
        JOIN order o ON o.ID = oa.orderID
        JOIN products a ON oa.productid = a.ID
        JOIN productparameter as collection ON collection.productid = a.ID AND collection.parameterid = 'collection_id'
        WHERE o.orderdate > NOW() - INTERVAL 1 MONTH
        GROUP BY collection.value
        ORDER BY SUM(oa.productprice * oa.productamount) DESC
    ) collection,
    (
        SELECT @rowId:=0
    ) t2
) as collection_sort ON collection.value = collection_sort.title
JOIN
(
    SELECT @rowId:=@rowId+1 AS rowId, colour.title
    FROM
    (
        SELECT colour.value as title
        FROM orderrows oa
        JOIN order o ON o.ID = oa.orderID
        JOIN products a ON oa.productid = a.ID
        JOIN productparameter as colour ON colour.productid = a.ID AND colour.parameterid = 'colour_id'
        WHERE o.orderdate > NOW() - INTERVAL 1 MONTH
        GROUP BY colour.value
        ORDER BY SUM(oa.productprice * oa.productamount) DESC
    ) colour,
    (
        SELECT @rowId:=0
    ) t2
) as colour_sort ON colour.value = colour_sort.title
JOIN
(
    SELECT @rowId:=@rowId+1 AS rowId, type.title
    FROM
    (
        SELECT type.value as title
        FROM orderrows oa
        JOIN order o ON o.ID = oa.orderID
        JOIN products a ON oa.productid = a.ID
        JOIN productparameter as type ON type.productid = a.ID AND type.parameterid = 'type_id'
        WHERE o.orderdate > NOW() - INTERVAL 1 MONTH
        GROUP BY type.value
        ORDER BY SUM(oa.productprice * oa.productamount) DESC
    ) type,
    (
        SELECT @rowId:=0
    ) t2
) as type_sort ON type.value = type_sort.title
WHERE o.orderdate > NOW() - INTERVAL 1 MONTH
GROUP BY a.ID
ORDER BY collection_sort.rowId ASC, colour_sort.rowId ASC, type_sort.rowId ASC

Answer 1

1. a as alias for products is not the best choice.
2. You don't use anything from products in your subqueries. So you don't need to join it.
3. If you only need the productID, you can also skip the products table in the outer query and use oa.productid instead.
4. You don't need a rowId - You can use the SUM to order the result.

So you query could look like:

SELECT p.ID, collection.value as title, colour.value as colour, type.value as type
FROM orderrows oa
JOIN order o ON o.ID = oa.orderID
JOIN products p ON oa.productid = p.ID
JOIN productparameter as collection ON collection.productid = p.ID AND collection.parameterid = 'collection_id'
JOIN productparameter as type ON type.productid = p.ID AND type.parameterid = 'type_id'
JOIN productparameter as colour ON colour.productid = p.ID AND colour.parameterid = 'colour_id'
JOIN
(
    SELECT collection.value, SUM(oa.productprice * oa.productamount) as total
    FROM orderrows oa
    JOIN order o ON o.ID = oa.orderID
    JOIN productparameter as collection ON collection.productid = oa.productid AND collection.parameterid = 'collection_id'
    WHERE o.orderdate > NOW() - INTERVAL 1 MONTH
    GROUP BY collection.value
) as collection_sort ON collection.value = collection_sort.value
JOIN
(
    SELECT colour.value, SUM(oa.productprice * oa.productamount) as total
    FROM orderrows oa
    JOIN order o ON o.ID = oa.orderID
    JOIN productparameter as colour ON colour.productid = oa.productid AND colour.parameterid = 'colour_id'
    WHERE o.orderdate > NOW() - INTERVAL 1 MONTH
    GROUP BY colour.value
) as colour_sort ON colour.value = colour_sort.value
JOIN
(
    SELECT type.value, SUM(oa.productprice * oa.productamount) as total
    FROM orderrows oa
    JOIN order o ON o.ID = oa.orderID
    JOIN productparameter as type ON type.productid = oa.productid AND type.parameterid = 'type_id'
    WHERE o.orderdate > NOW() - INTERVAL 1 MONTH
    GROUP BY type.value
) as type_sort ON type.value = type_sort.value
WHERE o.orderdate > NOW() - INTERVAL 1 MONTH
GROUP BY a.ID
ORDER BY collection_sort.total DESC, colour_sort.total DESC, type_sort.total DESC

That are 37 lines of code instead of 64.

But there are still three subqueries which are almost equal. You can merge them to this one:

SELECT pp.parameterid, pp.value, SUM(oa.productprice * oa.productamount) as total
FROM orderrows oa
JOIN `order` o ON o.ID = oa.orderID
JOIN productparameter as pp ON pp.productid = oa.productid
WHERE o.orderdate > NOW() - INTERVAL 1 MONTH
  AND collection.parameterid IN ('collection_id', 'colour_id', 'type_id')
GROUP BY pp.parameterid, pp.value

and use conditional aggregation to pivot the result in the outer query:

SELECT 
    p.ID,
    MAX(CASE WHEN t.parameterid = 'collection_id' THEN t.value END) as collection,
    MAX(CASE WHEN t.parameterid = 'colour_id'     THEN t.value END) as colour,
    MAX(CASE WHEN t.parameterid = 'type_id'       THEN t.value END) as `type`
FROM (
    SELECT pp.parameterid, pp.value, SUM(oa.productprice * oa.productamount) as total
    FROM orderrows oa
    JOIN `order` o ON o.ID = oa.orderID
    JOIN productparameter as pp ON pp.productid = oa.productid
    WHERE o.orderdate > NOW() - INTERVAL 1 MONTH
      AND collection.parameterid IN ('collection_id', 'colour_id', 'type_id')
    GROUP BY pp.parameterid, pp.value
) t
JOIN productparameter pp ON pp.parameterid = t.parameterid
JOIN products p ON p.ID = pp.productid
JOIN orderrows oa ON oa.productid = p.ID
JOIN `order` o ON o.ID = oa.orderID
WHERE o.orderdate > NOW() - INTERVAL 1 MONTH
GROUP BY p.ID
ORDER BY
    MAX(CASE WHEN t.parameterid = 'collection_id' THEN t.total END) DESC,
    MAX(CASE WHEN t.parameterid = 'colour_id'     THEN t.total END) DESC,
    MAX(CASE WHEN t.parameterid = 'type_id'       THEN t.total END) DESC

(24 lines)