Join查询时,条件设置不对 #2044

Open
opened 2021-09-10 10:31:51 +00:00 by bzssm · 3 comments

定义以下两个表

type Model struct {
	ID        string    `xorm:"'id' UUID pk notnull" json:"id"`
	CreatedAt time.Time `xorm:"created index(created)" json:"created_at"`
	UpdatedAt time.Time `xorm:"updated" json:"updated_at"`
	CreatedBy string    `xorm:"'created_by' varchar(64)" json:"created_by"`
	UpdatedBy string    `xorm:"'updated_by' varchar(64)" json:"updated_by"`
}

type Tenant struct {
	Model            `xorm:"extends"`
	Name             string    `xorm:"'name' varchar(64) notnull " json:"name"`
	Email            string    `xorm:"'email' varchar(64)" json:"email"`
}

type Project struct {
	Model `xorm:"extends"`
	TenantID    string `xorm:"'tenant_id' notnull UUID " json:"tenant_id"`
	Name        string `xorm:"'name' varchar(64) notnull" json:"name"`
	Description string `xorm:"'description' text" json:"description"`
}

使用查询

	var tp struct {
		Tenant  `xorm:"extends"`
		Project `xorm:"extends"`
	}
	tp.Project.Model.ID = "asd"
	tp.Tenant.Name = "333"
	tp.Tenant.ID = "222"
	exists, err := db.Engine.Table(&Project{}).
		Join("INNER",
			db.Engine.TableName(&Tenant{}),
			fmt.Sprintf("%v.id=%v.tenant_id", db.Engine.TableName(&Tenant{}), db.Engine.TableName(&Project{}))).
		Get(&tp)

会生成以下sql:

[xorm] [info]  2021/09/10 18:29:15.376693 [SQL] SELECT * FROM `t_project` INNER JOIN `t_tenant` ON t_tenant.id=t_project.tenant_id WHERE `t_project`.`id`=? AND `t_project`.`name`=? AND `t_project`.`id`=? LIMIT 1 [222 333 asd] - 9.977521ms

看起来tenant表的条件都没有了

当把查询换成

	var tp struct {
		Tenant  `xorm:"extends"`
		Project `xorm:"extends"`
	}
	tp.Project.Model.ID = "asd"
	tp.Tenant.Name = "333"
	tp.Tenant.Model.ID = "222"
	tp.Tenant.Email = "444"
	exists, err := db.Engine.Table(&Project{}).
		Join("INNER",
			db.Engine.TableName(&Tenant{}),
			fmt.Sprintf("%v.id=%v.tenant_id", db.Engine.TableName(&Tenant{}), db.Engine.TableName(&Project{}))).
		Get(&tp)

就会报错

[xorm] [info]  2021/09/10 18:30:11.216396 [SQL] SELECT * FROM `t_project` INNER JOIN `t_tenant` ON t_tenant.id=t_project.tenant_id WHERE `t_project`.`id`=? AND `t_project`.`name`=? AND `t_project`.`email`=? AND `t_project`.`id`=? LIMIT 1 [222 333 444 asd] - 7.819626ms
    tenant_test.go:26: Error 1054: Unknown column 't_project.email' in 'where clause'

使用的xorm版本是v1.0.7

定义以下两个表 ```go type Model struct { ID string `xorm:"'id' UUID pk notnull" json:"id"` CreatedAt time.Time `xorm:"created index(created)" json:"created_at"` UpdatedAt time.Time `xorm:"updated" json:"updated_at"` CreatedBy string `xorm:"'created_by' varchar(64)" json:"created_by"` UpdatedBy string `xorm:"'updated_by' varchar(64)" json:"updated_by"` } type Tenant struct { Model `xorm:"extends"` Name string `xorm:"'name' varchar(64) notnull " json:"name"` Email string `xorm:"'email' varchar(64)" json:"email"` } type Project struct { Model `xorm:"extends"` TenantID string `xorm:"'tenant_id' notnull UUID " json:"tenant_id"` Name string `xorm:"'name' varchar(64) notnull" json:"name"` Description string `xorm:"'description' text" json:"description"` } ``` 使用查询 ```go var tp struct { Tenant `xorm:"extends"` Project `xorm:"extends"` } tp.Project.Model.ID = "asd" tp.Tenant.Name = "333" tp.Tenant.ID = "222" exists, err := db.Engine.Table(&Project{}). Join("INNER", db.Engine.TableName(&Tenant{}), fmt.Sprintf("%v.id=%v.tenant_id", db.Engine.TableName(&Tenant{}), db.Engine.TableName(&Project{}))). Get(&tp) ``` 会生成以下sql: ```bash [xorm] [info] 2021/09/10 18:29:15.376693 [SQL] SELECT * FROM `t_project` INNER JOIN `t_tenant` ON t_tenant.id=t_project.tenant_id WHERE `t_project`.`id`=? AND `t_project`.`name`=? AND `t_project`.`id`=? LIMIT 1 [222 333 asd] - 9.977521ms ``` 看起来tenant表的条件都没有了 当把查询换成 ```go var tp struct { Tenant `xorm:"extends"` Project `xorm:"extends"` } tp.Project.Model.ID = "asd" tp.Tenant.Name = "333" tp.Tenant.Model.ID = "222" tp.Tenant.Email = "444" exists, err := db.Engine.Table(&Project{}). Join("INNER", db.Engine.TableName(&Tenant{}), fmt.Sprintf("%v.id=%v.tenant_id", db.Engine.TableName(&Tenant{}), db.Engine.TableName(&Project{}))). Get(&tp) ``` 就会报错 ```bash [xorm] [info] 2021/09/10 18:30:11.216396 [SQL] SELECT * FROM `t_project` INNER JOIN `t_tenant` ON t_tenant.id=t_project.tenant_id WHERE `t_project`.`id`=? AND `t_project`.`name`=? AND `t_project`.`email`=? AND `t_project`.`id`=? LIMIT 1 [222 333 444 asd] - 7.819626ms tenant_test.go:26: Error 1054: Unknown column 't_project.email' in 'where clause' ``` 使用的xorm版本是v1.0.7
Author

另外,如果查询到结果,那结构体里面的值也是乱套的。。

另外,如果查询到结果,那结构体里面的值也是乱套的。。
Owner

You should make the order of extends as same as join.

You should make the order of `extends` as same as `join`.
lunny added the
need
feedback
label 2021-09-11 13:50:02 +00:00
Author

@lunny
thanks for your reply.

Even i used correct order, it still works wrong.

	var tp struct {
		Project  `xorm:"extends"`
		Tenane   `xorm:"extends"`
	}
	tp.Tenant.Model.ID = "asd"
	exists, err := db.Engine.Table(&Project{}).
		Join("INNER",
			db.Engine.TableName(&Tenant{}),
			fmt.Sprintf("%v.id=%v.tenant_id", db.Engine.TableName(&Tenant{}), db.Engine.TableName(&Project{}))).
		Get(&tp)

this code will give following sql:

[xorm] [info]  2021/09/13 11:04:23.009370 [SQL] SELECT * FROM `t_project` INNER JOIN `t_tenant` ON t_tenant.id=t_project.tenant_id WHERE `t_project`.`id`=? LIMIT 1 [asd]
@lunny thanks for your reply. Even i used correct order, it still works wrong. ```go var tp struct { Project `xorm:"extends"` Tenane `xorm:"extends"` } tp.Tenant.Model.ID = "asd" exists, err := db.Engine.Table(&Project{}). Join("INNER", db.Engine.TableName(&Tenant{}), fmt.Sprintf("%v.id=%v.tenant_id", db.Engine.TableName(&Tenant{}), db.Engine.TableName(&Project{}))). Get(&tp) ``` this code will give following sql: ```bash [xorm] [info] 2021/09/13 11:04:23.009370 [SQL] SELECT * FROM `t_project` INNER JOIN `t_tenant` ON t_tenant.id=t_project.tenant_id WHERE `t_project`.`id`=? LIMIT 1 [asd] ```
Sign in to join this conversation.
No Milestone
No Assignees
2 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#2044
No description provided.