Rails ActiveRecord：筛选符合多条件的可投标职位

Alright, let's tackle this problem. I'll start by assuming standard model associations for a freelance platform like TaskRabbit/Upwork—if your schema differs a bit, you can tweak these to fit your setup.

First, Let's Define the Core Models & Associations #

These are the typical relationships you'd need for this functionality:

# app/models/job.rb
class Job < ApplicationRecord
  has_many :bids, dependent: :destroy
  has_many :job_skills, dependent: :destroy
  has_many :skills, through: :job_skills
  # Assumes your job has a :start_date (datetime/date field)
end

# app/models/worker.rb
class Worker < ApplicationRecord
  has_many :bids, dependent: :destroy
  has_many :worker_skills, dependent: :destroy
  has_many :skills, through: :worker_skills
end

# app/models/bid.rb
class Bid < ApplicationRecord
  belongs_to :job
  belongs_to :worker
  # Assumes a boolean :accepted field to track client-approved bids
end

# app/models/skill.rb
class Skill < ApplicationRecord
  has_many :job_skills
  has_many :jobs, through: :job_skills
  has_many :worker_skills
  has_many :workers, through: :worker_skills
end

# Join models (required for many-to-many skill relationships)
# app/models/job_skill.rb
class JobSkill < ApplicationRecord
  belongs_to :job
  belongs_to :skill
end

# app/models/worker_skill.rb
class WorkerSkill < ApplicationRecord
  belongs_to :worker
  belongs_to :skill
end

Building the Query Step-by-Step #

We'll chain ActiveRecord scopes to enforce all four conditions efficiently:

1. Filter Jobs with Future Start Dates

Straightforward—we only want jobs that haven't started yet. Use Time.current for datetime fields, or Date.today if your start_date is a date-only field:

future_jobs = Job.where("start_date > ?", Time.current)

2. Exclude Jobs the Worker Has Already Bidded On

We need to rule out any jobs where the worker has submitted a bid. Using an exists subquery is more performant than where.not(id: ...) because the database only checks for existence, not returns all matching IDs:

unbidded_jobs = future_jobs.where.not(
  Bid.where("jobs.id = bids.job_id AND bids.worker_id = ?", current_worker.id).exists
)

3. Exclude Jobs Where a Bid Has Already Been Accepted

We want to ignore jobs where the client has already picked a worker. Again, using exists keeps this efficient:

open_jobs = unbidded_jobs.where.not(
  Bid.where("jobs.id = bids.job_id AND bids.accepted = ?", true).exists
)
# Alternative: If your Job model uses an `accepted_bid_id` foreign key instead:
# open_jobs = unbidded_jobs.where(accepted_bid_id: nil)

4. Filter Jobs Matching the Worker's Skills

We need to ensure the worker has at least one skill required by the job. An exists subquery works best here for large datasets:

qualified_jobs = open_jobs.where(
  JobSkill.joins(:skill)
          .where("job_skills.job_id = jobs.id AND skills.id IN (?)", current_worker.skill_ids)
          .exists
)
# If you want to include jobs with no required skills, adjust this to:
# qualified_jobs = open_jobs.where(
#   "(SELECT COUNT(*) FROM job_skills WHERE job_skills.job_id = jobs.id) = 0 OR " \
#   "(SELECT EXISTS(SELECT 1 FROM job_skills JOIN skills ON job_skills.skill_id = skills.id WHERE job_skills.job_id = jobs.id AND skills.id IN (?)))", current_worker.skill_ids
# )

Full Combined Query (With Scopes for Cleanliness) #

Wrap these conditions into scopes in the Job model to keep your code reusable:

# app/models/job.rb
scope :future, -> { where("start_date > ?", Time.current) }
scope :not_bidded_by, ->(worker) { where.not(Bid.where("jobs.id = bids.job_id AND bids.worker_id = ?", worker.id).exists) }
scope :no_accepted_bid, -> { where.not(Bid.where("jobs.id = bids.job_id AND bids.accepted = ?", true).exists) }
scope :matches_worker_skills, ->(worker) { where(JobSkill.joins(:skill).where("job_skills.job_id = jobs.id AND skills.id IN (?)", worker.skill_ids).exists) }

Now you can call this cleanly in your controller:

# In your workers/jobs_controller.rb
def index
  @available_jobs = Job.future
                       .not_bidded_by(current_worker)
                       .no_accepted_bid
                       .matches_worker_skills(current_worker)
end

Performance Tips #

Add these indexes to speed up the queries (create a migration for this):

add_index :jobs, :start_date
add_index :bids, [:job_id, :worker_id]
add_index :bids, [:job_id, :accepted]
add_index :job_skills, [:job_id, :skill_id]
add_index :worker_skills, [:worker_id, :skill_id]

内容的提问来源于stack exchange，提问作者Mike N.