Join() being ignored when using Delete()

[phasetri]

Hello. I am wanting to create a SQL statement that involves a DELETE and JOIN keyword. Here is a small example:

DELETE 
FROM role_refs 
JOIN roles as role ON role.id = id 
WHERE role.name = 'Admin'

# role_ref and role is a many-to-one relationship; a role_ref can be matched with a single role at most.

Using go-pg, I'm using this code to mirror the above statement:

Model((*RoleRef)(nil)).
    Join(`JOIN roles as role ON role.id = id`).
    Where(`role.name = ?`, "Admin").
    Delete()

The table and column names don't really matter, but rather it's using both Join() and Delete(). However, when I execute the go-pg code, the assembled SQL statement is missing the JOIN statement completely:

DELETE 
FROM role_refs 
WHERE role.name = 'Admin'

I also tried replacing Join() with Relation() but there is no JOIN keyword in the resulting SQL either.

Is this a bug?

[elliotcourant]

I believe you also need to use JoinOn in addition to the Join function and move the ON clause to the JoinOn

[phasetri]

Tried it with JoinOn this time, but unfortunately it's yielding the same issue.

Model((*RoleRef)(nil)).
    Join(`JOIN roles as role`).
    JoinOn(`role.id = id`).
    Where(`role.name = ?`, "Admin").
    Delete()

[phasetri]

Oh actually, I read that Postgres doesn't support DELTETE with JOIN. Rather, USING or IN should be used instead.

https://stackoverflow.com/questions/13364855/error-postgresql-delete-with-inner-join

I will close this issue.