Converting a complex postgresql query/subquery to Rails activerecord syntax OR convert array to active record relation?

Question

So I put quite a bit of time to write this query, and then found out the hard way that this is returning an array rather than an activerecord relation. DOH. This wouldn't be a problem, but i need to use Ransack on these results, which requires the relation.

So, basically, i need to convert this to Rails using the syntax .joins() and .select() but everything I have tried errors out. I am guessing I may need to dive into AREL?

OR, if this can be easily converted to an activerecord relation with minimal performance issues AND keeping my alias columns then that works too!

Any help or advice is appreciated on this one!

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

Misology

Answer 1

Doesn't really do the whole job but it's a start...

http://www.scuttle.io/

Gives:

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

And yup, Arel tables are apparently needed

Hyperform

Answer 2

A general solution is to use .from(subquery_sql) for you to do the minimal amount of work. Then you can extract out joins etc from the subquery and into .joins piece by piece.

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

Since you want an ActiveRecord::Relation you need to use the correct methods https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html If you use Arel directly to build a query it will not solve your problem because it doesn't know about models, it's just knows about tables, select, group by, query parameter, etc, it's low level.

You didn't post your schema so I haven't verified that the code below works, but something like this

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

Cachepot