Rails activerecord query counting records via has_many :through

13 views
Skip to first unread message

Bazley

unread,
Nov 20, 2015, 7:38:19 AM11/20/15
to Ruby on Rails: Talk
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?
Reply all
Reply to author
Forward
0 new messages