Kohana 3 cross-table update using Query builder

Question

What is the proper way to construct a cross-table update in Kohana 3 using the DB query builder?

Currently I am just using a DB::expr but I know the query builder is smarter than that.

// update record
$rows_updated = DB::update(DB::expr('user_list_permissions INNER JOIN users ON user_list_permissions.user_id = users.id'))
->set($params)
->where('user_list_permissions.id', '=', $user_list_permission_id)
->where('users.account_id', '=', $this->account_id)
->execute();

And yes of course I tried using the "join" method like when building SELECT queries, but I receive an error:

ErrorException [ 1 ]: Call to undefined method Database_Query_Builder_Update::join()

Answer 1

So you are using the expression to do the join, it is possible to use the built in 'join' function on the 'on' function to achieve this behavior.

So in your example it would look something like:

$rows_updated = DB::update('user_list_permissions')
->join('users','INNER')
->on('user_list_permissions.user_id','=','users.id')
->set($params)
->where('user_list_permissions.id', '=', $user_list_permission_id)
->where('users.account_id', '=', $this->account_id)
->execute();

There isn't much on it but the docs do have a little bit at http://kohanaframework.org/3.2/guide/database/query/builder#joins

Answer 2

It's an old post but just to keep the record of my experiences in Kohana.

If you are using MySQL, it lets you to make the cross-table update avoiding the use of join as follow:

UPDATE table1, table2
SET table1.some_field = 'some_value'
WHERE table1.foreign_key = table2.primary_key AND table2.other_field = 'other_value' 

Note that condition table1.foreign_key = table2.primary_key is the same you used in ON clause with JOIN. So you can write a cross-table update in Kohana follow that pattern avoiding the JOIN:

$rows_updated = DB::update(DB::expr('user_list_permissions, users'))
->set($params)
->where('user_list_permissions.user_id', '=', DB::expr('users.id'))
->where('user_list_permissions.id', '=', $user_list_permission_id)
->where('users.account_id', '=', $this->account_id)
->execute();