How to make raw SQL query chainable using Scope? -Rails

Question

I have written a SQL and i want this to be chainable or i can use this query as a scope in Rails? any thoughts?

select '{Critical: '||"Critical"||', Major: '||"Major"||', Moderate: 
'||"Moderate"||', Minor: '||"Minor"||', Clear: '||"Clear"||'}' as result
from (
    select
      sum(case when max_weight=1   THEN 1 else 0 End ) as "Clear",
      sum(case when max_weight=2   THEN 1 else 0 End ) as "Minor",
      sum(case when max_weight=3   THEN 1 else 0 End ) as "Moderate",
      sum(case when max_weight=22  THEN 1 else 0 End ) as "Major",
      sum(case when max_weight=160 THEN 1 else 0 End ) as "Critical",
      count(*) as number_of_answers
    from (
      select 
        fua.forms_user_id, 
        max(fa.weight) max_weight
      from 
        public.forms_user_answers fua
        join public.forms_users fu on (fua.forms_user_id  = fu.id)
        left join public.form_answers fa on (fua.form_answer_id = fa.id)
      where 
        fu.form_id = #{form.id}
      group by 
        fua.forms_user_id
    ) subq
) subq1

Answer 1

It's unlikely you'll be able to make that kind of query chainable with ActiveRecord. But you can certainly make a scope for the query in Rails:

scope :my_scope, -> {
  self.connection.execute("
    select '{Critical: '||"Critical"||', Major: '||"Major"||', Moderate: 
    '||"Moderate"||', Minor: '||"Minor"||', Clear: '||"Clear"||'}' as result
    from (
        select
          sum(case when max_weight=1   THEN 1 else 0 End ) as "Clear",
          sum(case when max_weight=2   THEN 1 else 0 End ) as "Minor",
          sum(case when max_weight=3   THEN 1 else 0 End ) as "Moderate",
          sum(case when max_weight=22  THEN 1 else 0 End ) as "Major",
          sum(case when max_weight=160 THEN 1 else 0 End ) as "Critical",
          count(*) as number_of_answers
        from (
          select 
            fua.forms_user_id, 
            max(fa.weight) max_weight
          from 
            public.forms_user_answers fua
            join public.forms_users fu on (fua.forms_user_id  = fu.id)
            left join public.form_answers fa on (fua.form_answer_id = fa.id)
          where 
            fu.form_id = #{form.id}
          group by 
            fua.forms_user_id
        ) subq
    ) subq1
  ")
}