How do I get Preload with Select to work?

[cinjon]

Your Question

My two types are below. I would like to return the user plus its known last latitude and longitude and nothing else. However, I am struggling to do this and the best I have right now is the following that returns everything in the embedded location.

database.DB.Debug().Preload("Locations").Where("user_id = ?", uid).Find(&users)

How do I get this to work properly? All attempts to select from the location object are just producing null, e.g.

database.DB.Debug().Preload("Locations").Where("user_id = ?", uid).Select('user.id, locations.latitude').Find(&users)

type Location struct {
    gorm.Model
    UserID string    `json:"userId"`
    User   User  `gorm:"foreignKey:userID;references:Id"`
    Latitude   float64   `json:"latitude"`
    Longitude  float64   `json:"longitude"`
    RecordedAt time.Time `json:"recordedAt,omitempty"`
}

type User struct {
    gorm.Model
    Id       string    `gorm:"type:uuid;primaryKey" json:"id"`
    Name     string    `json:"name"`
    Email    string    `json:"email;unique"`
    Locations               []Location `json:"locations"`
}

The document you expected this should be explained

Expected answer

I expected that I'd get back select arguments from locations and users.

[black-06]

type Location struct {
    ID         uint64
    UserID     uint64
    Latitude   float64
    Longitude  float64
    RecordedAt time.Time
}

type User struct {
    ID        uint64
    Name      string
    Email     string
    Locations []Location
}

func TestSelectPerload(t *testing.T) {
    db := GetConn()

    db.Migrator().DropTable(&Location{}, &User{})
    db.AutoMigrate(&Location{}, &User{})

    db.Create(&User{
        ID:    1,
        Name:  "foo",
        Email: "bar",
        Locations: []Location{
            {ID: 1, Latitude: 1.1, Longitude: 1.2, RecordedAt: time.Date(2001, 1, 1, 0, 0, 0, 0, time.UTC)},
            {ID: 2, Latitude: 2.1, Longitude: 2.2, RecordedAt: time.Date(2002, 1, 1, 0, 0, 0, 0, time.UTC)},
            {ID: 3, Latitude: 3.1, Longitude: 3.2, RecordedAt: time.Date(2003, 1, 1, 0, 0, 0, 0, time.UTC)},
        },
    })

    lastLocation := func(tx *gorm.DB) *gorm.DB {
        return tx.Select("user_id", "latitude").Order("recorded_at DESC").Limit(1)
    }

    var user User
    db.Preload("Locations", lastLocation).Where("id = ?", 1).Select("id").Find(&user)

    assert.Equal(t, User{
        ID: 1,
        Locations: []Location{
            {UserID: 1, Latitude: 3.1},
        },
    }, user)
}

[cinjon]

That's not working. I am also getting back other fields, both those from the gorm.Model (updatedAt, deletedAt) as well as my own isBirth. I'll try to put up a playground.

"locations":[{"ID":0,"CreatedAt":"0001-01-01T00:00:00Z","UpdatedAt":"0001-01-01T00:00:00Z","DeletedAt":null,"userId":1,"latitude":3.21,"longitude":11.22,"recordedAt":"0001-01-01T00:00:00Z","isBirth":false}]

func GetStaticUsers(c *gin.Context) {
    lastLocation := func(tx *gorm.DB) *gorm.DB {
        return tx.Select("user_id", "latitude", "longitude").Order("recorded_at DESC").Limit(1)
    }

    var users []models.User
    if err := database.DB.Preload("Locations", lastLocation).Where("is_static = ?", true).Select("id").Find(&users).Error; err != nil {
        c.JSON(http.StatusNotFound, gin.H{"status": "error", "message": "No static users found"})
    } else {
        c.JSON(http.StatusOK, gin.H{
            "status":    "success",
            "users": users,
        })
    }
}

type User struct {
    gorm.Model
    Name                    string     `json:"name"`
    Locations               []Location `json:"locations"`
    NumSimultaneousMeetings int        `json:"numSimultaneousMeetings"`
    IsStatic                bool       `json:"isStatic"`
}

type Location struct {
    gorm.Model
    UserID uint `json:"userId"`
    Latitude   float64   `json:"latitude"`
    Longitude  float64   `json:"longitude"`
    RecordedAt time.Time `json:"recordedAt,omitempty"`
    IsBirth    bool      `json:"isBirth"`
}

[cinjon]

It also doesn't work with many users, e.g.

    lastLocation := func(tx *gorm.DB) *gorm.DB {
        return tx.Select("user_id", "latitude", "longitude").Order("recorded_at DESC").Limit(1)
    }

    var users []models.User
    if err := r.Database.Debug().Preload("Locations").Find(&users).Error; err != nil {
        return nil, fmt.Errorf("Cannot get static users")
    }

Returns only the most recent location of the last found user.

[github-actions[bot]]

This issue has been automatically marked as stale because it has been open 360 days with no activity. Remove stale label or comment or this will be closed in 180 days