Rails activerecord query counting records via has_many :through

rubyonrails-talk
1

A character has_many :conversations, through: :chats, and each conversation has_many :characters, through: :chats.

character.rb

has_many :chats, foreign_key: "character_id", dependent: :destroy
has_many :conversations, through: :chats, source: :conversation
has_many :messages

``

conversation.rb

has_many :chats, foreign_key: "conversation_id", dependent: :destroy
has_many :characters, through: :chats, source: :character
has_many :messages

``

chat.rb

belongs_to :character
belongs_to :conversation

``

messages_controller.rb

def create
  @sender    = Character.find_by(callsign: params[:callsign])
  @recipient = Character.find_by(callsign: params[:recipient])
  # 1
  @senderConversations = @sender.conversations
  # 2
  @senderAndRecipientConversations = @senderConversations.joins(:characters).where(characters: {id: @recipient.id}) # works fine up to here
  # 3
  @conversationAssociation = @senderAndRecipientConversations.joins(:characters).group('conversations.id').having('count(characters.id) = ?', 2) # problem line!
  @conversation = @conversationAssociation.first
  if(@conversation.nil?)
    @conversation = @sender.conversations.create
    @recipient.conversations << @conversation
  end
  @message = @sender.messages.build(message_params.merge(:conversation => @conversation))
  if @message.save
    @conversation.messages << @message
    respond_to do |format|
      format.html do
      end
      format.js do
      end
    end
  else
    redirect_to request.referrer || root_url
  end
end

``

All conversations must have at least two characters, and some conversations may have more than two characters (group chats).

I’m trying to construct a 3-step query that first finds all the @sender’s conversations, then selects from these the ones that also involve the @recipient, and finally chooses the conversation which only involves @sender and @recipient and no other characters.

The first two steps are working fine. I can’t get the code for the third step right. The best I’ve managed is:

@senderAndRecipientConversations.joins(:characters).group('conversations.id').having('count(characters.id) = ?', 2)

``

What’s wrong with this code?