SqlBuilder: delete doesn't parse joins

[adaamz]

Hi, example:

$db->table('table')
    ->where(':another_table.column', 5)
    ->delete();

Makes SQL like

DELETE FROM table WHERE :´another_table´.´column´ = 5.

[zaxxx]

Same for update

$db->table('movement')->where(':movement_tag.tag', [5])->update(['volume' => 50);

generates

UPDATE `movement` SET `volume`=50 WHERE (:`movement_tag`.`tag` IN ('5'))

instead of

UPDATE `movement` 
LEFT JOIN `movement_tag` ON `movement`.`id` = `movement_tag`.`movement` 
SET `volume`=50 
WHERE (`movement_tag`.`tag` IN ('5'))

[adaamz]

@zaxxx Every db system has individual solution for update/delete join. So maybe there should be some method in MySqlDriver /MsSqlDriver/OciDriver/PgSqlDriver... which will deal with joining problem.

[zaxxx]

Soon (monday hopefully) I'll make a quick PR, I'll just hardcode those joins into buildUpdateQuery&buildDeleteQuery methods and maybe wrap it in "if is mysql" condition. Very far from optimal solution, but all I need is get it working in mysql ASAP, because it is a very useful feature. I can't believe there's such a low demand for this.

[Unlink]

What if

$db->table('movement')->where(':movement_tag.tag', [5])->update(['volume' => 50);

genrates

UPDATE `movement` SET `volume`=50 WHERE `movement_id` IN (SELECT `movement_id` FROM `movement` LEFT JOIN `movement_tag` ON `movement`.`id` = `movement_tag`.`movement` 
WHERE (`movement_tag`.`tag` IN ('5')))

so use subquery, which should be same across different db systems?

[adaamz]

@Unlink That's ok but think about multi column primary key and multiple joins.

$db->table('abc')->where('def.aaa', [1,2,3])->where(':ghj:klm:nop.bbb', 'abcd')->update(['ccc' => 5]);

[Unlink]

@CZechBoY we can stil replace IN with EXISTS What do you means with

multiple joins

can you explain possible problem with it? Only one limitation is, that you can't make dependant update. Sth. like this

$db->table('abc')->where('sth.aaa', [1,2,3])->update(['ccc' => new SqlLiteral('sth.bbb + 1']);

But it looks weird, so i don't know.

[adaamz]

@Unlink Multiple joins as I mentioned (:d:e:f:g.abc) - for that EXISTS should be ok. That SqlLiteral thing is pretty good!