Bulk updating entites with field from an other table as condition

Hello there!

I have two tables which have a relationship. Now in one Model I want to add a bulkUpdate-function. As a condition, I need the value of a field of the other table. How is the correct syntax for that? Here is my code so far…

$this->updateAll([ 'isRead' => true, 'readTime' => Time::now(), ], [ 'user_id' => $user_id, 'ChatroomsMessages.id' => $chatroom_id, //<- **this is the guy.... tablename: ChatroomsMessages** 'isRead' => false, ]);

I tried several formats, but each time Cake/MySQL says: unkown column…

Thanks in advance!


I don’t wanna be impatient, but I need a solution for this. I read the cookbook and googled around. I didn’t find anything for this problem. Is it not well explained?

Or is the only possible way to use the query manager and build my own query?


updateAll() does not take into account any associations, joins or table aliases. You’ll need to use the alternative method shown at the end of http://book.cakephp.org/3.0/en/orm/saving-data.html#Cake\ORM\Table::updateAll

Thanks for your answer.

I did so, but I don’t know how to add the join table (named: ChatroomsMessages) to that query. I tried contain() and also tried from().

$this->query() ->update() ->from('ChatroomsMessages') ->set([ 'isRead' => true, 'readTime' => Time::now(), ]) ->where([ 'user_id' => $user_id, 'ChatroomsMessages.chatroom_id' => $chatroom_id, 'isRead' => false, ]) ->execute();

But the related table won’t be added to the query. What am I missing? Thanks.

Hello, the problem still exists. I just can’t figure out, how to join the tables in an update-query.

Please save my day :wink:

I finally found a solution. Now I’m asking you: is this solution a good one / could it be shorter/faster/better? This is my working code so far…

public function readAllUnread($user_id, $chatroom_id) { $this->query() ->update('chatrooms_messages_stati s, chatrooms_messages m') ->set([ 's.isRead' => true, 's.readTime' => Time::now()->format('Y-m-d H:i:s'), ]) ->where([ 's.user_id' => $user_id, 'm.chatroom_id' => $chatroom_id, 's.isRead' => false, ]) ->andWhere('s.message_id = m.id') ->execute(); }

PS: Why is my code displayed unformatted? In the preview it’s okay before posting it…