Bulk updating entites with field from an other table as condition

Need Help
1

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!

2

Hello,

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?

Thanks

3

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

4

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.

5

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:

6

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…

Thanks!