Gorm Join in combination with preload does not works as intended (basic usage)

GORM Playground Link

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

Description

Given this basic test :

package main

import (
    "testing"
)

// GORM_REPO: https://github.com/go-gorm/gorm.git
// GORM_BRANCH: master
// TEST_DRIVERS: sqlite, mysql, postgres

func TestGORM(t *testing.T) {
    user := User{
        Account: Account{
            Number: "123456",
            Companies: []Company{
                {Name: "Corp1"}, {Name: "Corp2"},
            },
            Pet: Pet{
                Name: "Pet1",
            },
        },
    }

    DB.Create(&user)

    var result User
    if err := DB.
        Joins("Account").
        Joins("Account.Pet").
        Preload("Account.Companies").
        First(&result, user.ID).Error; err != nil {
        t.Errorf("Failed, got error: %v", err)
    }

    if len(result.Account.Companies) != 2 {
        t.Errorf("Failed, got %v", len(result.Account.Companies))
    }

    if result.Account.Pet.Name != "Pet1" {
        t.Errorf("Failed, got '%v'", result.Account.Pet.Name)
    }
}

The test fails.

It must not fail, the query is correctly formatted :

SELECT "users"."id",
       "users"."created_at",
       "users"."updated_at",
       "users"."deleted_at",
       "Account"."id"              AS "Account__id",
       "Account"."created_at"      AS "Account__created_at",
       "Account"."updated_at"      AS "Account__updated_at",
       "Account"."deleted_at"      AS "Account__deleted_at",
       "Account"."user_id"         AS "Account__user_id",
       "Account"."number"          AS "Account__number",
       "Account__Pet"."id"         AS "Account__Pet__id",
       "Account__Pet"."created_at" AS "Account__Pet__created_at",
       "Account__Pet"."updated_at" AS "Account__Pet__updated_at",
       "Account__Pet"."deleted_at" AS "Account__Pet__deleted_at",
       "Account__Pet"."account_id" AS "Account__Pet__account_id",
       "Account__Pet"."name"       AS "Account__Pet__name"
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"."id" = 1
       AND "users"."deleted_at" IS NULL
ORDER  BY "users"."id"
LIMIT  1

As you can see Account__Pet is here, but it is not loaded. If you remove the preload it's working. If you replace Joins("Account.Pet"). with a preloda it's works.

The problem occure for a combination of join and preload.

Use case : optimizing queries, not performing preload for a join query.

Thanks for your amazing works, I might open a pull request for a feature that has nothing to do with that.

Comment From: alexisvisco

Even with just :

        Joins("Account.Pet").
        Preload("Account.Companies")

It does not works

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: alexisvisco

@jinzhu there is a gorm playground repo https://github.com/alexisvisco/join-and-preload-not-working

Comment From: github-actions[bot]

Comment From: alexisvisco

When I was trying to debug why this happen I just discovered a misoptimization :

This query :

DB.Joins("Account.Pet").Preload("Account.Companies").First(&result, user.ID)

Should execute 2 requests on the db, it does 3 :

2023/11/29 18:38:25 /Users/alexisviscogliosi/dev/join-and-preload-not-working/main_test.go:31 SLOW SQL >= 200ms
[247.663ms] [rows:2] SELECT * FROM "companies" WHERE "companies"."account_id" = 1

2023/11/29 18:38:25 /Users/alexisviscogliosi/dev/join-and-preload-not-working/main_test.go:31 SLOW SQL >= 200ms
[373.844ms] [rows:1] SELECT * FROM "accounts" WHERE "accounts"."user_id" = 1 AND "accounts"."deleted_at" IS NULL

2023/11/29 18:38:25 /Users/alexisviscogliosi/dev/join-and-preload-not-working/main_test.go:31 SLOW SQL >= 200ms
[589.421ms] [rows:1] SELECT "users"."id","users"."created_at","users"."updated_at","users"."deleted_at","Account"."id" AS "Account__id","Account"."created_at" AS "Account__created_at","Account"."updated_at" AS "Account__updated_at","Account"."deleted_at" AS "Account__deleted_at","Account"."user_id" AS "Account__user_id","Account"."number" AS "Account__number","Account__Pet"."id" AS "Account__Pet__id","Account__Pet"."created_at" AS "Account__Pet__created_at","Account__Pet"."updated_at" AS "Account__Pet__updated_at","Account__Pet"."deleted_at" AS "Account__Pet__deleted_at","Account__Pet"."account_id" AS "Account__Pet__account_id","Account__Pet"."name" AS "Account__Pet__name" 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"."id" = 1 AND "users"."deleted_at" IS NULL ORDER BY "users"."id" LIMIT 1

Account and Pet are in the main query but Account is also in a separate query, this might be optimized.

Comment From: github-actions[bot]

Comment From: a631807682

https://github.com/go-gorm/gorm/blob/master/callbacks/preload.go#L195

The queried content seems to be cleared by preload. cc @black-06

Comment From: black-06

I'll try to fix it.

Comment From: alexisvisco

Hi, I was investigating too and @a631807682 is right, the preload seems ovverwrite the join. That's why it execute 3 queries instead of 2.

Comment From: black-06

~~nested_join feature is later than nested_preload, so~~ preload thinks it needs to preload "Account" and "Companies" in turn. Unfortunately, Preload callback occurs after join query, so its preload of "Account" overwrite the value of join.

Comment From: alexisvisco

@black-06 any way to know if there is a join that has in commons associations between joins and preload, if it's the case not creating unecessary queries and so not ovveriding the previous scan ?

Comment From: alexisvisco

We should find a way to know what has already been loaded, right? From what I understand it seems that the statement is modified between each callback because the Query callback has the joins and the preloads, but the preload callback no longer has the joins.

Comment From: remicaumette

We (with @alexisvisco) dug into the issue and found that preload is done recursively by creating a query for each relation (https://github.com/go-gorm/gorm/blob/master/callbacks/preload.go#L192). Querying must be performed because everything seems to rely greatly on it. We will need to introduce a notion of root query to fix this. A hack more than a solution could be to : - cache statement.Joins, because it's cleared when the query is performed (https://github.com/go-gorm/gorm/blob/master/callbacks/query.go#L257) - introduce a RootStatement in the Statement struct - the RootStatement will cache the results of each relation it fetches - in the query callback we use cached results if a similar query is found in the root statement (here https://github.com/go-gorm/gorm/blob/master/callbacks/query.go#L20)

Do you have any other solutions? I can implement this if everyone is ok with this 🙂

Comment From: a631807682

Can we just observe whether there is a relationship between Join and Preload? For the above example, when Join and Preload have the same relationship Account, Preload's query for Account can be ignored. And if the same relationship does not exist https://github.com/go-gorm/gorm/blob/master/utils/tests/models.go#L15, you need to query

Joins("Account").Preload("Manager.Account")

Comment From: black-06

Can we just observe whether there is a relationship between Join and Preload? For the above example, when Join and Preload have the same relationship Account, Preload's query for Account can be ignored. And if the same relationship does not exist https://github.com/go-gorm/gorm/blob/master/utils/tests/models.go#L15, you need to query

go Joins("Account").Preload("Manager.Account")

Yes, I'm trying to do that, but as @remicaumette says

cache statement.Joins, because it's cleared when the query is performed (https://github.com/go-gorm/gorm/blob/master/callbacks/query.go#L257)

So I'm looking into it here.

Comment From: zonocto

this works for me Preload("Roles", func(db *gorm.DB) *gorm.DB { return db.Table("business_roles b"). Select("b.id, b.role_id, b.business_id, b.location_id, b.type, u.name, u.email, u.photo, b.permissions, b.services"). Joins("JOIN users u on u.id = b.role_id") }).