Support Joins and Using syntax for PostgreSQL Delete Statement (#4591)

[OlivierCavadenti]

• PostgreSQL dont support Join in Delete (https://www.postgresqltutorial.com/postgresql-delete-join/) so I implements the support of using to do the same thing in two king : implicit and explicit methods.

So implicit method, with usual join methods work:

 qb()
    .del()
    .from('users')
    .join('photos', 'photos.id', 'users.id')
    .where({ 'user.email': 'mock@example.com' })

and will generate the query with 'using' and 'where' to work in PostgreSQL.
It will general the same query that explicit form:

 qb()
  .del()
  .from('users')
  .using('photos')
  .whereRaw('photos.id = users.id')
  .where({ 'user.email': 'mock@example.com'})

• I dont want to go in intrusive and complex implementation (no new kind of joins...) so I just add small using method in querybuilder and make all the specific logic in PG files. Finally I implements both implicit joins conversion and explicit form.
• I choose to make a specific implement of Delete that dont allow joins in Delete and throw an error when user try to make the joins with Delete statement => "Joins with delete query isn't valid in PostgreSQL, use 'using' with 'where' functions instead." Specific implement of Delete for PostgreSQL that convert joins in 'using' + 'wheres'
• Fixes pg: incorrect "delete + join" query since 0.95.8 #4591

[kibertoad]

converting joins to using transparently is not feasible?

[OlivierCavadenti]

You mean use the joins logic by add 'using' joins types with the 'using()' syntax ?
I think about that but with that implementation I cant throw an error here.
Also we have a join with using syntax inside and I quite afraid about some misunderstand.

[kibertoad]

I'm still on the fence about this, TBH. Considering that many multi-db systems use knex specifically to abstract away db differences, transforming joins to using here would allow them to avoid having some conditional logic. Is it possible to always transform join into using, or you see certain case where that would fail and user would be forced to use using explicitly anyway?

[OlivierCavadenti]

I see the argument. I start on 'using' function because of an argument in the original PR about avoid silent converting operations. But in fact, we always do "silent" operations in Knex, especially with oracle, when converting limit/offset functions...
I will rework it.

[kibertoad]

I wonder if we should also keep using syntax for those who want to be explicit.

[OlivierCavadenti]

I push with implicit joins to 'using' + 'where's syntax and I also keep the actual using syntax too.

[t1a2l]

@elhigu - are you going to review this?

[t1a2l]

ok i just added the fix to local project.

[kibertoad]

Would also be helpful to update existing Delete with join tests, so that they would no longer skip PostgreSQL. They also currently miss assertions that only correct data is deleted, which would be great to add too.

[OlivierCavadenti]

Would also be helpful to update existing Delete with join tests, so that they would no longer skip PostgreSQL. They also currently miss assertions that only correct data is deleted, which would be great to add too.

I push with more integration tests + one unit test (mixed joins + using notation).
By the way I made fixes on querycompiler ('where' value as column value for ex).
I see that my example in this PR + unit test are not good (cannot use id columns with 'where' use 'whereRaw' instead).

[kibertoad]

Outstanding work on this one!