Skip to content
Advertisement

ActiveRecord having count query condition

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)

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement