mysqlarraysfind-in-set

Order by Field or Find_in_set without IN()


I'm building a MySQL query for a search function, now I want to order the outcome in a certain way using an array of ID's.

I've been able to do this by using a piece of MySQL similar to this:

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1) 
ORDER BY FIELD(id, 77, 3, 123, 1)

But I'm wondering if it's possible to Order by FIELD or by FIND_IN_SET without setting the IN() within the Select query.

I don't want to use the IN() statement because I also expect results that aren't in the array of ID's

Hopefully someone can push me in the right direction,


Solution

  • If you prefer just one evaluation, you can reverse your order in field() and use

    SELECT id, name
    FROM mytable 
    ORDER BY FIELD(id, 1, 123, 3, 77) desc
    

    It will still take considerable more time than your original query, because field() is more expensive than IN and you will now have to evaluate field() for every row instead of just some rows.

    An alternative might be to use

    SELECT id, name, FIELD(id, 77, 3, 123, 1) as orderno
    FROM mytable 
    WHERE id IN (77, 3, 123, 1) 
    union 
    SELECT id, name, 99999999
    FROM mytable 
    WHERE id NOT IN (77, 3, 123, 1)
    order by orderno, id
    

    This can be faster depending on the size of your array, but you would have to test it, it might as well be worse.