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,
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.