Order records through has_many relationship

Question

Class Event  
has_many event_sessions

Class EventSessions  
belongs_to event

Event Sessions table:  
t.integer "event_id"
t.datetime "date_start"
t.datetime "date_end"

My goal is to:

Filter events by a date range (e.g. April 5 - May 10, 2019)
Order events by start date of earliest event session (ordering for soonest and latest). There is no limit to the number of sessions.

I'm running into the following issues:

Duplicate events showing up after filtering and ordering (the number of duplicate events is equal to the number of event sessions associated with an event, so 3 event sessions result in 3 of the same event showing up).
Unable to use .distinct to remove duplicates

I'm using postgresql if that makes any difference.

This is my current code:

events = joins(:event_sessions)
         .where(event_sessions: { date_start: date_start..date_end } )
         .order('event_sessions.date_start ASC')

I run into the following error when I try to use .distinct:
PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Additionally, I'm unable to use .uniq, because it does not work with .paginate (.uniq converts it to an array).

I've found a solution (thanks to Rails - Distinct ON after a join):

join_query = EventSession.select("event_id, min(date_start) as date").group(1)
events = Event.joins("INNER JOIN (#{join_query.to_sql}) as unique_event_sessions ON events.id = unique_event_sessions.event_id")
            .where("date >= ?", date_start).where("date <= ?", date_end)
            .order('date ASC')

I added additional detail, sorry for the initial lack of it. — Joseph Kristofzski, Mar 19 '19 at 18:29

score 0 · Answer 1 · answered Mar 19 '19 at 19:16

0

Add to the select clause even the event_sessions attributes in order to use distinct

events = joins(:event_sessions)
         .where(event_sessions: { date_start: date_start..date_end } )
         .order('event_sessions.date_start ASC')
         .select('events.*, event_sessions.*')
         .distinct

answered Mar 19 '19 at 19:16

Ursus

29,643
3
33
50

Unfortunately after adding the select the order is messed up, and is no longer based on date_start on event_sessions. Additionally there are still duplicate records. – Joseph Kristofzski Mar 19 '19 at 22:19
It's strange, I tried this. Are you sure you don't have anything else in your code? – Ursus Mar 19 '19 at 22:20
I cut down the code to: events = joins(:event_sessions) .order('event_sessions.date_start ASC') .select('events.*, event_sessions.*') .distinct return events -- and it still isn't working correctly. – Joseph Kristofzski Mar 19 '19 at 22:26
select('events.*, event_sessions.date_start') allowed the order to work, but there were still duplicates that .distinct didn't remove – Joseph Kristofzski Mar 19 '19 at 23:17
have you put order in the last? – buncis Mar 20 '19 at 08:27
Yes. I tried order after the select as well as the distinct. This issue first popped up when I tried pushing to my production test site that using postgresql, specifically the error mentioned in the initial post when I used distinct. So I wouldn't run into the same error in production again I switched my dev server to postgresql as well. I'm thinking maybe that's why your results might be different? Additionally, the number of duplicates are equal to number of event sessions (which is expect imo). – Joseph Kristofzski Mar 20 '19 at 15:54
I found another strange issue, when checking the rails console I found the duplicate events all have different event ids, but they're the incorrect ids (event id 25 shows up as 73, 74, and 75). I'm now trying to determine if this is an issue caused by friendly_id. – Joseph Kristofzski Mar 21 '19 at 03:44

score 0 · Accepted Answer · answered Mar 21 '19 at 06:05

I found something that worked:

join_query = EventSession.select("event_id, min(date_start) as date").group(1)
events = Event.joins("INNER JOIN (#{join_query.to_sql}) as unique_event_sessions ON 
events.id = unique_event_sessions.event_id")
        .where("date >= ?", date_start).where("date <= ?", date_end)
        .order('date ASC')

Order records through has_many relationship

2 Answers2