Hello,
I want to show the latest Message of a User on a Page.
If i try to order the Messages in my Table class:
->order([ ‘Messages.created’ => ‘DESC’ ])
it will order by the latest messages…
but if I also do
->distinct([‘Users3.id’ ])
it will remove the redundant Users but it will not show the LATEST message. It just shows one of the messages of the user…
Can anyone help? I only want to see the latest message of a user
“Window Functions
¶”
this looks similiar to my problem but i use a Table Class with conditions etc… so it cant help me…
Are you wanting just a single result (i.e. message) that utilizes first()? If not, can you provide so more details.
$latestMessage = TableRegistry::getTableLocator()->get('Messages')->find()
->where(
[
'Messages.user_id' => $userId,
]
)
->order(
[
'Messages.created' => 'DESC',
]
)
->first();
no each user has many messages but i only want to see the latest one.
Thats what i do in my UsersTable:
public function getMessages($userid,$mailboxid)
{
$friends = $this->find();
$friends->select(
[
'Users3.email',
'Users3.id',
'Messages.created',
'Messages.id',
'Messages.user_id',
'Messages.body',
]
)
->where(['Mailboxes.user_id' => $userid , 'Messages.mailbox_id' => $mailboxid ])
->order([ 'Messages.created' => 'DESC' ])
->join([
'table' => 'mailboxes',
'alias' => 'Mailboxes',
'type' => 'LEFT',
'conditions' => [
'Mailboxes.user_id = Users.id'
],
])
->join([
'table' => 'messages',
'alias' => 'Messages',
'type' => 'LEFT',
'conditions' => [
'Messages.mailbox_id = Mailboxes.id'
],
])
->join([
'table' => 'users',
'alias' => 'Users3',
'type' => 'LEFT',
'conditions' => [
'Messages.user_id = Users3.id'
],
])
;
return $friends;
}
If i do : ->distinct([‘Users3.id’, ‘Messages.user_id’, ]), it will remove the redundant ids but it will not show the newest one of a user… I only want to see the newest one on many users.
any idea?
this is what a “message” looks like:
{ “Users3”: { “email”: “test1@test.com”, “id”: “7” }, “Messages”: { “created”: “2021-12-21 18:58:39”, “id”: “31”, “user_id”: “7”, “body”: “test data conversation” } }
if i change the select sequence:
$friends->select(
[
‘Messages.created’,
‘Messages.id’,
‘Messages.user_id’,
‘Messages.body’,
‘Users3.email’,
‘Users3.id’,
it will look like:
{ “Messages”: { “created”: “2021-12-20 18:58:39”, “id”: “30”, “user_id”: “7”, “body”: “test message” }, “Users3”: { “email”: “test1@test.com”, “id”: “7” }, }
but it will still not show only the LATEST message of an User…
I’m only replying off the top of my head, so can’t provide the correct Cake syntax, but couldn’t you ORDER BY “created” DESC - then put a count of 1 in the SELECT so it just pulls the 1 record, which will be the last one created?
there are many users with many messages. i think what you recommend will only show the latest message …
but i want to show the latest message of any user
GROUP BY? Not sure how count behaves with that though.
not working:
‘messages’ => [
(int) 0 => object(App\Model\Entity\User) id:0 {
‘Messages’ => [>]
‘Users3’ => [>]
if i count messages for example he will count all message and put it into the first messages object… thats not what i need/wrong… above the sample message that i provided there is “(int) X => object(App\Model\Entity\User) id:X”
‘messages’ => [
(int) 0 => object(App\Model\Entity\User) id:0 {
‘Messages’ => [
‘created’ => ‘2021-12-20 18:37:06’,
‘id’ => ‘29’,
‘user_id’ => ‘6’,
‘body’ => ‘thats my message’,
]
‘Users3’ => [ ]
‘[new]’ => false
‘[accessible]’ => [ ]
‘[dirty]’ => [ ]
‘[original]’ => [ ]
‘[virtual]’ => [ ]
‘[hasErrors]’ => false
‘[errors]’ => [ ]
‘[invalid]’ => [ ]
‘[repository]’ => ‘Users’
},
(int) 1 => object(App\Model\Entity\User) id:1 { },
(int) 2 => object(App\Model\Entity\User) id:2 { },
(int) 3 => object(App\Model\Entity\User) id:3 { },
(int) 4 => object(App\Model\Entity\User) id:4 { },
(int) 5 => object(App\Model\Entity\User) id:5 { },
(int) 6 => object(App\Model\Entity\User) id:6 { },
(int) 7 => object(App\Model\Entity\User) id:7 { },
(int) 8 => object(App\Model\Entity\User) id:8 { },
(int) 9 => object(App\Model\Entity\User) id:9 { },
(int) 10 => object(App\Model\Entity\User) id:10 { },
(int) 11 => object(App\Model\Entity\User) id:11 { },
(int) 12 => object(App\Model\Entity\User) id:12 { },
],
]
If I am understanding correctly, you could fake a hasOne association.
class UsersTable extends Table
{
public function initialize(array $config): void
{
parent::initialize($config);
$this->setTable('users');
$this->hasOne('LatestMessage')
->setClassName('App.Messages')
->setForeignKey('user_id')
->setStrategy('select')
->setProperty('latest_message');
$this->hasMany('Messages')
->setClassName('App.Messages')
->setProperty('messages');
}
}
Then contain the alias.
$users = TableRegistry::getTableLocator()->get('Users')->find()
->contain(
[
'LatestMessage',
]
)
->toArray();
Ideally, you’d probably want to have a true belongsTo association with a foreign key on your users table that points to the users newest message. That can easily be done with a CakePHP Behavior.
How about putting a last_message field in your users table, and have SQL triggers update it whenever a new message is created / edited / deleted? Have the SQL engine do all the heavy lifting. It’ll be fast, automatic, with just the slightest hint of redundancy - unless you critically care about database normalisation. This would be the least amount of coding, simple table structure, and the fastest in fetching the data.
Failing that work out the complete solution in SQL, so you’re not having a stack of data getting fed into Cake which then gets dropped anyway. Once you got that SQL convert it into Cake for its objects. But personally, I’d be putting last_message (and date&time if needed) in the user table, slight duplication for speed and sanity - or failing those fields a foreign key to the last record - which is a variation on Brent’s solution.
and why will it show the latest one?
I am legally obliged to leave this here:
Apologies I forgot the sort in the example. Either way, I do not like suggestion. I think overall, the least expensive option is to create a behavior to update a foreign key on the User model or have something quick and dirty like the following on your Messages table.
public function afterSave(Event $event, Entity $message, \ArrayObject $options)
{
if ($message->isNew()) {
$this->Users->updateAll(
[
'latest_message_id' => $message->id,
],
[
'id' => $message->user_id,
]
);
}
}
$this->belongsTo('LatestMessage')
->setClassName('App.Messages')
->setForeignKey('latest_message_id')
->setProperty('latest_message');
CREATE TABLE `users` (
`id` int NOT NULL AUTO_INCREMENT,
`email` varchar(256) NOT NULL,
`first_name` varchar(20) DEFAULT NULL,
`last_name` varchar(20) DEFAULT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`latest_message_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_uindex` (`email`),
UNIQUE KEY `users_id_uindex` (`id`),
KEY `users_messages_id_fk` (`latest_message_id`),
CONSTRAINT `users_messages_id_fk` FOREIGN KEY (`latest_message_id`) REFERENCES `messages` (`id`) ON DELETE SET NULL
) DEFAULT CHARSET=utf8mb4;
CREATE TABLE `messages` (
`id` int NOT NULL AUTO_INCREMENT,
`user_id` int NOT NULL,
`subject` varchar(20) NOT NULL,
`created` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `messages_id_uindex` (`id`),
KEY `messages_users_id_fk` (`user_id`),
CONSTRAINT `messages_users_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARSET=utf8mb4;
Can messages be deleted?
no you do some different things!
i tried this now:
$this->loadModel(‘Messages’);
$query = $this->Messages
->find()
->where([‘Messages.mailbox_id’ => $mailboxid ])
->select([‘Messages.id’, ‘Messages.user_id’])
->order([‘Messages.id’ => ‘DESC’])
->distinct(‘Messages.user_id’)
;
but it still does the same it didnt remove the redundant entries correct and it will not show the latest messages! The mailbox contains many Messages of different Users but i want to see only the latest Message of any User…
I recommend to get it working in pure SQL first - in phpMyAdmin or Adminer or your favourite database tool. Once you have that view showing each user and their last message, then engineer that query back into CakePHP.
its actually only a mailbox and messages… the user_id is without a user table that i join/contain…
i need all different Messages.user_id including their newest message inside a mailbox…
i wonder that i can order the messages by their id correctly but why the distinct command will not leave the latest ones… it will just show random messages
Here are 3 separate SQLs, all should work or be very close (depending on field names).
SELECT a.*
FROM Messages a
INNER JOIN (
SELECT user_id, MAX(created) created
FROM Messages
GROUP BY user_id
) b
ON a.user_id = b.user_id
AND a.created = b.created
SELECT a.*
FROM Messages a
LEFT OUTER JOIN Messages b
ON a.user_id = b.user_id
AND a.created < b.created
WHERE b.user_id IS NULL
SELECT *
FROM Messages
WHERE (user_id, created)
IN (
SELECT user_id, MAX(created)
FROM Messages
GROUP BY user_id
)
Again test in your SQL engine interface - you may need to expand them if you want to pull in the users table / mailbox or whatever. Once you’re happy with the result use the Cake query builder to get the data.