How do I refactor a query where I sort products by its relationship from another table

I have this query that seems to work in pgAdmin. But when I try to translate it to are using scuttle I get a massive query fragment containing whole case statement. How do I refactor this query so that I can use it in Arel?

SELECT  products.*, case when
(select custom_fields.value_text
        from custom_fields
        where custom_fields.custom_field_definition_id = 4
        and custom_fields.table_record_id = products.id and custom_fields.belongs_to_table = 'product') is null 
then 'stopped'
when
(select custom_fields.value_text
        from custom_fields
        where custom_fields.custom_field_definition_id = 4
        and custom_fields.table_record_id = products.id and custom_fields.belongs_to_table = 'product') is not null 
then (select custom_fields.value_text from custom_fields where custom_fields.custom_field_definition_id = 4
             and custom_fields.table_record_id = products.id and custom_fields.belongs_to_table = 'product')
end as sorted
from products
order by sorted

Additional information: I have created a sqlite database that shows expected behaviour and could be used for further experiments.

https://github.com/bigos/rails-sorting/blob/master/README.org

Initial conclusion: I found a way to get expected results, but can no longer use it with Active Record methods. Luckily, I found how to paginate an array of hashes.

Best solution: I can return proper Active record relations if I put my code as a subquery in order by.

SELECT products.*
FROM products
ORDER BY (case when
(select custom_fields.value_text
from custom_fields
where custom_fields.custom_field_definition_id = 4
and custom_fields.table_record_id = products.id and custom_fields.belongs_to_table = 'product') is null 
then 'stopped'
when
(select custom_fields.value_text
from custom_fields
where custom_fields.custom_field_definition_id = 4
and custom_fields.table_record_id = products.id and custom_fields.belongs_to_table = 'product') is not null 
then (select custom_fields.value_text from custom_fields where custom_fields.custom_field_definition_id = 4
and custom_fields.table_record_id = products.id and custom_fields.belongs_to_table = 'product')
end )

Solution

I don't know exactly what problem you're facing getting this to work from your other tools and/or Active Record but the queries below ought to be equivalent and probably faster:

select p.*, coalesce(cf.value_text, stopped) as sorted
from
    products p
    left outer join custom_fields cf
        on      cf.table_record_id = p.id
            and cf.belongs_to_table = 'product'
            and cf.custom_field_definition_id = 4
order by sorted

This type of query is one of the reason that these kinds of "custom fields" tables are best avoided if possible.

Another alternative which is very close to your original approach is to use a scalar subquery inside a coalesce. You might even prefer this way since it will cause an error if there's some way for the subquery to return more than one value.

select
    p.*,
    coalesce(
        (
        select custom_fields.value_text
        from custom_fields cf
        where 
                f.table_record_id = p.id
            and cf.custom_fields.belongs_to_table = 'product'
            and cf.custom_field_definition_id = 4
        ),
        'stopped'
    ) as sorted
from products p
order by sorted