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.
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
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”
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.
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.
MessagesTable
public function afterSave(Event $event, Entity $message, \ArrayObject $options)
{
if ($message->isNew()) {
$this->Users->updateAll(
[
'latest_message_id' => $message->id,
],
[
'id' => $message->user_id,
]
);
}
}
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.