将复杂的postgresql查询/子查询转换为Rails活动记录语法，还是将数组转换为活动记录关系

Question

所以我花了相当多的时间来编写这个查询，然后发现了一个困难的方法，那就是返回一个数组，而不是一个活动记录关系。DOH。这不是问题，但我需要在这些结果上使用Ransack，这需要关系式。

因此，基本上，我需要使用语法.joins((和.select((将其转换为Rails，但我尝试过的所有操作都会出错。我想我可能需要深入AREL？

或者，如果这可以很容易地转换为活动记录关系，而性能问题最小，并保留我的别名列，那么这也可以！

任何帮助或建议都将不胜感激！

find_by_sql("
SELECT
subq.*, 
renewal_date,
days_until_due,
renewal_stage_sort,
(
CASE
WHEN renewal_stage_sort IS NOT NULL THEN
CASE
WHEN days_until_due > 42 AND renewal_stage_sort >= 1 THEN TRUE
WHEN days_until_due > 28 AND days_until_due < 43 AND renewal_stage_sort >= 2 THEN TRUE
WHEN days_until_due > 13 AND days_until_due < 29 AND renewal_stage_sort >= 3 THEN TRUE
WHEN days_until_due > -1 AND days_until_due < 14 AND renewal_stage_sort >= 4 THEN TRUE
WHEN days_until_due < 0 AND renewal_stage_sort >= 5 THEN TRUE
ELSE FALSE
END
ELSE FALSE
END
)
AS on_target
FROM (   
SELECT DISTINCT ON (renewals.id) renewals.*,
CASE
WHEN renewal_types.name = 'IEP-504' THEN patients.iep_renewal_date
WHEN renewal_types.name = 'RR' THEN patients.rr_date
ELSE NULL
END 
AS renewal_date,
(  CASE
WHEN renewal_types.name = 'IEP-504' THEN patients.iep_renewal_date::date
WHEN renewal_types.name = 'RR' THEN patients.rr_date::date
ELSE NULL
END
- current_date)
AS days_until_due, 
renewal_stages.sort_order AS renewal_stage_sort
FROM renewals
INNER JOIN renewal_types ON renewal_types.id = renewals.renewal_type_id
LEFT JOIN renewal_stages ON renewal_stages.id = renewals.renewal_stage_id
INNER JOIN patients ON patients.id = renewals.patient_id AND patients.deleted_at IS NULL
WHERE renewals.deleted_at IS NULL
) subq
")    

Answer 1

并不能真正完成全部工作，但这只是一个开始。。。

http://www.scuttle.io/

提供：

RenewalStages.sortOrder.select(
[
Subq.arel_table[Arel.star], :renewal_date, :days_until_due, :renewal_stage_sort, Arel::Nodes::Group.new(
Arel.sql(
'CASE        WHEN renewal_stage_sort IS NOT NULL THEN          CASE            WHEN days_until_due > 42 AND renewal_stage_sort >= 1 THEN TRUE            WHEN days_until_due > 28 AND days_until_due < 43 AND renewal_stage_sort >= 2 THEN TRUE            WHEN days_until_due > 13 AND days_until_due < 29 AND renewal_stage_sort >= 3 THEN TRUE            WHEN days_until_due > -1 AND days_until_due < 14 AND renewal_stage_sort >= 4 THEN TRUE            WHEN days_until_due < 0 AND renewal_stage_sort >= 5 THEN TRUE            ELSE FALSE          END        ELSE FALSE      END'
)
).as('on_target')
]
).where(Renewal.arel_table[:deleted_at].eq(nil))

是的，战神表显然需要

Answer 2

一个通用的解决方案是使用.from(subquery_sql(来完成最少量的工作。然后，您可以从子查询中提取连接等，并逐个提取到.joins中。

User.
from("(select distinct * from users where id > 0) users ").
select("users.*, false as mystatus").
first.
mystatus 

既然你想要一个ActiveRecord:：Relation，你就需要使用正确的方法https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html如果你直接使用Arel来构建查询，它不会解决你的问题，因为它不知道模型，只知道表、选择、分组依据、查询参数等，这是低级的。

你没有发布你的模式，所以我还没有验证下面的代码是否有效，但类似于

fromsql = <<-SQL
(   
SELECT DISTINCT ON (renewals.id) renewals.*,
CASE
WHEN renewal_types.name = 'IEP-504' THEN patients.iep_renewal_date
WHEN renewal_types.name = 'RR' THEN patients.rr_date
ELSE NULL
END 
AS renewal_date,
(  CASE
WHEN renewal_types.name = 'IEP-504' THEN patients.iep_renewal_date::date
WHEN renewal_types.name = 'RR' THEN patients.rr_date::date
ELSE NULL
END
- current_date)
AS days_until_due, 
renewal_stages.sort_order AS renewal_stage_sort
FROM renewals
INNER JOIN renewal_types ON renewal_types.id = renewals.renewal_type_id
LEFT JOIN renewal_stages ON renewal_stages.id = renewals.renewal_stage_id
INNER JOIN patients ON patients.id = renewals.patient_id AND patients.deleted_at IS NULL
WHERE renewals.deleted_at IS NULL
) renewals
SQL
select_sql = <<-SQL
renewals.*, 
renewal_date,
days_until_due,
renewal_stage_sort,
(
CASE
WHEN renewal_stage_sort IS NOT NULL THEN
CASE
WHEN days_until_due > 42 AND renewal_stage_sort >= 1 THEN TRUE
WHEN days_until_due > 28 AND days_until_due < 43 AND renewal_stage_sort >= 2 THEN TRUE
WHEN days_until_due > 13 AND days_until_due < 29 AND renewal_stage_sort >= 3 THEN TRUE
WHEN days_until_due > -1 AND days_until_due < 14 AND renewal_stage_sort >= 4 THEN TRUE
WHEN days_until_due < 0 AND renewal_stage_sort >= 5 THEN TRUE
ELSE FALSE
END
ELSE FALSE
END
)
AS on_target
SQL
Referal.from(fromsql).select(select_sql).to_sql