Rails: условный подзапрос MAX(эффективная_дата)

Question

Допустим, в базе есть три таблицы:

курсы (:id,:name)

course_details (:course_id,: эффективная_дата,: статус),

course_codes (:course_detail_id,: код)

курс имеет много course_details

course_detail имеет много Cource_codes

Курс может иметь несколько записей course_details, которые применимы Effective_dated (означает, что в системе будет использоваться только одна запись course_detail).

Постановка проблемы: я хочу отфильтровать курсы по кодам курсов с учетом кода. И курс должен фильтроваться только по курсам_кодов, которые связаны с действующими датированными курсами_детали, и должны пропускать прошлые действующие датированные записи.

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

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

Если я использую этот код, это будет фильтровать курс независимо от фактического_датированного курса_детали:

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

курсы:

когда я передаю code = eng-01, он должен возвращать пустой массив, а не с идентификатором 1.

Кто-нибудь может мне помочь?

Answer 1

Чтобы решить эту проблему, я использовал подзапрос, который возвращает идентификаторы course_details всех курсов в соответствии с 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)

Получив все идентификаторы, я передал эти идентификаторы в поиск.

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

И это сработало, как ожидалось.