user984621 February 2016

Rails 4 - how to use "includes(:model)" with ".select(...)"? Is there any alternative?

I have 3 tables with 45+ columns each. Here are the models:

class Product < ActiveRecord::Base
  belongs_to :manufacturer
  belongs_to :team
end
class Manufacturer < ActiveRecord::Base
  has_many :products
end
class Team < ActiveRecord::Base
  has_many :products
end

I need to fetch all projects at certain stages, so:

  @products = Product.where('products.stage != 3 AND products.stage != 4 AND products.stage != 5')
                     .order(sort_column + " " + sort_direction) 

But - the N+1 problem will occur, so:

  @products = Product.includes(:team, :manufacturer)
                     .where('products.stage != 3 AND product.stage != 4 AND products.stage != 5')
                     .order(sort_column + " " + sort_direction) 

But because the tables have 135+ columns and fetching all of them is ineffective - even more I need only 10 columns (6 from products, 2 from teams and 2 from manufacturers), I wanted to use .select(...) to specify only the fields I need.

But I found out there's no way to use eager loading (includes(...)) with .select(...).

What can I do now? Is there some other approach? I've tried to write it as a raw SQL query, but don't know how to re-write the includes(...) part.

Thank you in advance.

Answers


messanjah February 2016

This solution fires 1 query per association, but only selects the necessary columns. I was unable to find a solution that selected only the desired columns in 1 query.

Firstly, introduce new associations that only select the columns you care about:

# class Product
# change the names to make sense in your app.
belongs_to :manufacturer # this doesn't change
belongs_to :team # this doesn't change

belongs_to :simple_team, -> { select(:id, :other_team_columns) }, class_name: "Team"

belongs_to :simple_manufacturer, -> { select(:id, :other_manufacturer_columns) }, class_name: "Manufacturer"

Then preload them in your Product query (eager_load ignored the select overrides in my testing [Rails 4.2.3]):

# Fires 3 queries, 1 each for Products, Teams and Manufacturers,
# but teams and manufacturers have only the desired columns selected.
@products = Product.preload(:simple_team, :simple_manufacturer)
  .where('products.stage != 3 AND product.stage != 4 AND products.stage != 5')
  .order(sort_column + " " + sort_direction)

Finally, use the new associations in your view:

@products.each do |product|
  product.simple_team.name
  product.simple_manufacturer.name
end


Plamena Gancheva February 2016

You can use joins instead of includes. This works only for belongs_to and has_one associations (not for has_many)

@products = Product.joins(:team, :manufacturer)
                   .select('products.*, 
                            teams.created_at as team_created_at,
                            manufacturers.created_at as manufacturer_created_at')
                   .where('products.stage != 3 AND product.stage != 4 AND products.stage != 5')
                       .order(sort_column + " " + sort_direction) 

@products.first.manufacturer_created_at

The model Product shouldn't have instance methods with the alias names (manufacturer_created_at) otherwise the select will be ignored and the method will be called.

Post Status

Asked in February 2016
Viewed 2,543 times
Voted 4
Answered 2 times

Search




Leave an answer