CODEWITHSUNDEEP
sqlruby-on-railseager-loading
Harry Moreno
Harry Moreno

Reputation: 11643

efficiently traverse all users and return 3 associated items [SQL] [Rails]

I'm running rails 4. We have a User model has_many Posts. I would like to create a 'feed' that displays a maximum of three posts from every user in the site.

the closest I could get was something along the lines of

  # GET /users
  # GET /users.json
  def index
    users = User.includes(:posts)
    users.each do |user|
       @feed_items << user.posts.limit(3)
    end
    @comment = Comment.new
  end

and my view would display all the items in @feed_items, but rails doesn't let you concatenate queries? How can I do this in Rails without doing an n+1 query? Do I need a custom sql query?

ref: http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations

Upvotes: 0

Views: 114

Answers (1)

Gene
Gene

Reputation: 46990

The includes already reads all the posts with every user in a single query. Therefore you should be able to get the first three of each in ruby without querying the database again.

users = User.includes(:posts)
@feed_items = []
users.each do |user|
   @feed_items += user.posts.first(3)
end

This forms one long list of posts. If you want a list of per user lists, use << instead +=.

I was able to test this. Here are the queries generated:

User Load (4.4ms)  SELECT "users".* FROM "users" 
Post Load (2.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (4, 5, 6, 7, 8, 9, 10, 11, 12, 13)

And here is the result:

irb(main):037:0> @feed_items.map &:subject
=> ["User 0's Post 1", "User 0's Post 2", "User 0's Post 3", "User 1's Post 1", 
"User 1's Post 2", "User 1's Post 3", "User 2's Post 1", "User 2's Post 2", 
"User 2's Post 3", "User 3's Post 1", "User 3's Post 2", "User 3's Post 3", 
"User 4's Post 1", "User 4's Post 2", "User 4's Post 3", "User 5's Post 1", 
"User 5's Post 2", "User 5's Post 3", "User 6's Post 1", "User 6's Post 2", 
"User 6's Post 3", "User 7's Post 1", "User 7's Post 2", "User 7's Post 3", 
"User 8's Post 1", "User 8's Post 2", "User 8's Post 3", "User 9's Post 1", 
"User 9's Post 2", "User 9's Post 3"]

The down side of includes is always that you may end up retrieving data that's not needed. Here if users have many more than 3 posts, retreiving all and throwing away all but 3 may be more expensive than one query per user.

Server side solutions require fancy SQL. You can search "sql limit within group" to see some proposals.

Upvotes: 1

Related Questions