How to conditionally apply where clause in Rails?

Question

I have the code:

ApplyJob.includes(:job, cv_attachment: :blob)
        .joins('INNER JOIN cities_jobs ON cities_jobs.job_id = apply_jobs.job_id')
        .joins('INNER JOIN industries_jobs ON industries_jobs.job_id = apply_jobs.job_id')
               .where('email = ? AND cities_jobs.city_id = ?
                       AND industries_jobs.industry_id = ?
                       AND apply_jobs.created_at BETWEEN ? AND ?',
                       applied_params[:email],
                       applied_params[:city],
                       applied_params[:industry],
                       d_start,
                       d_end)

def applied_params
  params.permit(:email, :city, :industry, :date_start, :date_end)
end

how to ignore clause inside where when corresponding params are blank. If using if else or case when the code will be very long

Example: if applied_params[:email] is not present, email = ? will not be mentioned.

Answer 1

You can conditionally chain where clauses based on provided input:

query = ApplyJob.includes(:job, cv_attachment: :blob)
                .joins('INNER JOIN cities_jobs ON cities_jobs.job_id = apply_jobs.job_id')
                .joins('INNER JOIN industries_jobs ON industries_jobs.job_id = apply_jobs.job_id')

query = query.where(email: applied_params[:email]) if applied_params[:email].present?
query = query.where(cities_jobs: { city_id: applied_params[:city] }) if applied_params[:city].present?

and so on.

Chaining where multiple times is not going to fire sql queries immediately, instead constructing a single query.

Another approach would be to initiate a mapping like:

mapping = { 
  ?: { email: applied_params[:email] }, 
  cities_jobs: { city_id: applied_params[:city] },
  industries_jobs: { industry_id: applied_params[:industry] },
  apply_jobs: { created_at: (d_start..d_end if d_start && d_end) }
}.filter { |_, value| value.compact.presence } 

and then pass it to where method:

query.where(mapping)