How to use joins in delete using gorm?

Question

I have 3 tables, users, roles, and role_users. I want to delete users that have roles.role_group IN ('admin') and id IN ('1fadec77-e186-4ea9-962c-f4c27b0805f7'), but I got error "ERROR: missing FROM-clause entry for table "roles" (SQLSTATE 42P01)", and I'm using postgresql as the database

I'm trying to use this code, but I got error "ERROR: missing FROM-clause entry for table "roles" (SQLSTATE 42P01)"

This is the code that I use

func (ur *UserRepository) Delete(db *gorm.DB, payload *requests.Delete, softDelete bool) error {
    db = db.
        Joins("JOIN role_users ON role_users.user_id = users.id").
        Joins("JOIN roles ON roles.id = role_users.role_id")
    return db.Transaction(func(tx *gorm.DB) error {
        if softDelete {
            if err := tx.
                Where("id IN (?)", payload.Ids).
                Where("roles.role_group IN (?)", payload.RoleGroup).
                Delete(&models.Users{}).Error; err != nil {
                return err
            }
        } else {
            if err := tx.
                Unscoped().
                Where("id IN (?)", payload.Ids).
                Where("roles.role_group IN (?)", payload.RoleGroup).
                Delete(&models.Users{}).Error; err != nil {
                return err
            }
        }

        return nil
    })
}

This is the payload

{
    "ids": [
        "1fadec77-e186-4ea9-962c-f4c27b0805f7"
    ],
    "role_group": ["admin"]
}

I'm expecting the users that have id of 1fadec77-e186-4ea9-962c-f4c27b0805f7 and role group of admin to be deleted and soft deleted if softDelete = true

Answer 1

You cannot use JOIN in DELETE according to PostgreSQL documentation:

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] 
]

Easiest way to deal with it is to use something like:

DELETE FROM user WHERE id IN (SELECT id FROM user JOIN ... WHERE ...)