Gorm Where with struct after Joins uses wrong table

GORM Playground Link

https://github.com/go-gorm/playground/pull/454

Description

Instead of users.age, pets.age is used because the table is derived from Find, and not from the Where clause that receives a struct

Explain your user case and expected results

var pets []*Pet

err := DB.
    Joins("INNER JOIN users ON users.id = pets.user_id").
    Where(&Pet{Name: "a"}).
    Where(&User{Age: 10}).
    Find(&pets).Error

if err != nil {
    t.Fatalf("err: %v", err)
}

Currently, the generated query is: SELECT `pets`.`id`,`pets`.`created_at`,`pets`.`updated_at`,`pets`.`deleted_at`,`pets`.`user_id`,`pets`.`name` FROM `pets` INNER JOIN users ON users.id = pets.user_id WHERE `pets`.`name` = "a" AND `pets`.`age` = 10 AND `pets`.`deleted_at` IS NULL

main_test.go:21: err: no such column: pets.age

The expected query is: SELECT `pets`.`id`,`pets`.`created_at`,`pets`.`updated_at`,`pets`.`deleted_at`,`pets`.`user_id`,`pets`.`name` FROM `pets` INNER JOIN users ON users.id = pets.user_id WHERE `pets`.`name` = "a" AND `users`.`age` = 10 AND `pets`.`deleted_at` IS NULL

Note that in the first query pets.age is used instead of users.age.

In statement.go in: func (stmt *Statement) BuildCondition(query interface{}, args ...interface{}) []clause.Expression the test reaches the case reflect.Struct: section.

In it, it reaches conds = append(conds, clause.Eq{Column: clause.Column{Table: clause.CurrentTable, Name: field.DBName}, Value: v}). Later on, clause.CurrentTable is converted to pets because of the Find clause.

Note that in the exact same place, field.Schema.Table is equal to users. i.e. if we switch: conds = append(conds, clause.Eq{Column: clause.Column{Table: clause.CurrentTable, Name: field.DBName}, Value: v})to: conds = append(conds, clause.Eq{Column: clause.Column{Table: field.Schema.Table, Name: field.DBName}, Value: v}) it starts to work fine.

Same apply for other append(conds... statements in this function.

Comment From: a631807682

This seems like an unmanageable problem.

type User struct {
    gorm.Model
    ...
    FavoritePet  *Pet
    NastyPet  *Pet
    ...
}

When we use Where(&Pet{Name: "a"}), we can't determine whether the query is FavoritePet or NastyPet.

Comment From: samyonr

This seems like an unmanageable problem.

type User struct { gorm.Model ... FavoritePet *Pet NastyPet *Pet ... }

When we use Where(&Pet{Name: "a"}), we can't determine whether the query is FavoritePet or NastyPet.

In any of these cases, the query Where(&Pet{Name: "a"}) should output:

`pets`.`name` = "a"

Which it does and it works fine.

The problem is with the second Where clause: Where(&User{Age: 10}) which is translated into:

`pets`.`age` = 10

(the table pet has no age column, hence the main_test.go:21: err: no such column: pets.age error).

Instead, it should be translated into

`users`.`age` = 10

It worked fine for me when I changed Table: clause.CurrentTable to Table: field.Schema.Table in statement.go->BuildCondition

Comment From: jinzhu

Suggest to write it like:

db.Joins("Company", DB.Where(&Company{Alive: true})).Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;

Refer: https://gorm.io/docs/query.html#Joins-Preloading

Where accepts any structs to build the condition, and it is not necessary to use current model, so you can write query like:

db.Where(Search{Name: "jinzhu"}).Find(&users)

Use its table name to build the SQL will break this.

Comment From: mnussbaum

Suggest to write it like:

db.Joins("Company", DB.Where(&Company{Alive: true})).Find(&users)
// SELECT `users`.`id`,`users`.`name`,`users`.`age`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `users` LEFT JOIN `companies` AS `Company` ON `users`.`company_id` = `Company`.`id` AND `Company`.`alive` = true;

@jinzhu in the above example, what is DB? Is it a gorm type that isn't an instantiated DB? Or is it the same as the lowercase db?

Comment From: jjkoh95

Hi @jinzhu

db.Joins("Company", db.Where(&Company{Alive: true})).Find(&users)

select
    `users`.`id`,
    `users`.`name`,
    `users`.`age`,
    `Company`.`id` as `Company__id`,
    `Company`.`name` as `Company__name`
from
    `users`
left join `companies` as `Company` on
    `users`.`company_id` = `Company`.`id`
    and `Company`.`alive` = true;

This query still returns me rows that don't fulfill the condition (when Company.alive is not true, the related model is returned as empty instead)

---

I'm able to achieve using this

db.Joins("Company").Where(`"Company"."alive" = ?`, true).Find(&users)

select
    `users`.`id`,
    `users`.`name`,
    `users`.`age`,
    `Company`.`id` as `Company__id`,
    `Company`.`name` as `Company__name`
from
    `users`
left join `companies` as `Company` on
    `users`.`company_id` = `Company`.`id`
where 
    `Company`.`alive` = true;

While this is not a problem for most the times, there is a little consistency in the primary table (snake_case) and the joined table (PascalCase) which makes it slightly less elegant when i try to construct my where conditions, can you share what is the way to alias them? (eg, making the Joins table alias into snake_case instead). Or simply use TableName instead.