Ransack and find_by_sql

Question

is there a way to use ransack with find_by_sql?

I have this:

def index
  @p = Patient.ransack(params[:q])
  @patients = @p.result.page(params[:page])

end

but I'd need:

  @p = Patient.find_by_sql(
    "SELECT DISTINCT first_name, last_name, gender,  MAX(S.surgery_date)
     FROM patients P
     LEFT JOIN
     hospitalizations H
     ON
     P.id = H.patient_id
     LEFT JOIN
     surgeries S
     ON
     S.hospitalization_id = H.id
     GROUP BY first_name, last_name, gender")

Answer 1

I would recommend avoiding find_by_sql and converting your query into a truer ActiveRecord query

In Rails 5+ You could try the following:

class Patient < ApplicationRecord
   scope :basic_info, -> { 
        self.left_joins(hospitalizations: :surgery)
           .distinct
           .select("first_name, 
                    last_name, 
                    gender,  
                    MAX(surgeries.surgery_date) as most_recent_surgery")
           .group("first_name, last_name, gender")
   }
end

This will provide the same SQL as your find_by_sql but will return an ActiveRecord::Relation rather than a ActiveRecord::Result. This should allow ransack to be chained to the response like so:

def index
  @p = Patient.basic_info.ransack(params[:q])
  @patients = @p.result.page(params[:page])

end

If you are using Rails less than 5 then it gets a little messier but the following will still provide the same

class Patient < ApplicationRecord
   scope :basic_info, -> { 
        patient_table = Patient.arel_table
        hospitalizations_table = Hospitaliztion.arel_table
        surgeries_table = Surgery.arel_table
        patient_join = patient_table.join(hospitalizations_table,Arel::Nodes::OuterJoin).on(
            hospitalizations_table[:patient_id].eq(patient_table[:id])
        ).join(surgeries_table, Arel::Nodes::OuterJoin).on(
          surgeries_table[:hospitalization_id].eq(hospitalizations_table[:id])
        )  
        self.joins(patient_join.join_sources)
           .select("first_name, 
                    last_name, 
                    gender,  
                    MAX(surgeries.surgery_date) as most_recent_surgery")
           .group("first_name, last_name, gender")
   }
end