CODEWITHSUNDEEP
ruby-on-railsactiverecord
Lou KnowsAlready
Lou KnowsAlready

Reputation: 123

Constructing Query with Rails - GROUP, MAXIMUM, UNION

I have two models:

The tables goes something like this: https://i.sstatic.net/ilIxL.png

Now my goal is to retrieve those students which latest enrollment end date is lesser than the current date, or if the student has no enrollment yet. So given the sample table on the link above, and current date is 11/20/2019, the query should return:

How do I create that query in Ruby on Rails? (Sorry, I'm still noob in rails).

I visualize the sql query is this:

select students.*
from students s
left join
( select student_id, end_date 
  from enrollments e1
  where end_date = (select max(e2.end_date) 
                    from enrollments e2 
                    group by e2.student_id 
                    having e2.student_id = e1.student_id
                    )
) e on e.student_id = s.id
where e.end_date < Date.today OR e.student_id IS NULL

but I can't seem to build it using Rails ActiveRecord's methods.

EDIT

I tried Roc khalil's/Catmal's solution, but the "or" method is giving an incompatible error:

(Relation passed to #or must be structurally compatible. Incompatible values: [:includes])

Also, I revised the code a little bit to fit my needs:

This will retrieve all students that have no records in enrollments table
Student.where.not(id: Enrollment.pluck(:student_id))


And this code will retrieve all students whose last enrollment's end date is lesser than today
Student.joins(:enrollments).where('end_date IN (?)',Enrollment.all.group(:student_id).maximum(:end_date).values).where('end_date < ?', Date.today)

When I executed them separately, I get no error. However if I combine them with the "or" method I get the same incompatible error:

ArgumentError (Relation passed to #or must be structurally compatible. Incompatible values: [:joins])

I've searched it and it looks like it's a bug. :(
So close though :(

UPDATE

I have implemented both @nathanvda and @Catmal suggestion, and came up with this:

Student model:

scope :no_enrollments, -> { where.not(id: Enrollment.pluck(:student_id)) }
scope :with_expired_enrollments, -> { joins(:enrollments).merge(Enrollment.expired_enrollments) }
scope :unenrolled, -> { find_by_sql("#{no_enrollments.to_sql} UNION #{with_expired_enrollments.to_sql}") }

Enrollment model:

scope :expired_enrollments, -> { where('end_date IN (?)',Enrollment.all.group(:student_id).maximum(:end_date).values).where('end_date < ?', Date.today) }


So in my controller, to get the unenrolled students, I just have to call:

Student.unenrolled

Maybe not the optimum solution, but it works on my app. Thanks @nathanvda and @Catmal.

Upvotes: 1

Views: 171

Answers (1)

catmal
catmal

Reputation: 1758

Try this:

@selected_students = Student.where.not(
  id: Enrollment.pluck(:student_id)
).or(
  Student.includes(:enrollments).where(
    enrollments: {
      date_end < Date.today
    }
  )
)

Upvotes: 1

Related Questions