时间戳

首页 > 解决方案 > 将复杂的 postgresql 查询/子查询转换为 Rails activerecord 语法或将数组转换为活动记录关系？

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

问题描述

所以我花了相当多的时间来编写这个查询，然后发现这是返回一个数组而不是一个 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
")

标签： postgresqlruby-on-rails-4activerecordransackarel

解决方案

并没有真正完成整个工作，但这是一个开始......

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 表

推荐阅读