I'm trying to order a query based on the comma separated result of another query

SELECT t.field1, t.field2 /*, ...*/
FROM table t
/* ... */
ORDER BY 
    CASE WHEN NOT EXISTS (SELECT 1 FROM table1 t1 WHERE t1.field1sub = t.field1) 
        THEN FIELD(t.field2, (SELECT field_order FROM table1 t1 WHERE t1.field1sub = t.field1))
        ELSE FIELD(t.field2, (SELECT field_order FROM table1 t1 WHERE t1.field1sub = 0))
    END

The important part of the query is this

FIELD(t.field2, (SELECT field_order FROM table1 t1 WHERE t1.field1sub = t.field1))

So basically, the subquery returns a comma separated string 1,2,3,7,9,4,10, and I want to order by those fields first.

I know we can do for example ORDER BY FIELD(t.field,2,1,2,3), but how can we use a subquery that returns a string, to fill the order field?

CodePudding user response：

There is no neeed for dynamic sql.

you can doit with FIND_IN_SET

But still this is slower as when you had a normalized Structure.

and fyi you should read Is storing a delimited list in a database column really that bad?

A simplifieed version of your query to demonstarte that it works

CREATE TABLE t1 (Sortder varchar(100), id int)

INSERT INTO t1 VALUES ('1,2,3,4',0),('4,3,2,1',1)

CREATE TABLe t2 (f1 int, val varchar(10))

INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d')

SELECT * FROM t2
ORDER BY
CASE WHEN  4 = 5
THEN  find_in_set( f1, (SELECT Sortder FROM t1 WHERE id = 0) )
ELSE find_in_set( f1, (SELECT Sortder FROM t1 WHERE id = 1) )
END

f1 | val
-: | :--
 4 | d  
 3 | c  
 2 | b  
 1 | a  

SELECT * FROM t2
ORDER BY
CASE WHEN  5 = 5
THEN  find_in_set( f1, (SELECT Sortder FROM t1 WHERE id = 0) )
ELSE find_in_set( f1, (SELECT Sortder FROM t1 WHERE id = 1) )
END

f1 | val
-: | :--
 1 | a  
 2 | b  
 3 | c  
 4 | d  

db<>fiddle here

CodePudding user response：

The function that you can use to locate a value in a comma separated list of values is FIND_IN_SET().

You don't need the CASE expression and EXISTS:

ORDER BY FIND_IN_SET(
           t.field2,
           COALESCE(
             (SELECT field_order FROM table1 t1 WHERE t1.field1sub = t.field1),
             (SELECT field_order FROM table1 t1 WHERE t1.field1sub = 0)
           )
         ) 

When there is no row in table1 satisfying the condition of the 1st subquery:

SELECT field_order FROM table1 t1 WHERE t1.field1sub = t.field1

then its result is null in which case COALESCE() will return the result of the 2nd subquery:

SELECT field_order FROM table1 t1 WHERE t1.field1sub = 0

Another way to write the ORDER BY clause would be:

ORDER BY FIND_IN_SET(
           t.field2,
           (
             SELECT field_order 
             FROM table1 t1 
             WHERE t1.field1sub IN (t.field1, 0) 
             ORDER BY t1.field1sub = 0 LIMIT 1
           )
         ) 

In this case, the subquery:

SELECT field_order 
FROM table1 t1 
WHERE t1.field1sub IN (t.field1, 0) 
ORDER BY t1.field1sub = 0 LIMIT 1 

returns the row with t1.field1sub = t.field1 if it exists, but if it does not exist it returns the row with t1.field1sub = 0.