ruby-on-rails - 如何在 Rails 中实现多对多关系搜索

Question

job 和 city 我有多对多关系，这是我的模型

工作.rb

class Job < ApplicationRecord
    has_many :job_cities, dependent: :destroy
    has_many :cities, through: :job_cities
end

城市.rb

class City < ApplicationRecord
    has_many :job_cities, dependent: :destroy
    has_many :jobs, through: :job_cities
end

和job_city.rb

class JobCity < ApplicationRecord
    belongs_to :city
    belongs_to :job
end

我想你已经明白是怎么回事了。现在我需要根据 jobs 和 cities 搜索工作然后我试过这样

哪个工作:

2.3.4 :002 > city = City.find_by_name('Madrid')
  City Load (0.3ms)  SELECT  "cities".* FROM "cities" WHERE "cities"."name" = ? LIMIT ?  [["name", "Madrid"], ["LIMIT", 1]]
 => #<City id: 3, name: "Madrid", created_at: "2017-11-15 13:33:41", updated_at: "2017-11-15 13:33:41", jobs_count: 1, companies_count: 0> 
2.3.4 :003 > jobs = Job.includes(:cities).where('job_cities.city_id' => city.id)
  SQL (0.5ms)  SELECT  DISTINCT "jobs"."id" FROM "jobs" LEFT OUTER JOIN "job_cities" ON "job_cities"."job_id" = "jobs"."id" LEFT OUTER JOIN "cities" ON "cities"."id" = "job_cities"."city_id" WHERE "job_cities"."city_id" = ? LIMIT ?  [["city_id", 3], ["LIMIT", 11]]
  SQL (0.6ms)  SELECT "jobs"."id" AS t0_r0, "jobs"."title" AS t0_r1, "jobs"."details" AS t0_r2, "jobs"."location" AS t0_r3, "jobs"."no_of_vacancies" AS t0_r4, "jobs"."age_range" AS t0_r5, "jobs"."salary_range" AS t0_r6, "jobs"."requirements" AS t0_r7, "jobs"."qualifications" AS t0_r8, "jobs"."additional_qualifications" AS t0_r9, "jobs"."label" AS t0_r10, "jobs"."deadline" AS t0_r11, "jobs"."years_of_experience" AS t0_r12, "jobs"."tags" AS t0_r13, "jobs"."email_for_receive_cv" AS t0_r14, "jobs"."view_count" AS t0_r15, "jobs"."urgency" AS t0_r16, "jobs"."status" AS t0_r17, "jobs"."company_id" AS t0_r18, "jobs"."employer_id" AS t0_r19, "jobs"."created_at" AS t0_r20, "jobs"."updated_at" AS t0_r21, "jobs"."is_hb_apply" AS t0_r22, "jobs"."job_id" AS t0_r23, "jobs"."closed" AS t0_r24, "cities"."id" AS t1_r0, "cities"."name" AS t1_r1, "cities"."created_at" AS t1_r2, "cities"."updated_at" AS t1_r3, "cities"."jobs_count" AS t1_r4, "cities"."companies_count" AS t1_r5 FROM "jobs" LEFT OUTER JOIN "job_cities" ON "job_cities"."job_id" = "jobs"."id" LEFT OUTER JOIN "cities" ON "cities"."id" = "job_cities"."city_id" WHERE "job_cities"."city_id" = ? AND "jobs"."id" = 6  [["city_id", 3]]

 => #<ActiveRecord::Relation [#<Job id: 6, title: "Android Developer", ...........>]> 

哪个不工作

2.3.4 :004 > city_not_matched = City.find_by_name('madrid')
  City Load (0.3ms)  SELECT  "cities".* FROM "cities" WHERE "cities"."name" = ? LIMIT ?  [["name", "madrid"], ["LIMIT", 1]]
 => nil 
2.3.4 :005 > jobs = Job.includes(:cities).where('job_cities.city_id' => city_not_matched.id)
NoMethodError: undefined method `id' for nil:NilClass
        from (irb):5

在控制台上试过了，我在搜索 Controller 中的代码是这样的

@city = City.find_by_name(params[:city])
@jobs = Job.includes(:cities).where('job_cities.city_id' => @city.id)

摘要是当城市名称完全匹配时搜索工作，否则显示

NoMethodError: undefined method `id' for nil:NilClass

我知道找不到城市名称，这就是为什么会出现此错误，这不是正确的搜索方式，但如何搜索。

我的问题是:如何使用多对多关联进行搜索，我想实现like关键字搜索无误。

注意:请不要建议任何 gem。

谢谢

Answer 1

我会做这样的事情:

Job.joins(:cities)
   .where("cities.name ILIKE ?", "%#{params[:city]}%")