Rails & Active Record: Find objects whose children don't belong to current_user OR who don't have any children

Question

User.rb

 has_many :votes

Event.rb

  has_many :votes

Vote.rb

 belongs_to :user
 belongs_to :event

I am trying to render all the Events that the current_user has not voted on.

Consider this situation:

• There are 100 events Event.includes(:votes).count ==> 100
• The User has voted on 5 events current_user.votes.count ==> 5
• There are 75 events with at least one vote from other users
• 20 of the events have not received votes from any users

The result I'm looking for should render the 95 events that have not been voted on by the current_user, including events that have not been voted on by any user.

This query gets all the events that NO users have voted on:

Event.includes(:votes).where(:votes => {:id => nil}).references(:votes).count ==> 20

What query can I use to get all the events that have been voted on by users excluding those that have been voted on by current user (should return 75 in the example above)? I tried the below query but it returned 80 (it included events voted on by the current_user):

Event.includes(:votes).where.not(:votes => {:id => current_user}).references(:votes).count ==> 80

1. What query can I use to get events with votes, excluding ones that current user voted for
2. Can I combine the two queries into one?

Answer 1

Hacky but fun answer

class Event
  has_many :votes
  has_many :user_votes,
             ->() { where({user_id: Thread.current[:user]&.id}.compact) },
             class_name: 'Vote'

  def self.using_user(user)
    old_user, Thread.current[:user] = Thread.current[:user], user
    yeild
  ensure
    Thread.current[:user] = old_user
  end
end

Event.using_user(current_user) do
  Event.includes(:user_votes).where("votes.id IS NULL")
end

Original answer:

Event.
  joins(
   "LEFT OUTER JOIN votes ON votes.event_id = events.id AND " +
   "votes.user_id = #{user.id}"
  ).
  where("votes.id IS NULL")

Answer 2

Can I combine the two queries into one?

Use merge if you want to combine using AND:

results = frecords.merge(lrecords)

If you want to combine using OR, use or (only available in ActiveRecord 5+):

results = frecords.or(lrecords)

Answer 3

1. What query can I use to get events with votes, excluding ones that current user voted for.

events_current_user_voted_on = current_user.votes.pluck(:event_id) 
events_all_users_voted_on = Vote.pluck(:event_id).uniq

events_only_others_voted_on = events_all_users_voted_on - events_current_user_voted_on

# 75 events with at least one vote from other users
Event.where(id: events_only_others_voted_on)

Answer 4

Answer for your first question: I have modified your tried query a little bit so try this

Event.includes(:votes).where.not(:votes => {:user_id => current_user}).references(:votes).count

notice the where condition. You need to check user_id column of votes or you can try string syntax like:

Event.includes(:votes).where.not("votes.user_id" => current_user).references(:votes).count

about your second question, I am not sure I understand your requirement. If you need to filter events with at least 1 vote and from other user only then just add another to where statement .

Event.includes(:votes).where.not("votes.user_id" => current_user, "votes.user_id" => nil).references(:votes).count

And BTW, I think you have a error in vote.rb you have specified a association to itself instead of association to Event i.e belongs_to :event