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

[alexisvisco]

GORM Playground Link

go-gorm/playground#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.

[alexisvisco]

Even with just :

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

It does not works

[github-actions]

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 ✨

[github-actions]

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 ✨

[github-actions]

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 ✨

[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.

[github-actions]

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 ✨

[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

[black-06]

I'll try to fix it.

[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.

[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.

[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 ?

[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.

[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 🙂

[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")

[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

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.

[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") }).