[QB 11.0.2] Is It Possible to Delete() With Join()

Box Modules qb
1

In QB, is it possible to call delete() when you also have a join() statement?

I’d like to execute the following query:

DELETE post
FROM [post]
INNER JOIN [post_category]
    ON [post].[id] = [post_category].[postId]
WHERE [post_category].[id] = 5

I have the following QB statement:

qb.from( "post" )
	.join( "post_category", function( q ) {
		q.on( "post.id", "=", "post_category.postId" );
		q.where( "post_category.id", "=", 5 );
	} )
	.delete()

However, when I output the SQL QB generates, I get this:

DELETE FROM [post]

This feels a little dangerous and I would have expected a different result. Is this behavior intentional? If so, please let me know because I think it should be emphasized in the docs

If it’s a bug, I can create an issue on Github.

2

Deletes with joins are not possible in all grammars, turns out. MySQL and SQL Server support it. Postgres and Oracle do not. Postgres has an alternate syntax using CTEs that could maybe be adapted to it. Oracle and SQLite use subqueries for this.

So at the very least, we can have the default grammar get mad if you have configured joins when trying to delete. Then it can be an enhancement for MySQL and SQL Server later.

1 Like
3

Well, I went ahead and implemented it for MySQL and SQL Server while I was in there.

1 Like
4

That’s awesome! Thank you @elpete!