Gorm Nested Preload loading incorrect data into model

GORM Playground Link

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

Description

I would like to preload a nested field from a model.

In the playground my models are set up as follows: * User has many pets * Pet has many toys * Pet has one Favourite Toy

When I do:

userFromDB := &User{}
result := DB.
  Preload("Pets").
  Preload("Pets.FavToy").
  First(userFromDB)
 ```

I would expect the FavToy data to be populated based on the fav_toy_id field stored on the pets table in the DB.

However, in some cases the FavToy model is populated by one of the Pet's toys, but not their Fav Toy.


**Comment From: LouisSayers**

Actually it's not even just the case with nested Preloads, it seems this problem exists for directly referenced data as well.

e.g. the following would have the same issue 

pet := &Pet{} result := DB. Preload("FavToy"). First(pet)

**Comment From: LouisSayers**

To get around this I've ended up doing something like:

pet := &Pet{} result := DB. Joins("FavToy"). Where("pets"."fav_toy_id" = "FavToy"."id"). First(pet)

Which is a bit of a hack - I learnt that the same issue is also present with `Joins`...

**Comment From: eduardoths**

I took a look on your PR and got a few minor mistakes from your modelling.
When you're transaction a query with a preload you're basically saying "take this private key from the pets and search for the foreignKey from Toys equals to the pk" (you're not doing a join), so in the Sql table we would have something like.

|   Pets  | Description |
| :------: | :------: |
| id | private key | 

| Toys | Description | 
| :------: | :------: |
| id | private key |
| pet_id | foreign key that relates to pet |

This way when you're trying to create a HasMany the table where we have many items is where we should store a foreign key.

The problem with the way you're modeled is that there's no key to discover which of the toys is the pet's favorite. To do it we could also store a foreign key on the pet table to make a HasOne relation with toys. And so we would have something like:

|   Pets  | Description |
| :------: | :------: |
| id | private key | 
| favorite_toy_id | foreign key has one favorite toy |

| Toys | Description | 
| :------: | :------: |
| id | private key |
| pet_id | foreign key that relates to pet |

I'm also new to gorm so I couldn't find a way to store the foreignKey on the same Model we define the HasOne, so I've modeled it like this:
```go
type User struct {
    gorm.Model
    Pets []*Pet
}

type Pet struct {
    gorm.Model
    Name   string
    FavToy *Toy   `gorm:"foreignKey:FavoritedBy;references:ID"`
    Toys   []*Toy `gorm:"foreignKey:ID"`
    UserID uint
}

type Toy struct {
    gorm.Model
    Name        string
    PetID       uint
    FavoritedBy uint
}

This way you can make your query by executing this: ```go result := DB. Preload("Pets"). Preload("Pets.FavToy"). Preload("Pets.Toys"). First(userFromDB)

Also there's an error with the way you're saving the user database because you've already saved on pet1 and pet2 the  userID relation key, so when you're executing `user.Pets = []*Pet{pet1}` the user still has pet1 and pet2. I would recommend you to do it like this:
```go
    pet1 := &Pet{Name: "pet1"}
    pet2 := &Pet{Name: "pet2"}

    DB.Create(pet1)
    DB.Create(pet2)

    user.Pets = []*Pet{pet1}
    DB.Save(user)

Comment From: LouisSayers

Thanks for the thorough review @eduardoths!

The problem with the way you're modeled is that there's no key to discover which of the toys is the pet's favorite.

I didn't spot that the Pets table was missing the foreign key field. I've updated the PR to fix that (I was missing a FavToyID field on the Pet struct).

Also there's an error with the way you're saving the user database

Ah yes, you're right. I've removed that line as it's not relevant for the test.

---

In regards to to the DB modelling, I made a comment on the PR, but for the semantics of what I'm showing in this particular test case I'm really wanting the pets table to have a fav_toy_id field. In the given scenario a pet should only ever have at most one favourite toy. Therefore, I'd like to avoid adding another foreign key on the toys table as technically this would allow the database to support a pet having many favourite toys, which is not the intention of this example.

Comment From: eduardoths

You're wellcome! I've been thinking about the solution to your problem lately and searching through the docs I found out that I was wrong and gorm actually allows bellongs to relations. There's a big problem with what you want which is: there's a circular relation: pet has many toys, so in the table toys there will be a field called pet_id. But if we're creating a fav_toy_id we have a circular reference and this constraint will bring us some problems when trying to insert it into the database.

So this is the models I came up with

type User struct {
    gorm.Model
    Pets []*Pet
}

type Pet struct {
    gorm.Model
    Name     string
    FavToy   *Toy `gorm:"foreignKey:FavToyID;references:ID"`
    FavToyID *uint
    Toy      []*Toy
    UserID   uint
    User     *User
}

type Toy struct {
    gorm.Model
    Name  string
    PetID uint
}

I don't know why, but I gorm wouldn't just automatically create the right constraint if I didn't specify it's the tag. There's also something special: FavToyID must be a pointer, not a int, because in this way we're telling gorm this relation is nullable and if we don't do that we're gonna have problems when inserting. If we don't do this we have the following problem: to create a pet we must have a FavToy and to create a Toy we must have a Pet. By doing this it will allow us to create pet first, then create the toy and then update FavToyID field. And there's another database problem when we're trying to migrate the models due to this circular reference (even though we have a nullable field in fav toy) which I only found a way out thanks to this link. I don't know why but when you migrate your Toy model, gorm knows it will have a relation with Pet so the only way I found was creating another package (I called it custom) and in there I would only create the Toy model, without any relation to pets:

package custom

import "gorm.io/gorm"

type Toy struct {
    gorm.Model
    Name  string
    PetID uint
}

And then when trying to migrate I had to migrate the custom.Toy before Pet and then migrate the wanted Toy:

func RunMigrations() {
    var err error
    allModels := []interface{}{&User{}, &Toy{}, &Pet{}}
    rand.Seed(time.Now().UnixNano())

    DB.Migrator().DropTable("user_friends", "user_speaks")
    if err = DB.Migrator().DropTable(allModels...); err != nil {
        log.Printf("Failed to drop table, got error %v\n", err)
        os.Exit(1)
    }
       // This create a toy table withouth any constraints
    if err = DB.AutoMigrate(&custom.Toy{}); err != nil {
        log.Printf("Failed to auto migrate, but got error %v\n", err)
        os.Exit(1)
    }

    if err = DB.AutoMigrate(allModels...); err != nil {
        log.Printf("Failed to auto migrate, but got error %v\n", err)
        os.Exit(1)
    }

    for _, m := range allModels {
        if !DB.Migrator().HasTable(m) {
            log.Printf("Failed to create table for %#v\n", m)
            os.Exit(1)
        }
    }
}

And by doing this it was finally possible to use preload for favorite toy 😄

English is not my main language so let me know if something is not clear