Has Many relation is searching by NULL Id

[GaryLCoxJr]

I have the following structs:

type AccessGroup struct {
    TableName     struct{}                        `sql:"access_groups,alias:access_group"`
    Id            int                             `sql:",pk" json:"id"`
    TenantId      int                             `sql:",notnull" json:"-"`
    Name          string                          `json:"name"`
    DoorSchedules []*DoorGroupScheduleAccessGroup `json:"doors"`

    CreatedAt time.Time `json:"created_at"`
    UpdatedAt time.Time `json:"updated_at"`
}

type DoorGroupScheduleAccessGroup struct {
    TableName     struct{} `sql:"door_group_schedule_access_groups,alias:door_schedules"`
    Id            int      `sql:",pk" json:"id"`
    AccessGroupId int
    DoorGroupId   int
    DoorGroup     *DoorGroup
    ScheduleId    int
    Schedule      *Schedule
}

When I try to load the list of AccessGroups, I get an empty array of DoorSchedules.

accessGroups := make([]*AccessGroup, 0)
err := database.DB.
      Model(&AccessGroup{}).
      Column("access_group.*").
      Where("access_group.tenant_id = ?", tenantID).
      Relation("DoorSchedules").
      Select(&accessGroups)

SQL Generated

SELECT "access_group".* FROM access_groups AS access_group WHERE (access_group.tenant_id = '7')
SELECT door_schedules."id", door_schedules."access_group_id", door_schedules."door_group_id", door_schedules."schedule_id" FROM door_group_schedule_access_groups AS door_schedules WHERE (door_schedules."access_group_id" IN (NULL))

Notice the (door_schedules."access_group_id" IN (NULL)

[vmihailenco]

That is sub-optimal but still correct, right?

[GaryLCoxJr]

@vmihailenco it's not correct, it should be loading the door_schedules that I have added. However the query is passing (NULL) for keys, seems like a bug or am I doing something wrong? The query being run does return 1 record for "access_group"

[vmihailenco]

Then you need to provide a full program that reproduces the problem.

[GaryLCoxJr]

@vmihailenco

package main

import (
    "fmt"
    "github.com/go-pg/pg"
    "github.com/go-pg/pg/orm"
    "time"
)

type AccessGroup struct {
    TableName     struct{}                        `sql:"access_groups,alias:access_group"`
    Id            int                             `sql:",pk" json:"id"`
    TenantId      int                             `sql:",notnull" json:"-"`
    Name          string                          `json:"name"`
    DoorSchedules []*DoorGroupScheduleAccessGroup `json:"doors"`

    CreatedAt time.Time `json:"created_at"`
    UpdatedAt time.Time `json:"updated_at"`
}

type DoorGroupScheduleAccessGroup struct {
    TableName     struct{} `sql:"door_group_schedule_access_groups,alias:door_schedules"`
    Id            int      `sql:",pk" json:"id"`
    AccessGroupId int
    DoorGroupId   int
    ScheduleId    int
}

func createTables(db *pg.DB) error {
    models := []interface{}{
        (*AccessGroup)(nil),
        (*DoorGroupScheduleAccessGroup)(nil),
    }
    for _, model := range models {
        err := db.CreateTable(model, &orm.CreateTableOptions{
            Temp: true,
        })
        if err != nil {
            return err
        }
    }
    return nil
}

func main() {
    db := pg.Connect(&pg.Options{
        Addr:     "0:5432",
        User:     "", // Your user
        Password: "", // Your password
        Database: "", // Your database
    })

    defer db.Close()

    if err := createTables(db); err != nil {
        panic(err)
    }

    values := []interface{}{
        &AccessGroup{Id: 1, TenantId: 1, Name: "test1"},
        &DoorGroupScheduleAccessGroup{Id: 1, AccessGroupId: 1, ScheduleId: 1, DoorGroupId: 1},
    }
    for _, v := range values {
        err := db.Insert(v)
        if err != nil {
            panic(err)
        }
    }

    accessGroups := make([]*AccessGroup, 0)
    err := db.
        Model(&AccessGroup{}).
        Column("access_group.*").
        Where("access_group.tenant_id = ?", 1).
        Relation("DoorSchedules").
        Select(&accessGroups)

    if err != nil {
        panic(err)
    }
    fmt.Printf("Access: %s\n", accessGroups[0].Name)
    // This will fail because DoorSchedules is empty
    fmt.Println("Access", accessGroups[0].Id, "DoorSchedules", accessGroups[0].DoorSchedules[0].Id)

}

[vmihailenco]

It should be db.Model(&accessGroups)...Select(). Otherwise go-pg uses &AccessGroup{} model which is empty.

[GaryLCoxJr]

@vmihailenco "face palm" moment, thanks!

[frederikhors]

@GaryLCoxJr Did you find a way to avoid the tenant query in every single query and use a "global" mode? I asked here: https://github.com/go-pg/pg/issues/1179

@vmihailenco, we can close this I think.

[GaryLCoxJr]

@vmihailenco this can be closed. @frederikhors thanks for the link, my database code is in its' own repo that is used by several micro-services. So for me, the tenant has to be passed into the func.