ruby on rails - Active Record .includes with where clause

Question

I'm attempting to avoid an N+1 query by using includes, but I need to filter out some of the child records. Here's what I have so far:

Column.includes(:tickets).where(board_id: 1, tickets: {sprint_id: 10})

The problem is that only Columns containing Tickets with sprint_id of 10 are returned. I want to return all Columns with board_id of 1, and pre-fetch tickets only with a sprint_id of 10, so that column.tickets is either an empty list of a list of Ticket objects with sprint_id 10.

question from:https://stackoverflow.com/questions/65862415/active-record-includes-with-where-clause

Answer 1

Column.includes(:tickets).where(board_id: 1, tickets: {sprint_id: 10}) makes two SQL queries. One to select the columns that match the specified where clause, and another to select and load the tickets that their column_id is equal to the id of the matched columns.

To get all the related columns without loading unwanted tickets, you can do this:

columns = Column.where(board_id: 1).to_a
tickets = Ticket.where(column_id: columns.map(&:id), sprint_id: 10).to_a

This way you won't be able to call #tickets on each column (as it will again make a database query and you'll have the N+1 problem) but to have a similar way of accessing a column's tickets without making any queries you can do something like this:

grouped_tickets = tickets.group_by(&:column_id)
columns.each do |column|
  column_tickets = grouped_tickets[column.id]
  # Do something with column_tickets if they're present.
end