I have 2 models : Conversation
and Message
I need to retrieve all conversations that belongs to a Brand
and where there are at least one message from the brand and one message from an influencer
This is my query :
Conversation.includes(:messages) .joins(:messages) .group("conversations.id, messages.id") .where("conversations.brand_id = ?", brand_id) .having("COUNT(messages.authorable_type = 'Influencer') > 0 AND COUNT(messages.authorable_type = 'BrandMember') > 0") .limit(10)
But i get conversations that doesn’t have messages from both… What am i doing wrong please ?
Advertisement
Answer
The problem lays in COUNT
expressions.
Note that COUNT
counts all NON-NULL values. In your case you put to COUNT
some conditional expression which evaluates to TRUE
or FALSE
which are not NULL
.
Laziest solution would be look like this:
COUNT(messages.authorable_type = 'Influencer' OR NULL)