Sort Chats in Chat App by newest message

Hi,

I created a simple chat application, which basically consists of 3 Tables:

  • Users (each user belongs to many chats)
  • Chats (each chat has 2 users and many messages)
  • Messages (each message belongs to a chat)

Now I would like to find all chats

  • which belong to a certain user (works)
  • and sort them by the chat with the newest message (need help)

Your help on the sorting is highly appreciated!

Below my “find” query:

$query = $this->Messages->Chats->find()
	->contain([
		'ChatPartners1', 
		'ChatPartners2', 
		'Messages'
		])
	->where(['ChatPartners1.id' => $this->Auth->user('id')])
	->orWhere(['ChatPartners2.id' => $this->Auth->user('id')]);

Below my table setup (shortened to the important lines):

class ChatsTable extends Table
{
    public function initialize(array $config)
    {
	...

        $this->belongsTo('ChatPartners1', [
		'className' => 'Users',
            	'foreignKey' => 'chatpartner1_id',
            	'joinType' => 'INNER'
        ]);
        $this->belongsTo('ChatPartners2', [
		'className' => 'Users',
            	'foreignKey' => 'chatpartner2_id',
            	'joinType' => 'INNER'
        ]);
	$this->hasMany('Messages', [
            'foreignKey' => 'chat_id',
            'joinType' => 'INNER'
        ]);		
    }
...
}

class MessagesTable extends Table
{
    public function initialize(array $config)
    {
	...
        $this->belongsTo('Chats', [
            	'foreignKey' => 'chat_id',
            	'joinType' => 'INNER'
        ]);
    }
...
}

I would move the query to the ChatsTable.php.
As orWhere is deprecated I would change it also.

Something like this:

$query = $this->find()
	->contain([
		'ChatPartners1', 
		'ChatPartners2', 
		'Messages' => ['sort' => ['Messages.time']]
		])
	->where(['OR' => 
         ['ChatPartners1.id' => $this->Auth->user('id')],
	     ['ChatPartners2.id' => $this->Auth->user('id')]
       ]);

https://book.cakephp.org/3.0/en/orm/retrieving-data-and-resultsets.html#sorting-contained-associations

1 Like

Hi rrd,

thanks for the help. However, either I didn’t implement your reply correctly or I wasn’t presice enough:

The sort you suggest, will put the messages into the right order in one chat. However, I want to have the chats sorted: the one with the latest message should be on top (similar to whatsapp or facebook).

Thanks,
Christian

Ah I see. That is a little bit trickier.

in MessagesTable.php you would need to select the latest messages of each chats. Here is a MySQL 5.7 compatible sample.

public function findLatest(Query $query, array $options)
    {
        return $query->innerJoin(
            [
                'latest' => $query->getConnection()->newQuery()
                    ->select(
                        [
                            'chat_id' => 'Chats.id',
                            'latest' => $query->func()->max('Messages.created')

                        ]
                    )
                    ->from(['Messages' => 'messages'])
                    ->group('Messages.chat_id')
            ],
            [
                $this->getAlias() . '.chat_id' => new IdentifierExpression('latest.chat_id'),
                $this->getAlias() . '.created' => new IdentifierExpression('latest.latest')
            ]
        );

After this you need to join this into a chat query in ChatsTable.php

public function findCurrent(Query $query, array $options)
    {
        return $query->select(['Chats.id', 'Messages.created', 'Messages.message'])
            ->innerJoin(
                ['LatestMessage' => $this->Messages->find('latest', $options)],
                ['Chats.id = LatestMessage.Messages__chat_id']
            );
    }

Perhaps this is for you to help you start to think about it. There is a good chance that you should tweak a little on both queries.