ORDER BY WHERE IN (subquery)

Question

I have a query like this:

SELECT products.* FROM products WHERE products.code = 'search word' OR products.id IN(SELECT product_names.product_id FROM product_names WHERE MATCH (product_names.name) AGAINST ('+search* +word*' IN BOOLEAN MODE) ORDER BY product_names.sorting_field) ORDER BY ??? 

I want the order of the main request to be the same as the order with WHERE IN() . I can’t use FIELD or FIND_IN_SET because I don’t know what I get from MATCH AGAINST .

I tried things like ORDER BY FIELD(products.id, product_names.product_id) , etc., but I did not understand how to make it work.

In fact, my query is much longer and more complex, it is somewhat pseudo version. I cannot do MATCH AGAINST outside of a subquery.

Answer 1

 SELECT products.* FROM products LEFT JOIN ( SELECT product_id, sorting_field FROM product_names WHERE MATCH (product_names.name) AGAINST ('+search* +word*' IN BOOLEAN MODE) ) t ON products.id = t.product_id WHERE products.code = 'search word' OR t.product_id IS NOT NULL ORDER BY t.sorting_field