Mix :select with :include in find method (Rails 2)

Question

I have 2 models, User and UserProfile. A user has_one user_profile and a user_profile belongs_to user.

1) Find without select

This query in console works fine, and take only 2 SQL queries.

>> User.find(:all, :limit => 10, :include => [ :user_profile ])

User Load (0.3ms)   SELECT * FROM `users` LIMIT 10
UserProfile Load (0.3ms)   SELECT `user_profiles`.* FROM `user_profiles`
      WHERE (`user_profiles`.user_id IN (1,2,3,...))

2) Find with select on user model

I can select columns from User model, with

>> User.find(:all, :select => '`users`.id, `users`.last_name',
     :limit => 10, :include => [ :user_profile ])

User Load (0.3ms)   SELECT `users`.id, `users`.last_name FROM `users` LIMIT 10
UserProfile Load (0.2ms)   SELECT `user_profiles`.* FROM `user_profiles`
      WHERE (`user_profiles`.user_id IN (17510,18087,17508,17288...))

Everything works fine. Note that I must set users.id in the user selected columns, because the second query doesn't work (return NULL).

3) Find with select on user_profile model

But when I try to select columns from UserProfile model, I got only 1 query, which doesn't take care of my :select

>> User.find(:all,
     :select => '`users`.id, `users`.last_name, `user_profiles`.permalink',
     :limit => 10, :include => [ :user_profile ])

User Load Including Associations (0.6ms) SELECT `users`.`id` AS t0_r0,
  `users`.`login` AS t0_r1, ....
  `user_profiles`.`id` AS t1_r0,
  `user_profiles`.`birth_date` AS t1_r1,
  LEFT OUTER JOIN `user_profiles` ON user_profiles.user_id = users.id LIMIT 10

As you can see, the Rails query contains fiels from users and fields from user_profiles that I didn't select.

4) Join method

Codeit purpose a method with join function :

user_details = User.find(:all,
   :select => '`users`.id, `users`.last_name, `user_profiles`.permalink',
   :limit => 10, :joins => [ :user_profile ]
)

User Load (0.2ms) SELECT `users`.id, `users`.last_name, `user_profiles`.permalink
   FROM `users`
   INNER JOIN `user_profiles` ON user_profiles.user_id = users.id
   LIMIT 10

This solution works fine with SQL queries, but doesn't make 'link' between User and User Profile. 10 new queries are needed, while the method 1 and 2 make only 2 SQL queries.

 user_details.map(&:user_profile).map(&:permalink)
 UserProfile Load (0.3ms)   SELECT * FROM `user_profiles` WHERE (`user_profiles`.user_id = 1) LIMIT 1
 UserProfile Load (0.2ms)   SELECT * FROM `user_profiles` WHERE (`user_profiles`.user_id = 2) LIMIT 1
 ... (10 times) ...
 UserProfile Load (0.3ms)   SELECT * FROM `user_profiles` WHERE (`user_profiles`.user_id = 10) LIMIT 1

Is there a right syntax to have same results than the 2 first queries, but with a :select witch select only a few columns of my models ?

Rahul Tapali · Answer 1 · 2013-03-18T18:12:39.810

0

Use join:

User.find(:all,
 :select => '`users`.id, `users`.last_name, `user_profiles`.permalink',
 :limit => 10, :joins => [ :user_profile ])

include is used for eager loading. It is used to solve (N+1) queries problem for accessing user_profile when you have large users with user_profile. If you want to select columns of included table you need to use join. If you use columns of included table it will just ignored from select clause.

EDIT:

user_details = User.find(:all,
   :select => '`users`.id, `users`.last_name, `user_profiles`.permalink',
   :limit => 10, :joins => [ :user_profile ]
 )

user_details.map(&:permalink)

edited Mar 18 '13 at 18:12

answered Mar 18 '13 at 17:25

Rahul Tapali

9,887
7
31
44

Yes, I thought about `joins`, but `joins` does not make the link between User and UserProfile... Then when I do your query followed by .map(&:user_profile), it takes 10 queries. – pierallard Mar 18 '13 at 17:29
what do you mean by `doesn't link` ?? Can paste the query ?? When you use `.map(&:user_profile)` it will fire `N` queries. Thats the difference between `include` and `join`. – Rahul Tapali Mar 18 '13 at 17:51
Why are using `map(&:user_profile)` when you selected `permalink` just do `previous_query.map(&:permalink)`. I guess it works without firing extra queries. – Rahul Tapali Mar 18 '13 at 18:09

Mix :select with :include in find method (Rails 2)

1 Answers1