1

I have three tables in the postgres.

  • ChatMessages
  • ChatReactions
  • ChatReactionTypes

The association looks like this : enter image description here

I am trying to get the user reaction list message wise. Like this example. This modal is opened per message wise :

enter image description here

I have tried the following query in CakePHP query builder :

$this->ChatMessages->find()
    ->contain([
        'ChatReactions.ChatReactionTypes',
    ])
    ->contain('ChatReactions', function($q) {
        $q->select([
                'chat_message_id',
                'chat_reaction_type_id',
                'count' => $q->func()->count('chat_reaction_type_id')
        ])
    ->innerJoinWith('ChatReactionTypes')
    ->group(['chat_reaction_type_id', 'chat_message_id']);
    
        return $q;
    })
    ->order([
        'ChatMessages.created' => 'ASC',
    ])
    ->all()
;

This is the result :

enter image description here

The chat_reactions (marked red in the image above) object is grouped by reaction_type_id. I want to have all the users associated with that id inside chat_reactions.

Sabbir Ahmed
  • 23
  • 7
  • I think you need to drop the grouping and counting at the database level, as that's going to give you just one record per type. Instead, look at using `groupBy` on the record set after you have loaded it. – Greg Schmidt Jun 22 '21 at 15:06

0 Answers0