Given a dynamic export feature where users could select a column they would want to include in an export, some columns are an N+1, and some require multiple joining of tables.
Ex:
Tables:
Customer Table: id, first_name, address_id Address Table: id, street, city Pet Table: id, animal_type, customer_id ---- Customer could own multiple pets
Columns to export in UI:
Customer Name
, Full Address
, Pets
Sample result:
Row 1: John Doe | Alcala st. Zone city | cat,dog,bird
There are 2 ways that I could think of to achieve this in rails, first is with a straightforward with N+1 approach below:
Approach 1:
excel_sheet << customer.pets.pluck(:breed).join(',')
Problem: N+1 query in Pets
column.
Approach 2:
To generate a single raw complicated query with multiple joins and subquery to pets
table.
Problem: Query could get complicated/dirty and hard to maintain in the future.
Database structure given is just for representation purposes and the actual project consists of more tables to join. Also considering of using this Batchloader gem https://github.com/exAspArk/batch-loader to lessen the N+1 of approach 1.
Consider this as an export feature that returns millions of records.
Advertisement
Answer
What’s the problem with using includes
, as you would normally? You could have something like the following:
# prepares the query with eager-loaded associations, but doesn't execute it customers = Customer.includes(:address, :pets) # `find_each` retrieves customers in batches of 1000 from the database. # When using it with `includes`, the associations are batched as well customers.find_each do |customer| row = [ customer.full_name, "#{address.street} #{city}", pets.pluck(:breed).join(',') # this pluck is on the pre-loaded association ].join('|') # whatever your export logic is - like a CSV stream export(row) end
If you want to avoid eager-loading every association, you could dynamically build your includes
like this:
includes = [:address] includes << :pet if selected_columns.include?(:pet_breed) customers = Customer.includes(*includes)