ruby - Rails: Conditional subquery MAX(effective_date)

Question

Let's suppose, there are three tables in the database:

courses (:id, :name)

course_details (:course_id, :effective_date, :status),

course_codes (:course_detail_id, :code)

course has many course_details

course_detail has many cource_codes

Course can have multiple course_details records which are effective_dated applicable (means only one record of course_detail will be used in the system).

Problem statement: I want to filter courses by course codes given code. And course should only be filtered by the course_codes which are linked with effective dated course_detail and should skip the past effective dated records.

course = Course.find(params[:id])

course_detail = CourseDetail.find_by(effective_date: CourseDetail.max(effective_date), course_id: course.id)

If I use this code this will filter course irrespective of effective_dated course_details:

Course.left_joins(course_details: :course_codes).where(course_details: { course_codes: { code: params[:code] } })

courses:

Id	Name
1	English
2	Maths

question from:https://stackoverflow.com/questions/65920727/rails-conditional-subquery-maxeffective-date

Answer 1

To resolve this issue, I used a subquery that returns ids of course_details of all the courses according to effective_date:

    query = "select child.id from courses as parent
  inner join course_details as child on child.course_id = parent.id
  where child.effective_date = 
  (select  max(child1.effective_date) as effective_date
  from    course_details as child1
  where   child1.course_id = parent.id
  and (child1.effective_date <= CURRENT_DATE
    or child1.effective_date = (select  min(child2.effective_date) as effective_date
    from    course_details as child2
    where   child2.course_id = parent.id)
    ))"
effective_dated_ids = Course.find_by_sql(query).pluck(:id)

After getting all the ids, I passed these ids in search.

records = Course.left_joins(course_details: :course_codes).where(course_details: { id: effective_record_ids, course_codes: { course_code: params[:course_code] } })

And it worked as expected.