N+1 with subclass query

[treagod]

Hi 👋

I'm working with multi-table inheritance and I’d like to iterate over all events in one pass while avoiding N+1 queries and still being able to branch on the concrete subtype.

Models:

class Event < Marten::Model
   field :id, :big_int, primary_key: true, auto: true
   field :title, :string, max_size: 255
   field :description, :text
   field :start_time, :date_time
   field :end_time, :date_time, null: true
   field :all_day, :bool, default: false
   field :timezone, :string, max_size: 100, default: "UTC"
   field :location_name, :string, max_size: 255
   field :location_address, :string, max_size: 255
   field :online_link, :string, max_size: 255, null: true
end

class ConcertEvent < Event
   field :artist, :string, max_size: 255
   field :genre, :string, max_size: 100
   field :venue_capacity, :int
end

class WorkshopEvent < Event
   field :instructor, :string, max_size: 255
   field :topic, :string, max_size: 255
   field :duration_hours, :int
end

Goal

• Run a single (or few) queries to fetch “all events” and then branch on the concrete type for each row.
• Access subclass fields without incurring per-row lookups.

Minimal code:

events = Event.all

events.each do |event|
   puts "Event: #{event.title}, Start: #{event.start_time}, End: #{event.end_time}"

   if (concert = event.concert_event)
      puts "Type: Concert (artist=#{concert.artist}, capacity=#{concert.venue_capacity})"
   elsif (workshop = event.workshop_event)
      puts "Type: Workshop (instructor=#{workshop.instructor}, hours=#{workshop.duration_hours})"
   else
      puts "Type: Base Event"
   end
end

Observed behavior:

• The initial Event.all runs a single SELECT on main_event.
• Accessing event.concert_event and event.workshop_event triggers additional SELECTs on the subclass tables for each row, producing N+1 behavior even when there are very few child rows in reality.
• In a tiny dataset (1 ConcertEvent, 1 WorkshopEvent) I already see multiple follow-up queries: one for each attempted child lookup on each event. This obviously won’t scale for larger lists.

What I tried to mitigate N+1:

1. Explicit join on one child:

   events = Event.all.join(:concert_event)

   This does bring the concert child columns into the main result and has only 1 query, but I only get one subclass.

2. Manual branching queries + merge in memory:

   concerts = ConcertEvent.all.to_a
   workshops = WorkshopEvent.all.to_a

   This avoids N+1 and gives concrete instances, but loses the convenience of starting from Event.all when I truly want the union of all subtypes. It also becomes cumbersome once there are many subtypes and I still want global filtering/pagination on the unified set.

3. Joining both children from the parent:

   Won't return any result

Questions

1. Is there a built-in, idiomatic way to eager-load child rows for MTI from the parent side to avoid N+1?

   • The desired outcome: accessing event.concert_event / event.workshop_event does not issue extra SQL because the child rows were already fetched and cached during the initial query.

2. Is there (or could there be) an API to “downcast on hydrate” when querying the parent, i.e. return concrete subclass instances automatically based on the presence of a child row?

   • Something like:

     Event.all.with_subclasses.each do |event|
        case event
        when ConcertEvent
        …
        when WorkshopEvent
        …
        end
     end

3. Regarding join(:concert_event):

   • Is there a way to include multiple child joins at once (e.g., join(:concert_event, :workshop_event)) and have the framework recognize and hydrate those so that calling event.concert_event / event.workshop_event does not perform additional queries?

4. Might it be possible to enable abstract models to be queried? Maybe under the condition, that there is at least one subclass?

I hope I've gathered the most relevant details. If not feel free to ask.

Thanks for reading!

[ellmetha]

Hey! 👋

Is there a built-in, idiomatic way to eager-load child rows for MTI from the parent side to avoid N+1?
Regarding join(:concert_event):
Is there a way to include multiple child joins at once (e.g., join(:concert_event, :workshop_event)) and have the framework recognize and hydrate those so that calling event.concert_event / event.workshop_event does not perform additional queries?

What you were trying to do in one of your examples (joining all the needed child reverse relations) should work in theory:

events = Event.all.join(:concert_event, :workshop_event)

Unfortunately, this doesn't work right now. 😢 The fact that it doesn't is certainly not intended and should be fixed.

EDIT: fixed in e29ec6d.

Is there (or could there be) an API to “downcast on hydrate” when querying the parent, i.e. return concrete subclass instances automatically based on the presence of a child row?

That's an idea, but I would argue that this should never be the default behavior. I see at least two reasons for that:

• Predictability: Users expect that Event.all will give them Event records, not a mix of subclasses unless they've explicitly opted into it.
• Performance: Automatic polymorphism requires an extra join for every child model involved. That's costly and unnecessary if the user only cares about Event fields.