cees
November 14, 2017, 1:16am
1
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'
]);
}
...
}
rrd
November 14, 2017, 2:15pm
2
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
cees
November 14, 2017, 8:30pm
3
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
rrd
November 15, 2017, 1:52pm
4
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.