Ruby ActiveRecord Query with has_many Association

Question

I have a system that has a User, Message, and MessageToken models. A User can create Messages. But when any User reads the Messages of others a MessageToken is created that associates the reader (User) to the Message. MessageTokens are receipts that keep track of the states for the user and that particular message. All of my associations in the Models are set up properly, and everything works fine, except for structuring a very specific query that I cannot get to work properly.

---

User.rb

has_many :messages

Message.rb

belongs_to :user
has_many :message_tokens

MessageToken.rb

  belongs_to :user
  belongs_to :message

---

I am trying to structure a query to return Messages that: Do not belong to the user; AND { The user has a token with the read value set to false OR The user does not have a token at all }

The later part of the statement is what is causing problems. I am able to successfully get results for Messages that are not the user, Messages that the user has a token for with read => false. But I cannot get the expected result when I try to make a query for Messages that have no MessageToken for the user. This query does not error out, it just does not return the expected result. How would you structure such a query?

Below are the results of my successful queries and the expected results.

130 --> # Messages
Message.count


78  --> # Messages that are not mine
Message.where.not(:user_id => @user.id)


19  --> # Messages that are not mine and that I do not have a token for


59  --> # Messages that are not mine, and I have a token for
Message.where.not(:user_id => @user.id).includes(:message_tokens).where(message_tokens: {:user_id => @user.id}).count
Message.where.not(:user_id => @user.id).includes(:message_tokens).where(["message_tokens.user_id = ?", @user.id]).count

33  --> # Messages that are not mine, and I have a token for, and the token is not read
Message.where.not(:user_id => @user.id).includes(:message_tokens).where(message_tokens: {:user_id => @user.id, :read => false}).count
Message.where.not(:user_id => @user.id).includes(:message_tokens).where(["message_tokens.user_id = ? AND message_tokens.read = false", @user.id]).references(:message_tokens).count

---

The Final Expected Result

52 --> # Messages that are not mine and: I have a token for that is not read OR I do not have a token for

---

My best attempt at a query to achieve my goal

64  --> # Wrong number returned, expected 52
Message.where.not(:user_id => @user.id).includes(:message_tokens).where(["(message_tokens.user_id = ? AND message_tokens.read = false) OR message_tokens.user_id <> ?", @user.id, @user.id]).references(:message_tokens).count

---

The problem lies in the query trying to find Messages that are not the users and that the user does not have a token for

63 --> #This does not yield the expected result, it should == 19 (the number of Messages that are not mine and that I do not have a token for)
Message.where.not(:user_id => @user.id).includes(:message_tokens).where.not(message_tokens: {:user_id => @user.id}).count
Message.where.not(:user_id => @user.id).includes(:message_tokens).where(["message_tokens.user_id <> ?", @user.id]).references(:message_tokens).count

How can I solve this?

Answer 1

Ok, so thanks to the help of R11 Runner I was able to come up with a solution, which required using pure SQL. I could not use the Squeel gem or ActiveRecord as there was no equivalent to SQL's NOT EXISTS operator, which was the crucial component missing.

The reason this works is because unlike the other solutions the NOT EXISTS operator will return all records from the Messages table where there are no records in the MessageTokens table for the given user_id, whereas using where.not would look for the first match instead not ensuring the non existence that was needed.

Message.find_by_sql ["SELECT * FROM messages where messages.user_id <> ? 
                      AND (
                          (EXISTS (SELECT * FROM message_tokens WHERE message_id = messages.id AND user_id = ? AND read = FALSE)) 
                          OR
                          (NOT EXISTS (SELECT * FROM message_tokens WHERE message_id = messages.id AND user_id = ?))
                          )",@user.id, @user.id, @user.id] 

Answer 2

If you don't mind using 2 queries, a possible solution would be:

messages_not_written_by_user =  Message.where.not(:user_id => @user.id)
messages_already_read_by_user = Message.where.not(:user_id => @user.id).includes(:message_tokens).where(message_tokens: {:user_id => @user.id, :read => true})
messages_not_read_by_user_yet = messages_not_written_by_user - messages_already_read_by_user

I would personally find this syntax more readable:

messages_not_written_by_user =  Message.where.not(:user => @user).count
messages_already_read_by_user = Message.where.not(:user => @user).includes(:message_tokens).where(message_tokens: {:user => @user, :read => true}).count

One remark to this query:

63 --> #This does not yield the expected result, it should == 19 (the number of Messages that are not mine and that I do not have a token for)
Message.where.not(:user_id => @user.id).includes(:message_tokens).where.not(message_tokens: {:user_id => @user.id}).count

This query searches for all the messages which have a token with an arbitrary other user. (If msg1 has a token with @user, and it also has a token with @another_user, this query will find it.)

Answer 3

Full disclosure - I'm not sure how I'd do this as you have it set up right now. However: are you against installing a gem to help? If you're not, I'd suggest you look into the Squeel gem (https://github.com/activerecord-hackery/squeel).

Squeel makes these kinds of associations a lot easier and allows use to use the plain old | operator. It's built on Arel and shouldn't effect anything you've written in ActiveRecord (at least in my experience). Hope that helps!