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

Question

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

There's nothing there that does a `n+1` query. What gives you the idea one is required? — Gene, Dec 18 '13 at 01:49

Gene · Accepted Answer · 2013-12-18T15:38:04.600

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.

<< is undefined for nil class. I don't believe @feed_items can be treated that way. — Harry Moreno, Dec 18 '13 at 03:20
@HarryMoreno I was able to test. Works fine. Hope this is helpful. — Gene, Dec 18 '13 at 15:38
on another note I think there will be many more posts per user in the future. I'm thinking of creating a separate index, where a user can select their 'top 3'. Then I wouldn't be costing my app as much ram. How would I do this? another table? — Harry Moreno, Dec 18 '13 at 21:43
I worked on an on-line contest where all contest entries were stored forever and I frequently had to retrieve a list of the top-N teams ranked by their best entries. This is similar to your problem. Only needed the best of many entries per team. I ended up keeping a "bests" table that maintained foreign keys to the best entry of every team. It was the only way I could find to make the top-N teams query efficient enough. Your problem is similar, but you need top-3 per user (team in my case). Note you probably must use Rails locking to make the related updates in the two tables look atomic. — Gene, Dec 18 '13 at 22:36

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

1 Answers1