GORM Playground Link

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

Description

When I tried to get parent and its child by using Joins and Where, the same where condition combined twice time

Model

type Parent struct {
    ID int `gorm:"primarykey"`
    Name string
    Children []Child
}

type Child struct{
    ChildID int `gorm:"primarykey"`
    ParentID int
    ChildName string
}

Code

        var result Parent
    if err := DB.Model(&result).Select("*").Joins("LEFT JOIN children ON children.parent_id = parents.id").Where("parents.id = ?", 1).Scan(&result).Scan(&result.Children).Error; err != nil {
        t.Errorf("Failed, got error: %v", err)
    }

    log.Println(result)

Expected Result:

2022/03/05 00:48:27 /home/hidetomo8111f/playground/main_test.go:50
[6.669ms] [rows:1] SELECT * FROM "parents" LEFT JOIN children ON children.parent_id = parents.id WHERE parents.id = 1

2022/03/05 00:48:27 /home/hidetomo8111f/playground/main_test.go:50
[2.311ms] [rows:3] SELECT * FROM "parents" LEFT JOIN children ON children.parent_id = parents.id WHERE parents.id = 1 
2022/03/05 00:48:27 {1 name1 [{1 1 Child1} {2 1 Child2} {3 1 Child3}]}

What I get:

2022/03/05 00:48:27 /home/hidetomo8111f/playground/main_test.go:50
[6.669ms] [rows:1] SELECT * FROM "parents" LEFT JOIN children ON children.parent_id = parents.id WHERE parents.id = 1

2022/03/05 00:48:27 /home/hidetomo8111f/playground/main_test.go:50
[2.311ms] [rows:3] SELECT * FROM "parents" LEFT JOIN children ON children.parent_id = parents.id WHERE parents.id = 1 AND "parents"."id" = 1
2022/03/05 00:48:27 {1 name1 [{1 1 Child1} {2 1 Child2} {3 1 Child3}]}

Why same condition combined into second query when I Called Scan multiple time? What is the efficient way to scanning the results of SELECT JOIN queries into parent and child object without using Preload?

Comment From: a631807682

  1. Model primary key will append to where.Exprs if not zero. you should use &Parent{} instead. https://github.com/go-gorm/gorm/blob/master/tests/scan_test.go#L43
  2. gorm.DB changed by every Scan or other func called, to reuse gorm.DB you should to use Session. https://gorm.io/docs/method_chaining.html#Method-Chain-Safety-Goroutine-Safety
    var result Parent
    sess := DB.Model(&Parent{}).Select("*").Joins("LEFT JOIN children ON children.parent_id = parents.id").Where("parents.id = ?", 1).Session(&gorm.Session{})
    sess.Scan(&result)
    sess.Scan(&result.Children)

Comment From: maxant

a) the double scan is cool, but I cannot find it in the docs b) it doesn't quite work when the child table contains column names which match the parent table, e.g. simply "ID" for the PK - they need to be named uniquely, otherwise scanning seems to take the first column it encounters, rather than the column belonging to the relevant table