如何重构一个查询，在该查询中，我根据另一个表中的产品关系对其进行排序

Question

我有一个似乎在pgAdmin中工作的查询。但当我尝试使用crush将其翻译为时，我得到了一个包含完整case语句的巨大查询片段。如何重构这个查询，以便在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

其他信息：我创建了一个sqlite数据库，该数据库显示了预期的行为，可用于进一步的实验。

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

初步结论：我找到了一种获得预期结果的方法，但无法再将其用于Active Record方法。幸运的是，我找到了如何对哈希数组进行分页。

最佳解决方案：如果我按照.的顺序将代码作为子查询，我可以返回正确的活动记录关系

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 )

Answer 1

我不知道你在使用其他工具和/或活动记录时到底面临什么问题，但下面的查询应该是等效的，而且可能更快：

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

如果可能的话，这种类型的查询是最好避免使用这些类型的"自定义字段"表的原因之一。

另一种非常接近原始方法的选择是在coalesce中使用标量子查询。您甚至可能更喜欢这种方式，因为如果有某种方式让子查询返回多个值，它会导致错误。

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