如何在Rails中有条件地应用where子句?

Question

我有代码:

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

当params为blank时，如何忽略where中的子句。如果使用if else或case when，代码将非常长

示例:如果applied_params[:email]不存在，则不会提及email = ?。

Answer 1

您可以根据提供的输入条件链接where子句:

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?

等等

多次链接where不会立即触发sql查询，而是构建单个查询。

另一种方法是初始化一个映射，如:

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 } 

，然后传递给where方法:

query.where(mapping)