ActiveRecord includes. Specify included columns

Question

I have model Profile. Profile has_one User. User model has field email. When I call

Profile.some_scope.includes(:user)

it calls

SELECT users.* FROM users WHERE users.id IN (some ids)

But my User model has many fields that I am not using in rendering. Is it possible to load only emails from users? So, SQL should look like

SELECT users.email FROM users WHERE users.id IN (some ids)

score 46 · Accepted Answer · edited Jun 10 '20 at 12:47

46

Rails doesn't have the facility to pass the options for include query. But we can pass these params with the association declaration under the model.

For your scenario, you need to create a new association with users model under the profile model, like below

belongs_to :user_only_fetch_email, :select => "users.id, users.email", :class_name => "User"

I just created one more association but it points to User model only. So your query will be,

Profile.includes(:user_only_fetch_email)

or

Profile.includes(:user_only_fetch_email).find(some_profile_ids)

edited Jun 10 '20 at 12:47

Sebastián Palma

32,692
6
40
59

answered Sep 13 '12 at 15:23

Mohanraj

4,056
2
22
24

2

The foreign_key option must be added when using select. From the manual: guides.rubyonrails.org/association_basics.html#belongs-to-association-reference 4.1.3.4 select The select method lets you override the SQL SELECT clause that is used to retrieve data about the associated object. By default, Rails retrieves all columns. If you use the select method on a belongs_to association, you should also set the :foreign_key option to guarantee the correct results. – jogaco Dec 23 '14 at 16:38
11

Had to change the syntax a bit to get it to work (Rails 4): `belongs_to :user_only_fetch_email, -> {select('users.id, users.email')}, class_name: 'User', foreign_key: 'user_id'` – CHawk May 09 '16 at 10:31
I can't get this to work on Rails 3 unfortunately. I added foreign key as well, but when running the query, it does not give a error, but also does not load the association + fields. EDIT: never mind, I needed the `:has_one` assocation instead of the `:belongs_to` , now it works perfectly! – Peterdk Aug 26 '17 at 11:24

score 16 · Answer 2 · answered Sep 13 '12 at 09:32

16

If you want to select specific attributes, you should use joins rather than includes.

From this asciicast:

the include option doesn’t really work with the select option as we don’t have control over how the first part of the SELECT statement is generated. If you need control over the fields in the SELECT then you should use joins over include.

Using joins:

Profile.some_scope.joins(:users).select("users.email")

answered Sep 13 '12 at 09:32

Chris Salzberg

27,099
4
75
82

1

I'm not sure that answers the question, but it's exactly what I was looking for – Steve May 30 '13 at 03:08
18

this is true, but :joins doesn't accomplish the magical eager-loading of associations that :includes does. – Jacob Jul 20 '13 at 21:20
You can use :includes but you need to use :references as well to specify which tables you reference in your where clause – weexpectedTHIS May 23 '14 at 22:10
Using includes with references brings you back to the initial problem of this post. – bkunzi01 Nov 09 '19 at 17:13

Musaffa · Answer 3 · 2014-12-09T01:48:16.230

You need extra belongs to in the model.

For simple association:

belongs_to :user_restricted, -> { select(:id, :email) }, class_name: 'User'

For Polymorphic association (for example, :commentable):

belongs_to :commentable_restricted, -> { select(:id, :title) }, polymorphic: true, foreign_type: :commentable_type, foreign_key: :commentable_id

You can choose whatever belongs_to name you want. For the examples given above, you can use them like Article.featured.includes(:user_restricted), Comment.recent.includes(:commentable_restricted) etc.

EarlyZhao · Answer 4 · 2018-08-27T04:26:07.363

Rails does not support to select specific columns when includes. You know ,it's just lazy load.

It use the ActiveRecord::Associations::Preloader module to load the associated data before data actually using. By the method:

def preload(records, associations, preload_scope = nil)
    records = Array.wrap(records).compact

    if records.empty?
      []
    else
      records.uniq!
      Array.wrap(associations).flat_map { |association|
        preloaders_on association, records, preload_scope
      }
    end
 end

preload_scope the third params of preload, is a way to select specify columns. But can't lazy load anymore.

At Rails 5.1.6

relation = Profile.where(id: [1,2,3])
user_columns = {:select=>[:updated_at, :id, :name]}
preloader = ActiveRecord::Associations::Preloader.new
preloader.preload(relation, :user, user_columns)

It will select the specify columns you passed in. But, it just for single association. You need create a patch for ActiveRecord::Associations::Preloader to support loading multiple complex associations at once.

Here is a example for patch

The way to use it, example

I like this solution, as it prevent littering the code with a belongs_to in an unrelated place. Note that in Rails 6 `preload_scope` changed to be an Active record Relation instead of a Hash, so in the above example it would become: `user_columns = User.select(:updated_at, :id, :name)` — Jan M, Oct 29 '19 at 18:58

score 2 · Answer 5 · answered Apr 09 '15 at 05:26

I wanted that functionality myself,so please use it. Include this method in your class

#ACCEPTS args in string format "ASSOCIATION_NAME:COLUMN_NAME-COLUMN_NAME"

def self.includes_with_select(*m)
    association_arr = []
    m.each do |part|
      parts = part.split(':')
      association = parts[0].to_sym
      select_columns = parts[1].split('-')
      association_macro = (self.reflect_on_association(association).macro)
      association_arr << association.to_sym
      class_name = self.reflect_on_association(association).class_name 
      self.send(association_macro, association, -> {select *select_columns}, class_name: "#{class_name.to_sym}")
    end
    self.includes(*association_arr)
  end

And you will be able to call like: Contract.includes_with_select('user:id-name-status', 'confirmation:confirmed-id'), and it will select those specified columns.

well for some reason for me there are still all selected @articles = @articles.includes_with_select("social_post:followers") but then "social_posts"."message" AS t3_r7, "social_posts"."sentiment" AS t3_r8, "social_posts"."url" AS t3_r9 appears in the sql — bormat, Oct 14 '21 at 16:04

score 0 · Answer 6 · answered Aug 28 '14 at 21:49

0

Using Mohanaj's example, you can do this:

belongs_to :user_only_fetch_email, -> { select [:id, :email] }, :class_name => "User"

answered Aug 28 '14 at 21:49

Yan Zhao

213
4
8

ActiveRecord includes. Specify included columns

6 Answers6

At Rails 5.1.6

Linked