I have several tables that get JOINed together to form a table with columns
designID
garmentID
colorID
sizeID
imageID
I have a function that looks like this [variables in square brackets are optional]:
getProductImages($designID, [$garmentID], [$colorID], [$sizeID]);
I want it to return all imageIDs that match $designID in the following order:
I could do this pretty easily by just loading all the rows that match $designID and then sorting them in PHP, but my understanding is that it's generally faster to do sorting in MySQL when possible. There will be about 20 rows matching a given $designID.
So my question is twofold: Is it worth doing the sorting in a SQL statement? If I do, what is the best approach to take?
I would also be very interested to know if there is a name for this kind of sorting.
If I understood correctly, it looks like you can use expressions in your ORDER BY
, in a way similar to the accepted answer given to the following Stack Overflow post:
Therefore, your query might look like this:
SELECT imageID
FROM ...
JOIN ...
WHERE designID = 100
ORDER BY garmentID = 1 DESC,
colorID = 5 DESC,
sizeID = 10 DESC;
Note that garmentID
, colorID
, and sizeID
are not used as filters in the WHERE
clause. The values are only used in the ORDER BY
expressions.
Test case:
CREATE TABLE designs (designID int, garmentID int, colorID int, sizeID int);
INSERT INTO designs VALUES (100, 1, 1, 1);
INSERT INTO designs VALUES (100, 1, 2, 2);
INSERT INTO designs VALUES (100, 1, 5, 3);
INSERT INTO designs VALUES (100, 1, 5, 10);
INSERT INTO designs VALUES (100, 1, 5, 15);
INSERT INTO designs VALUES (100, 1, 8, 20);
INSERT INTO designs VALUES (100, 2, 5, 10);
INSERT INTO designs VALUES (100, 2, 6, 15);
INSERT INTO designs VALUES (101, 1, 1, 1);
INSERT INTO designs VALUES (101, 2, 1, 1);
Result:
SELECT *
FROM designs
WHERE designID = 100
ORDER BY garmentID = 1 DESC,
colorID = 5 DESC,
sizeID = 10 DESC;
+----------+-----------+---------+--------+
| designID | garmentID | colorID | sizeID |
+----------+-----------+---------+--------+
| 100 | 1 | 5 | 10 |
| 100 | 1 | 5 | 3 |
| 100 | 1 | 5 | 15 |
| 100 | 1 | 1 | 1 |
| 100 | 1 | 2 | 2 |
| 100 | 1 | 8 | 20 |
| 100 | 2 | 5 | 10 |
| 100 | 2 | 6 | 15 |
+----------+-----------+---------+--------+
8 rows in set (0.02 sec)
Note how the row that matches the specified garmentID
, colorID
and sizeID
is first. Failing that, the rows that match garmentID
and colorID
are next. Then the rows that only match garmentID
follow. Then the rest, which only match the designID
filter of the WHERE
clause.
I believe it is worth doing this in SQL. As @Toby noted in the other answer, in general you don't need to worry about performance when sorting such a small number of rows, assuming you will always be filtering by designID
... As for your other question, I don't know if there is a name for such a query - I tend to call it "ordering by an expression".
This is an interesting problem, and reading Daniel's answer has taught me something (complicated) I didn't know about SQL.
However,
If you are only ever likely to have 20 such designs, the reality is that the sorting is going to be just as fast in php or MySQL. Often, unless you're dealing with 1000 or millions of rows, speed isn't an issue.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With