QueryBuilder - Does not support JOIN in UPDATE

[najdanovicivan]

I see in database classes that there's no support for update with joins.

More info here:
MYSQL - https://www.mysqltutorial.org/mysql-update-join/
PostgreSQL - Does Not Support It
SQLite - Does Not Support It
MSSQL - https://www.sqlshack.com/an-overview-of-sql-server-update-join/

The same feature lack on CI3 as well bcit-ci/CodeIgniter#5941

[otrelin]

QueryBuilder provides the ability to work with any database. If this feature is not supported in all databases, then QueryBuilder cannot provide it for everyone. Any time You can switch from one database to another and your code still working.
You can use custom queries instead.

[nowackipawel]

I was into Builder lately and in fact I saw exceptions from this rule (driver specific exceptions) :). But I have to say that IMHO join in update is not as common as it require it to be implemented as long as we could use query for this. :/

…

On Sun, Apr 5, 2020, 12:21 Oleksandr Trelin ***@***.***> wrote: QueryBuilder provides the ability to work with any database. If this feature is not supported in all databases, then QueryBuilder cannot provide it for everyone. Any time You can switch from one database to another and your code still working. You can use custom queries instead. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#2799 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACAGMUQYAL46BJ7UM6IE3ULRLBLT5ANCNFSM4L6CEOZQ> .

[najdanovicivan]

Regadring the CI4 it should not be that hard to implement as it only needs to be implemented in MySQL driver. I've created the issue for reference here. I will work on that and submit PR

[lonnieezell]

The same thing can be accomplished with Postgres so a way to do that would also have to be implemented. Additionally, a way of dealing with it in SQLite would need to be handled if at all possible since SQLite is often used for running tests even when the site would use MySQL or Postgres. So, it's not as simple as it seems.

Additionally, the goal with the Query Builder is to provide a consistent API across all database drivers that we support. We avoid adding any engine-specific functions to the Builder, since you can still hand-write queries for anything more specific needed.