Prevent upvote model from being called for every comment

I have three models: User, Comment and Upvote. User-to-Comment has a one-to-many relation, Comment-to-Upvote has a one-to-many relation and User-to-Upvote has a one-to-many relation.

I want to do something similar to the upvoting done on Stackoverflow. So when you upvote/downvote the arrow will highlight and remain highlighted even if you refresh the page or come back to the page days/weeks later.

Currently I am doing this:

<% if Upvote.voted?(@user.id, comment.id) %>
  <%= link_to '^', ... style: 'color: orange;'%>
<% else %>
  <%= link_to '^', ... style: 'color:black;'%>
<% end %>

where the voted? method looks like this:

  def self.voted?(user_id, comment_id)
    find_by(comment_id: comment_id, user_id: user_id).present?
  end

So if I have 10 comments on a page, this will load an upvote from my database 10 times, just to check if it exist!

There has to be a better way to go about doing this, but I think my brain stopped working, so I can't think of any.

Answers


Assuming you have properly set relations

# user.rb
class User
  has_many :upvotes
end

we can load comments, current user and his upvotes:

# comments_controller.rb
def index
  @comments = Comment.limit(10)
  @user = current_user
  user_upvotes_for_comments = current_user.upvotes.where(comment_id: @comments.map(&:id))
  @upvoted_comments_ids = user_upvotes_for_comments.pluck(:comment_id)
end

And then change if condition in view:

# index.html.erb
<% if @upvoted_comments_ids.include?(comment.id) %>
  <%= link_to '^', ... style: 'color: orange;'%>
<% else %>
  <%= link_to '^', ... style: 'color:black;'%>
<% end %>

It will require only 2 DB queries. Hope it helps.


We can do it the following way if you want it to be handled by a single query.

Lets make sure the relations are proper

# user.rb
class User < ActiveRecord::Base
  has_many :comments
  has_many :upvotes
end

# comment.rb
class Comment < ActiveRecord::Base
  belongs_to :user
  has_many :upvotes
end

# upvote.rb
class Upvote < ActiveRecord::Base
  belongs_to :user
  belongs_to :comment
end

Then in the controller

def index
  current_user = User.first # current_user may come from devise or any authentication logic you have.
  @comments = Comment.select('comments.*, upvotes.id as upvote').joins("LEFT OUTER JOIN upvotes ON comments.id = upvotes.comment_id AND upvotes.user_id = #{current_user.id}")
end

And in view

# index.html.erb
<% @comment.each do |comment| %>
  <% link_color =  comment.upvote ? 'orange' : 'black' %>
  <%= link_to '^', ...style: "color: #{link_color}" %>
<% end %>
# And all of your logics ;)

If you're limited to N comments per page then you can probably do this in two queries using the limit and offset methods to return the 1st, 2nd, ... ith set of N comments for the ith page, something like (syntax may be off, Ruby isn't my primary language)

comment_ids = 
    Comments.select("comment_id")
            .where(user_id: user_id)
            .order(post_date/comment_id/whatever)
            .offset(per_page * (page_number - 1)) // assumes 1-based page index
            .limit(per_page)

This gives you a list of comment_ids which you can use to query Upvote:

upvoted_comments = 
    Upvotes.select("comment_id")
           .where(user_id: user_id, comment_id: comment_ids)

If you're sorting the comment_ids by a column that also exists in Upvote (e.g. if you're sorting by comment_id) then you can replace the Upvote set query with a range query.

Put upvoted_comments in a hash and you're good to go - if the comment_id is in the hash then it's been upvoted, else not.


I'm not sure this will avoid excess queries in this state but maybe you could include the upvotes when you fetch comments:

@comments = Comment.includes(:upvotes).where(foo: 'bar').limit(10)

Then in the view:

<%=
  link_color =  comment.upvotes.map(&:user_id).include?(@user.id) ? 'orange' : 'red'
  link_to '^', ...style: "color: #{link_color}"
%>

Need Your Help

Phonegap, BackboneJS, UnderscoreJS, RequireJS issues with templates

android cordova backbone.js requirejs underscore.js

I am writing an Android app with Phonegap, BackboneJS, UnderscoreJS, RequireJS.