Is there an SQL method to streamline by matching multiple criteria?

Question

I have several tables that join in JOINed 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 image identifiers that match $ designID in the following order:

• Lines that first match $ garmentID, $ colorID and $ sizeID.
• Strings that match $ garmentID and $ colorID next
• Strings that match only $ garmentID next
• Strings that don't match any ($ designID only)

I could do this quite easily by simply loading all the lines that match $ designID and then sorting them in PHP, but I understand that as soon as possible it is easier to sort in MySQL. There will be about 20 lines matching the given $ designID.

So my question is twofold: is it worth sorting in SQL statements? If so, what is the best approach?

I would also be very interested to know if there is a name for this sort of sorting.

Answer 1

If I understood correctly, it looks like you can use expressions in your ORDER BY , similar to the accepted answer to the following post:

• Using MySql, can I sort the column, but the last one came?

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 not used as filters in the WHERE . Values ​​are used only in 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 that the first line matches the specified garmentID , colorID and sizeID . Otherwise, the lines corresponding to garmentID and colorID are as follows. Then the lines corresponding only to garmentID . Then the rest that match only the designID filter of the WHERE .

I find it worth doing in SQL. As @Toby noted in another answer , in general, you don’t have to worry about performance when sorting such a small number of lines, assuming you will always filter designID ... As for your other question, I don’t know if there is a name for this query - I usually call this "ordering by expression."

Answer 2

This is an interesting problem, and reading Daniel's answer taught me something (complicated) that I did not know about SQL.

but

If you only ever have 20 such projects, the reality is that sorting will be just as fast in php or MySQL. Often, if you are not dealing with 1000 or millions of lines, speed is not an issue.