I found an odd problem with Rails 4 Active Record queries where the includes table is not joined if I use a PostgreSQL function in the order. This same query works fine if I remove the PostgreSQL function.
This works fine...
Widget.includes(:sprocket).order("sprockets.name").all
This fails because the includes relationship is not joined...
Widget.includes(:sprocket).order("lower(sprockets.name)").all
Notice the only thing different is the lower(sprockets.name).
I know I can add .references like this...
Widget.includes(:sprocket).references(:sprockets).order("lower(sprockets.name)").all
That will work, but then what is the purpose of the includes?
I've found that replacing includes with eager_load works also, but again, what is the purpose of includes then?
Widget.eager_load(:sprocket).order("lower(sprockets.name)").all
In Rails 3, just using includes works fine. I guess I have a lot of code to change, but just hoping there is an easier fix?
Thank you
Suppose you need to get the user name of first five post. You quickly write the query below and go enjoy your weekend.
posts = Post.limit(5)
posts.each do |post|
puts post.user.name
end
Good. But let's look at the queries
Post Load (0.5ms) SELECT `posts`.* FROM `posts` LIMIT 5
User Load (0.3ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
User Load (0.3ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
User Load (0.3ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 2 LIMIT 1
User Load (0.3ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 2 LIMIT 1
User Load (0.3ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
1 query to fetch all posts and 1 query to fetch users for each post results in a total of 6 queries. Check out the solution below which does the same thing, just in 2 queries:
posts = Post.includes(:user).limit(5)
posts.each do |post|
puts post.user.name
end
#####
Post Load (0.3ms) SELECT `posts`.* FROM `posts` LIMIT 5
User Load (0.3ms) SELECT `users`.* FROM `users` WHERE `users`.`id` IN (1, 2)
There’s one little difference. Add includes(:posts) to your query, and problem solved. Quick, nice, and easy.
But don’t just add includes in your query without understanding it properly. Using includes with joins might result in cross-joins depending on the situation, and you don’t need that in most cases.
If you want to add conditions to your included models you’ll have to explicitly reference them. For example:
User.includes(:posts).where('posts.name = ?', 'example')
Will throw an error, but this will work:
User.includes(:posts).where('posts.name = ?', 'example').references(:posts)
Note that includes works with association names while references needs the actual table name.