Include a left joined has_many association with condition

Question

There is an association query I seem to be unable to do without triggering a N+1 query.

Suppose I host Parties. I have many Friends, and each time a friend comes to a party, they create a Presence.

And so:

Presence.belongs_to :party
Presence.belongs_to :friend

Friend.has_many :presences
Party.has_many :presences

So far so good.

I want to obtain a list of every one of my Friends, knowing whether or not they are present at this Party, without triggering a N+1 query.

My dataset would look like this:

friends: [
  {name: "Dave Vlopment", presences: [{created_at: "8pm", party_id: 2012}]},
  {name: "Brett E. Hardproblem", presences: [nil]},
  {name: "Ann Plosswan-Quarry", presences: [{created_at: "10pm", party_id: 2012}]},
  ...
]

and so on.

I have a lot of friends and do a lot of parties, of course. (This is of course a fictional example.)

I would do:

Friend.all.includes(:presence).map{ |them| them.parties }

# But then, `them.parties` is not filtered to tonight's party. 

Friend.all.includes(:presence).map{ |them| them.parties.where(party_id: pid) }

# And there I have an N+1.

I could always filter at the Ruby layer:

Friend.all.includes(:presence).map{ |them| them.parties.select{ |it| it.party_id = party.id } }

But this works pretty badly with as_json(includes: {}) and so on. I'm discovering this is very error-prone as I'll be making calculations on the results.

And I make a lot of parties, you know? (still fictional)

If I where on the first query, I lose the left join:

Friend.all.includes(:presence).where(party: party)

I have no idea that tonight, Brett and a bunch of friends, who are always there, are absent. (this one is not guaranteed to be a fictional experience)

I will only see friends who are present.

And if I go through party, well of course I will not see who is absent either.

Now I know there are ways I can do this in SQL, and other ways we can wrangle around some Ruby to pull it together.

However, I'm looking for a "first-class" way to do this in Activerecord, without getting N+1s.

Is there a way to do this using only the Activerecord tools? I haven't found anything yet.

Answer 1

I'm not sure whether this meets your expectation about "first-class" way or not.

But you can use this approach to avoids N+1

  # fetch all friends
  friends = Friend.all

  # fetch all presences. grouped by friend_id
  grouped_presences = Presence.all.group_by(&:friend_id)

  # arrange data
  data = []
  friends.each do |friend|
    json = friend.as_json
    json["presences"] = grouped_presences[friend.id].as_json
    data << json
  end

  puts data

It only executes 2 queries

SELECT `friends`.* FROM `friends`
SELECT `presences`.* FROM `presences`