Rails check multiple day range for start time and end time

Question

This is my code for checking if the user has a time clashing job with his current job application. However, before the jobs were only 1 day now they are multiple days with the same start time and end time denoted with job.days.

    job = Job.find(params[:job_application][:job_id])
    id = job.id
    start_time = job.start_time
    end_time   = job.end_time
    overlaps   = @user.job_applications.joins(:job)
                   .where.not(job_id: id).where(approved: true, status: "SUCCESS")
                     .where("(jobs.start_time >= ? AND jobs.start_time <= ?) OR (jobs.end_time <= ? AND jobs.end_time >= ?)", start_time, end_time, end_time, start_time)
    unless overlaps.empty?
      render json: { error: "timeClash", status: 400}
    end

I am trying to modify my code to do this but I can't seem to get it to work. I tried a couple of ways but none of them worked. What is the easiest way to do this without using a ton of SQL?

Answer 1

I would incorporate a validation to see if a time slot is available for a job

class Job

  def validate(record)
    conflicting_jobs = Job.where("jobs.start_time < ? and jobs.end_time ? >", self.end_time, self.start_time).where(approved: true, status: 'SUCCESS')
    errors << conflicting_jobs.map {|j| "Job conflicts with #{j.name}"}
  end
end