fix: relation join soft delete SQL generate

[danclive]

When I used "Table relationships" to query, I found that the output was incorrect, and checked the logs to find that there was a problem generating the SQL statement in the soft delete position, so I fixed it.

I refer to the soft delete handling code in the query_base.go file.

https://github.com/uptrace/bun/blob/28915f89b0e864ee7c43782fe3234f8d65831e00/query_base.go#L806-L822

Problem Reproduction

The database I use is PostgreSQL, and the model I defined is as follows:

type User struct {
    bun.BaseModel `bun:"user"`
    ID            string    `bun:"type:TEXT,pk" json:"id"`
    Name          string    `bun:"name,type:TEXT" json:"name"`
    Deleted       time.Time `bun:"deleted,soft_delete" json:"-"`
    Created       time.Time `bun:"created" json:"created"`
    Updated       time.Time `bun:"updated" json:"updated"`
}

type UserToGroup struct {
    bun.BaseModel `bun:"user_to_group"`
    ID            string    `bun:"type:TEXT,pk" json:"id"`
    UserID        string    `bun:"user_id,type:TEXT"`
    User          *User     `bun:"rel:belongs-to,join:user_id=id"`
    Deleted       time.Time `bun:"deleted,soft_delete" json:"-"`
    Created       time.Time `bun:"created" json:"created"`
    Updated       time.Time `bun:"updated" json:"updated"`
}

The query statement I use is:

item := UserToGroup{
    ID: "01832d6de013d76b41bd51c4",
}

db.NewSelect().Model(&item).Relation("User").WherePK().Scan(ctx)

The generated SQL statement is:

SELECT "user_to_group"."id", "user_to_group"."user_id", "user_to_group"."deleted", "user_to_group"."created", "user_to_group"."updated", "user"."id" AS "user__id", "user"."name" AS "user__name", "user"."deleted" AS "user__deleted", "user"."created" AS "user__created", "user"."updated" AS "user__updated" FROM "user_to_group" LEFT JOIN "user" AS "user" ON ("user"."id" = "user_to_group"."user_id") AND "user"."deleted" IS NULL WHERE "user_to_group"."deleted" = '0001-01-01 00:00:00+00:00' AND ("user_to_group"."id" = '01832d6de013d76b41bd51c4')

As you can see, I did not use the nullzero option, but the generated SQL statement is AND "user"."deleted" IS NULL.

The SQL statement generated after the fix is as follows:

SELECT "user_to_group"."id", "user_to_group"."user_id", "user_to_group"."deleted", "user_to_group"."created", "user_to_group"."updated", "user"."id" AS "user__id", "user"."name" AS "user__name", "user"."deleted" AS "user__deleted", "user"."created" AS "user__created", "user"."updated" AS "user__updated" FROM "user_to_group" LEFT JOIN "user" AS "user" ON ("user"."id" = "user_to_group"."user_id") AND "user"."deleted" = '0001-01-01 00:00:00+00:00' WHERE "user_to_group"."deleted" = '0001-01-01 00:00:00+00:00' AND ("user_to_group"."id" = '01832d6de013d76b41bd51c4')

[vmihailenco]

Hey,

Thanks for the fix - it looks good.

Do you think it is possible to add a test here? This should give a hint how to run tests.

[danclive]

Ok, I've added the test, I don't know if it fits.

[vmihailenco]

Thank you!