将复杂的postgresql查询/子查询转换为Rails activerecord语法或将数组转换为活动记录关系？

Question

所以我花了相当多的时间来编写这个查询，然后发现这是一个返回数组而不是activerecord关系的艰难方法。多伊尔。这不是问题，但我需要对这些结果使用Ransack，这需要关系。

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

或者，如果这可以很容易地转换为activerecord关系，并且性能问题最少，并保留我的别名列，那么也可以！

在这一点上，任何帮助或建议都是非常感谢的！

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))

是的，显然需要Arel表

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