Rails: better approach in handling dynamic column export feature-CodePudding

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.

CodePudding user response：

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)