rails select and include

Question

Can anyone explain this?

Project.includes([:user, :company])

This executes 3 queries, one to fetch projects, one to fetch users for those projects and one to fetch companies.

Project.select("name").includes([:user, :company])

This executes 3 queries, and completely ignores the select bit.

Project.select("user.name").includes([:user, :company])

This executes 1 query with proper left joins. And still completely ignores the select.

It would seem to me that rails ignores select with includes. Ok fine, but why when I put a related model in select does it switch from issuing 3 queries to issuing 1 query?

Note that the 1 query is what I want, I just can't imagine this is the right way to get it nor why it works, but I'm not sure how else to get the results in one query (.joins seems to only use INNER JOIN which I do not in fact want, and when I manually specifcy the join conditions to .joins the search gem we're using freaks out as it tries to re-add joins with the same name).

possible duplicate of [Rails 3 - select with Include?](http://stackoverflow.com/questions/4047833/rails-3-select-with-include) — Ciro Santilli OurBigBook.com, Jan 26 '14 at 08:58

score 25 · Answer 1 · answered Jul 26 '12 at 13:42

25

I had the same problem with select and includes. For eager loading of associated models I used native Rails scope 'preload' http://apidock.com/rails/ActiveRecord/QueryMethods/preload It provides eager load without skipping of 'select' at scopes chain.

I found it here https://github.com/rails/rails/pull/2303#issuecomment-3889821

Hope this tip will be helpful for someone as it was helpful for me.

answered Jul 26 '12 at 13:42

suhovius

426
1
6
10

3

This is the real answer. `:preload` FTW – Cody Caughlan Jun 20 '13 at 23:13

score 9 · Accepted Answer · answered Nov 20 '10 at 20:54

Allright so here's what I came up with...

.joins("LEFT JOIN companies companies2 ON companies2.id = projects.company_id LEFT JOIN project_types project_types2 ON project_types2.id = projects.project_type_id LEFT JOIN users users2 ON users2.id = projects.user_id") \
.select("six, fields, I, want")

Works, pain in the butt but it gets me just the data I need in one query. The only lousy part is I have to give everything a model2 alias since we're using meta_search, which seems to not be able to figure out that a table is already joined when you specify your own join conditions.

I know it's vague, but the correct response is 'Don't over optimize'. 99% of the time just let it do it's thing. — Kevin, Nov 17 '12 at 21:53

rwilliams · Answer 3 · 2010-11-13T00:27:49.903

2

Rails has always ignored the select argument(s) when using include or includes. If you want to use your select argument then use joins instead.

You might be having a problem with the query gem you're talking about but you can also include sql fragments using the joins method.

Project.select("name").joins(['some sql fragement for users', 'left join companies c on c.id = projects.company_id'])

I don't know your schema so i'd have to guess at the exact relationships but this should get you started.

edited Nov 13 '10 at 00:27

answered Nov 13 '10 at 00:08

rwilliams

21,188
6
49
55

1

Actually my question was more along the lines of, if select is ignored, why does it change how the query is run? Specifically, when using includes without select, there are multiple queries issued, oen for the mail model and one for each association. With select added on, however, only one query is run for all data. The reason I'm asking this is that for performance, I want only one query executed for this data. And I'm trying to avoid joins, since they use an inner join, or if I specify left join then they cause issues with the meta_search gem when it tries to add the join on again. – Chad Nov 15 '10 at 17:14
Which search gem are you using? – rwilliams Nov 15 '10 at 21:26

score 0 · Answer 4 · answered Apr 09 '15 at 05:16

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.

score 0 · Answer 5 · answered Nov 12 '10 at 23:58

0

I might be totally missing something here but select and include are not a part of ActiveRecord. The usual way to do what you're trying to do is like this:

Project.find(:all, :select => "users.name", :include => [:user, :company], :joins => "LEFT JOIN users on projects.user_id = users.id")

Take a look at the api documentation for more examples. Occasionally I've had to go manual and use find_by_sql:

Project.find_by_sql("select users.name from projects left join users on projects.user_id = users.id")

Hopefully this will point you in the right direction.

answered Nov 12 '10 at 23:58

Dan Harper

1,130
10
22

3

select and include are part of the Rails 3 query interface http://guides.rubyonrails.org/active_record_querying.html – rwilliams Nov 13 '10 at 00:05
I thought that might have been the case (I have yet to delve into v3), although I couldn't find reference to them in the API docs. The syntax seems a little counter-intuitive from what I can see. – Dan Harper Nov 13 '10 at 05:43

score 0 · Answer 6 · answered Nov 24 '22 at 08:46

The preload solution doesn't seem to do the same JOINs as eager_load and includes, so to get the best of all worlds I also wrote my own, and released it as a part of a data-related gem I maintain, The Brick.

By overriding ActiveRecord::Associations::JoinDependency.apply_column_aliases() like this then when you add a .select(...) then it can act as a filter to choose which column aliases get built out.

With gem 'brick' loaded, in order to enable this selective behaviour, add the special column name :_brick_eager_load as the first entry in your .select(...), which turns on the filtering of columns while the aliases are being built out. Here's an example:

Employee.includes(orders: :order_details)
        .references(orders: :order_details)
        .select(:_brick_eager_load,
                'employees.first_name', 'orders.order_date', 'order_details.product_id')

Because foreign keys are essential to have everything be properly associated, they are automatically added, so you do not need to include them in your select list.

Hope it can save you both query time and some RAM!

rails select and include

6 Answers6

Linked