1

What's the best method to show user his vote type (for/against/abstain) on page with lots of post items?

direct query is

SELECT vote_type FROM votes WHERE post_id = 888 AND user_id = 888

and direct way with rails

post.votes.where(:user_id => current_user.id).pluck(:vote_type).first

but it's too heavy and stupid to make db query for every post.

I can build array of posts_id and make one query

@posts.each do |post|
  ids << post.id
end
votes = Vote.select(:post_id, :vote_type).where(:user_id => current_user.id, :post_id => ids)

SELECT post_id, vote_type FROM votes WHERE user_id = 888 AND post_id IN (887, 888, 889)

Is there built-in "magick" methods in rails? I use postgresql.

UPD +1 same write method

current_user.votes.find_by_post_id(post.id)

UPD2 Dummy-table on pastebin

br.
  • 1,259
  • 1
  • 14
  • 21
  • Looks like you're looking for eager loading of associations. If you could post your model associations as well as your controller action it would be possible to give you a complete query. http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations – Marcelo De Polli Jan 05 '14 at 23:16
  • Yes, that's it. Models assoc is simple: User(has_many: posts, votes), Post(belongs_to: user, has_many: votes), Vote(belongs_to: post, user). what is better - 1000 partially cached(?) small queries or 1 large query with IN (1..1000)? – br. Jan 05 '14 at 23:47
  • @depa, something like this? `@posts = Post.includes(:votes).where("votes.user_id" => current_user.id)` But this returns only posts with votes and makes *scary* SQL query. – br. Jan 06 '14 at 00:07
  • Yeah, that should be it. Do you need data from any other tables other than `posts` and `votes`? – Marcelo De Polli Jan 06 '14 at 00:43
  • @depa, i need all posts from all users and information about current users vote type for each post if vote exists – br. Jan 06 '14 at 00:47
  • It seems the vote type is a column in the `votes` table. So, it looks like you're set. That query you posted looks OK. Don't worry too much about the scary part. The important thing is to check if you're only running 2 queries for the whole page, rather than a new one for each individual vote. I highly recommend the bullet gem for that: https://github.com/flyerhzm/bullet – Marcelo De Polli Jan 06 '14 at 01:07
  • 1
    thanks, but my query returns only post with current user votes, not all – br. Jan 06 '14 at 01:48

2 Answers2

0
@posts = Post.all.includes(:votes).where(posts: { user_id: current_user.id })
Marcelo De Polli
  • 28,123
  • 4
  • 37
  • 47
  • 1
    this gets all posts with ALL votes, not only current user votes. I need something like this `(Post.all) + (includes(:votes).where(:user_id => current_user.id))` – br. Jan 06 '14 at 11:11
  • I think, the problem is in condition. If you use only includes - all posts will load, but if you use inclides with conditions, there's will be only posts which includes votes which match conditions – br. Jan 06 '14 at 12:09
  • nope. nope. nope. Not about that. I am user, i want to see page with all posts of any users. I'm voting for one of posts and after page reload i want to know how i vote. Like here, you can vote for my comment and you will see your vote type after page will reload. – br. Jan 06 '14 at 13:11
0

How about 

votes = Post.
  joins(:user).
  joins("LEFT JOIN votes ON votes.post_id = posts.id AND votes.user_id = 1").
  select("posts.id, posts.title, users.id AS users_id, votes.vote_type")

with a 

votes.map {|p| [p.id, p.title, p.users_id, p.vote_type] }

I get 

[[1, "Say hello", 1, nil], [2, "Amazing world", 2, 1]]
Mads Ohm Larsen
  • 3,315
  • 3
  • 20
  • 22
  • Thank you, oldschool way usually works :) I think, there is no built-in methods (like associations eg.) to make this in rails? – br. Jan 06 '14 at 22:39