michael michael - 10 months ago 68
SQL Question

Filter includes on nested relationship

I'm working on a reporting feature for a Rails app where users contact people in offices. An office has many people, and a person has many contacts.

I want to query for all of the offices that have been contacted in a date range, and all of the people in the offices contacted in that date range, but not people in those offices who weren't contacted in that range. In other words, if there are two people in the office, and one was contacted in the range and one wasn't, I only want to include that one contacted in that range.

The query I started writing looks like this:

Office.includes(:contacts, :people).where('contacts.created_at >= ? AND contacts.created_at <= ?', @from_date, @to_date)

But this filters the contacts, not the people. Then I tried this:

Office.includes(:contacts, :people).where('people.contacts.created_at >= ? AND people.contacts.created_at <= ?', @from_date, @to_date)

But that gives me
Unknown column 'voters.call_attempts.created_at'
, which totally makes sense. Now I'm not sure where to go from here.

Any advice is much appreciated! Thanks in advance.


I think you're very close - based on the associations you've described, you should be able to just change the includes statement a bit. Instead of includes(:contacts, :people), try includes(people: :contacts). This will get all contacts through people. You may even be able to get it down to just includes(:contacts) if you've set up a has many through relationship between office and contacts.