ORDER BY field when using table joins

Question

I've got a products table that I'm trying to get to work. The query brings back results; however, it isn't actually using the ORDER BY FIELD to sort the results. It's skipping it somehow. I even tried ORDER BY FIELD(sc.id,'4','5','6'), and that didn't work either.

Is it even possible to use table_name.column in an ORDER BY FIELD()? Is there an alternative or a better method of doing this query?

$product = $db1q->query("
SELECT p.id, p.name, p.image, p.url,p.subcat as subcat, sc.id as scid,sc.name as scname 
FROM Product as p 
JOIN Product_Sub_Category as sc ON p.subcat = sc.id 
WHERE p.visibility='1' AND find_in_set(p.id,'". $sidr['products'] ."') 
ORDER BY FIELD(p.subcat,'4','5','6'), sc.sort_order ASC, p.sort_order ASC") 
or die ('Unable to execute query. '. mysqli_error($db1q));

I just dumbed the query down to the basic level....

$product = $db1q->query("
SELECT id, name, image, url,subcat 
FROM Product WHERE visibility='1' AND id IN ({$sidr['products']}) ORDER BY FIELD(subcat,'5','4','6','22')") or die ('Unable to execute query. '. mysqli_error($db1q));

and for some reason the order of my subcats are as follows....

3,12,23,5,5,4,4,4,4,4,22

Why wouldn't they begin with 5, 4, 6(doesn't exist), and 22? Then display 3,12, and 23 after those are first....

This should work. Can you show some sample table data, the result you expect, and the result you're getting instead? — Barmar, Feb 27 '18 at 18:44
Not related to the problem, but why are you using `FIND_IN_SET` instead of `AND p.id IN ({$sidr['products']})`? — Barmar, Feb 27 '18 at 18:45
will that perform better? For some reason I tried that in a different section of the site using a similar code and it wouldn't work correctly. Can't remember the reasoning at this point though. — KDJ, Feb 27 '18 at 18:48
Yes, it should work better. You might have been trying it where there value was a comma-separated SQL string, not a PHP string. — Barmar, Feb 27 '18 at 18:49
let me ask a question...using the FIELD like I have in my question, will that order by p.subcat = 4, p.subcat = 5, p.subcat = 6. and then everything else will sort by sc.sort_order ASC and p.sort_order ASC after those get sorted? — KDJ, Feb 27 '18 at 19:07
You could do `order by case when p.subcat in (4,5,6) then 0 else 1 end), subcat` this would assign 0 to 4,5,6 and 1 to all others. 0 comes before 1 and 4 before 5, 5, before 6 etc... so 4,5,6 would be first followed by 3,12,22,23 etc.. — xQbert, Feb 27 '18 at 19:42
is this the best way of doing that though? Why wouldn't the ORDER BY FIELD not work? — KDJ, Feb 27 '18 at 19:43
what data type is subcat? Numeric? if so why compare numeric to 'string' data? as to why yours isn't working... 3,12,23 would all be null... when using field(). null will sort before 1,2,3,4 (position)... so perhaps https://stackoverflow.com/questions/2051602/mysql-orderby-a-number-nulls-last to put nulls last or assign position to all other values as well... or use the case to assign 1,0 and then use field? — xQbert, Feb 27 '18 at 19:48
subcat is numeric - int(11). Can you provide an answer with code to answer the question? The demo link you sent didn't have any code in it. How would I put a negative on a FIELD....I tried and it didn't produce any results. — KDJ, Feb 27 '18 at 19:57

xQbert · Accepted Answer · 2018-02-27T20:15:03.907

Simple Rextester Demo

When datatype is numeric don't compare to 'string' values

eg visibility = '1' if visibility is numeric you really shouldn't have the apostrophes around it. same in the field function given subcat.

$product = $db1q->query("SELECT id, name, image, url,subcat 
                         FROM Product 
                         WHERE visibility='1' 
                           AND id IN ({$sidr['products']}) 
                         ORDER BY case when subcat in (5,4,6,22) then 0 else 1 end, 
                                  FIELD(subcat,5,4,6,22)
  ") or die ('Unable to execute query. '. mysqli_error($db1q));

or something like:

order by case when field(sort,'5','4','22') = 0 then (select max(sort)+1+sort from Product) 
                                                else field(sort,'5','4','22') end;

The issue with the 2nd approach is that it has to run a subquery for every record. In addition if the size of subcat/sort exceed or approach the max of int we'll run into a problem adding the values together. This problem is negated by using the 2 column sort approach in the first method.

Again, my gut feeling is that the first approach with 2 sort columns would be faster; and in my opinion easier to follow/maintain. The downfall is if the sort order defined changes then we have to change code. So... why have the order defined here... what isn't the order defined in a table; or is the order passed in as a parameter by user?

Testing on large data-sets would be required I can't honestly say. My gut feeling is the subquery would be slower as it's executed every record each time. whereas the in statement has a small # of values to evaluate... but I can't say for sure. — xQbert, Feb 27 '18 at 20:07
I suspect the `MAX(sort)` will make the second version worse. — Barmar, Feb 27 '18 at 20:10
check out edit...its still not sorting correctly.....I don't get it....I placed the new code (including your rendition) and the order the numbers are being sorted in.... — KDJ, Feb 27 '18 at 20:24

ORDER BY field when using table joins

1 Answers1