Optimize associated queries

Question

Lets take the following example. We have two models with this association

class Post < ActiveRecord::Base
  belongs_to :user  
end

class User < ActiveRecord::Base
  has_many :posts
end

So as you know, we can do the following:

<%= @post.title %>
<%= @post.user.name %>
etc etc

This association usually generates the following queries:

SELECT * FROM `posts` WHERE `post`.`id` = 1;
SELECT * FROM `users` WHERE `user`.`id` = 15; # from @post.user

Now if I want to select some specific fields (lets assume for a moment there's no association) when listing all posts or show just a single one, I do something like this:

@posts = Post.select("id, title, slug, created_at").all()
# or
@post = Post.select("id, title, slug, created_at").find(1)

In case of association how can I select specific fields for the associated query? In other words instead of having

SELECT * FROM `users`
SELECT * FROM `posts` WHERE `user_id` IN ( user IDs here )

to have

SELECT `id`, `name` FROM `users`
SELECT `id`, `user_id`, `title`, `slug` FROM `posts` WHERE `user_id` IN ( user IDs here )

score 0 · Answer 1 · answered Oct 14 '16 at 08:44

0

You need to refer below link about association

http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html

answered Oct 14 '16 at 08:44

Hardik Upadhyay

2,639
1
19
34

I did an update on my queries.. basically I wand to use `select` with `includes` on Rails 4 – ltdev Oct 14 '16 at 10:50
you can use like `Post.includes(:user).select("posts.id, posts.title, users.name AS user_name")` – Hardik Upadhyay Oct 14 '16 at 11:24

score 0 · Accepted Answer · edited May 23 '17 at 10:32

When you retrieve a single Post restricting it's attributes in select clause, it behaves like any other Post, but in order to have access to the User it should have the user_id attribute selected.

@post = Post.select("id").first
# this post doesn't know about it's user since it has no user_id attribute
@post.user # => nil

@post = Post.select("id", "user_id").first
# this post has a user
@post.user # => #<User...>

When you retrieve a list of posts you should solve the 'n+1 queries problem'. You can read about it here http://guides.rubyonrails.org/active_record_querying.html (13 Eager Loading Associations)

You can use includes for eager loading associations:

@posts = Post.includes(:user)

@posts.each do |post|
  post.user # this will not make an extra DB query since users are already eager loaded
end

Or if you don't need to instantiate users and want just get specific attributes, the better solution is to join with users and take attributes that you need

@posts = Post.joins(:user).select("posts.id", "posts.title", "users.name AS user_name")

@posts.each do |post|
  post.user_name # each post now has `user_name` attribute
end

Edited

As far as I know using includes will ignore select. You can't use select here to specify which attributes of a Post or a User you need. The whole Post will be retrieved and the whole Users will be eager loaded. However, there is a way to restrict user's loading attributes by specifying them in belongs_to association. There is a similar question about this: ActiveRecord includes. Specify included columns

Also, keep in mind, that Post.joins(:user) will not return posts that have no user. So, if it is possible and you need all posts use .left_outer_joins instead.

# works in Rails 5
Post.left_outer_joins(:user).select(...)

In my example I have this code `@posts = Post.select("id, user_id, title, slug").includes(:user)` .. So, if I understand correct in both answers, in order to select specific fields from `users` (such as only id and name) I should use `joins` instead of `includes` ?? — ltdev, Oct 14 '16 at 09:51
I don't completely understand your question. I added some extra explanation to my answer. Use `joins` if you don't need a User instance for each Post (post.user), but only Post instances with some attributes that belong to their users. Use `includes` when you need not just user's attributes, but a user instance in order to use logic from User model. — chumakoff, Oct 14 '16 at 22:22

Optimize associated queries

2 Answers2