hazel_jester hazel_jester - 1 month ago 10
Ruby Question

Query with join produce uncompleted result?

Here is how relations look like

class Conversation < ApplicationRecord
belongs_to :user
has_many :messages

class Message < ApplicationRecord
belongs_to :sender, class_name: 'User'
belongs_to :receiver, class_name: 'User'
belongs_to :conversation


I want to get all Conversations where user is owner or receiver or sender, I am doing it like so

[79] pry(main)> Conversation.joins(:messages).where("messages.receiver_id = ? OR messages.sender_id = ? OR conversations.user_id = ?", 2, 2, 2).count
(0.5ms) SELECT COUNT(*) FROM "conversations" INNER JOIN "messages" ON "messages"."conversation_id" = "conversations"."id" WHERE (messages.receiver_id = 2 OR messages.sender_id = 2 OR conversations.user_id = 2)
=> 0


But
0
is incorrect, I have Conversation with
user_id: 2


[78] pry(main)> Conversation.where(user_id: 2).count
(0.4ms) SELECT COUNT(*) FROM "conversations" WHERE "conversations"."user_id" = $1 [["user_id", 2]]
=> 1


Any ideas whats wrong with my query?

Answer

QueryMethods#joins performs (read: “is translated to”) a LEFT INNER JOIN of tables.

The conversation and messages in the query should be linked themselves via "messages"."conversation_id" = "conversations"."id". Yours seem to be not linked.

Now the question is what do you expect to receive as a result. If you want everything, related to the user, despite whether it’s linked internally or not, use CROSS JOIN (I am not aware of Rails implementation, you might need to execute raw SQL query for that.)

In my opinion, you want to either fix your database, or use QueryMethods#left_outer_joins to “lazy” join, or just use two single queries.