https://github.com/go-gorm/playground/pull/736
type AB struct {
Id int `json:"id" gorm:"primaryKey"`
BId int `json:"aId" gorm:"index"`
AId int `json:"bId"`
B B `json:"b"`
A A `json:"a"`
}
type A struct {
Id int `json:"id" gorm:"primaryKey"`
Name string `json:"name" gorm:"uniqueIndex"`
}
type B struct {
Id int `json:"id" gorm:"primaryKey"`
Name string `json:"name" gorm:"uniqueIndex"`
}
func main() {
db, _ := gorm.Open(tests.DummyDialector{}, &gorm.Config{
NamingStrategy: schema.NamingStrategy{
SingularTable: true,
},
})
var (
count int64
appNodes = []*AB{}
)
db:= db.Model(&AB{}).Joins("A").Joins("B").Where(`id = ?`, 1)
db.Count(&count)
db.Limit(1).Offset(0).Order("id DESC").Find(&appNodes)
}
db := db.Model(&AB{}).Preload("A").Preload("B").Where(`id = ?`, 1)
The two pieces of code have different results, preload will get the correct result, because Count will call AfterQuery,it will "clear the joins after query because preload need it" ,I don't know why did it, this means need to write Joins twice.
Comment From: github-actions[bot]
The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨
Comment From: ivila
@liov What version are you using?
var (
count int64
appNodes = []*AB{}
)
db1 := db.Model(&AB{}).Joins("A").Joins("B").Where("abs.`id` = ?", 1)
// [0.572ms] [rows:1] SELECT count(*) FROM `abs` LEFT JOIN `as` `A` ON `abs`.`a_id` = `A`.`id` LEFT JOIN `bs` `B` ON `abs`.`b_id` = `B`.`id` WHERE abs.`id` = 1
db1.Debug().Count(&count)
// [0.705ms] [rows:0] SELECT `abs`.`id`,`abs`.`b_id`,`abs`.`a_id`,`A`.`id` AS `A__id`,`A`.`name` AS `A__name`,`B`.`id` AS `B__id`,`B`.`name` AS `B__name` FROM `abs` LEFT JOIN `as` `A` ON `abs`.`a_id` = `A`.`id` LEFT JOIN `bs` `B` ON `abs`.`b_id` = `B`.`id` WHERE abs.`id` = 1 ORDER BY abs.`id` DESC LIMIT 1
db1.Limit(1).Offset(0).Order("abs.`id` DESC").Debug().Find(&appNodes)
var ab AB
// [0.630ms] [rows:0] SELECT `abs`.`id`,`abs`.`b_id`,`abs`.`a_id`,`A`.`id` AS `A__id`,`A`.`name` AS `A__name`,`B`.`id` AS `B__id`,`B`.`name` AS `B__name` FROM `abs` LEFT JOIN `as` `A` ON `abs`.`a_id` = `A`.`id` LEFT JOIN `bs` `B` ON `abs`.`b_id` = `B`.`id` WHERE abs.`id` = 1 ORDER BY abs.`id` DESC LIMIT 1
db1.Model(&AB{}).Preload("A").Preload("B").Debug().Find(&ab)
I run it with version v1.25.9, and the two pieces of code have same results.
Comment From: ivila
And testing with your playground (after fix your syntax error with the: Error 1052 (23000): Column 'id' in where clause is ambiguous), the two pieces of code have same results too (with both version v1.25.9 and your playground version v1.25.4)
// [rows:0] SELECT `abs`.`id`,`abs`.`b_id`,`abs`.`a_id`,`B`.`id` AS `B__id`,`B`.`name` AS `B__name` FROM `abs` LEFT JOIN `bs` `B` ON `abs`.`b_id` = `B`.`id` WHERE abs.id = 11
db = DB.Model(&AB{}).Preload("A").Joins("B").Debug().Where(`abs.id = ?`, 11).Find(&ab)
// [2.033ms] [rows:1] SELECT count(*) FROM `abs` LEFT JOIN `bs` `B` ON `abs`.`b_id` = `B`.`id` WHERE abs.id = 11
db.Debug().Count(&count)
// [1.860ms] [rows:0] SELECT `abs`.`id`,`abs`.`b_id`,`abs`.`a_id`,`B`.`id` AS `B__id`,`B`.`name` AS `B__name` FROM `abs` LEFT JOIN `bs` `B` ON `abs`.`b_id` = `B`.`id` WHERE abs.id = 11 ORDER BY abs.id DESC LIMIT 1
db.Limit(1).Offset(0).Order("abs.id DESC").Debug().Find(&appNodes)
Comment From: liov
@ivila I'm mistaken, actually it's right to clear joins here, but count affects the select, please try the latest https://github.com/go-gorm/playground/pull/736
Comment From: liov
I guess that's where the problem lies
for _, join := range db.Statement.Joins {
// xxx
if isRelations {
for _, s := range relation.FieldSchema.DBNames {
if v, ok := selectColumns[s]; (ok && v) || (!ok && !restricted) {
clauseSelect.Columns = append(clauseSelect.Columns, clause.Column{
Table: tableAliasName,
Name: s,
Alias: utils.NestedRelationName(tableAliasName, s),
})
}
}
}
}
Comment From: liov
Is anyone looking into this? This bug was introduced by the fix for this issue https://github.com/go-gorm/gorm/issues/6715. Now, I just need to call count in front, and it can be fully reproduced. https://github.com/go-gorm/playground/pull/736
2024/05/21 03:15:28 /home/runner/work/playground/playground/main_test.go:35
[0.313ms] [rows:1] SELECT count(*) FROM `users` LEFT JOIN `accounts` `Account` ON `users`.`id` = `Account`.`user_id` AND `Account`.`deleted_at` IS NULL LEFT JOIN `pets` `Account__Pet` ON `Account`.`id` = `Account__Pet`.`account_id` AND `Account__Pet`.`deleted_at` IS NULL WHERE `users`.`deleted_at` IS NULL
2024/05/21 03:15:28 /home/runner/work/playground/playground/main_test.go:39
[0.144ms] [rows:2] SELECT * FROM `companies` WHERE `companies`.`account_id` = 1
2024/05/21 03:15:28 /home/runner/work/playground/playground/main_test.go:39
[0.445ms] [rows:1] SELECT * FROM `accounts` WHERE `accounts`.`user_id` = 1 AND `accounts`.`deleted_at` IS NULL
2024/05/21 03:15:28 /home/runner/work/playground/playground/main_test.go:39
[0.859ms] [rows:1] SELECT `users`.`id`,`users`.`created_at`,`users`.`updated_at`,`users`.`deleted_at` FROM `users` LEFT JOIN `accounts` `Account` ON `users`.`id` = `Account`.`user_id` AND `Account`.`deleted_at` IS NULL LEFT JOIN `pets` `Account__Pet` ON `Account`.`id` = `Account__Pet`.`account_id` AND `Account__Pet`.`deleted_at` IS NULL WHERE `users`.`deleted_at` IS NULL AND `users`.`id` = 1 ORDER BY `users`.`id` LIMIT 1
main_test.go:48: Failed, got ''
Also, I have fixed it now. https://github.com/go-gorm/gorm/pull/7027