N+1 Queries with Joins("Company").Preload("Company.Addresses") on Addresses

[alexisvisco]

GORM Playground Link

go-gorm/playground#722

Version

Superior to v1.25.6

Description

This issues to demonstrate an issue in GORM's query behavior.

The function illustrates potential inefficiencies and unexpected results that may have been introduced due to a previous fix in GORM's preloading and join operations.

Background

Previously, a fix was implemented in GORM to address an issue where, when joining on Account.Pet and then preloading Account.Companies, the account was loaded twice, even though it was already present in the main query. This fix aimed to improve the consistency and efficiency of the query process.

Current Issue

However, the provided playground suggests that the fix may have unintentionally introduced another issue. The test highlights the following:

Unexpected Queries: The function is expected to execute two queries (one for fetching user data and another for related data). Instead, there may be an additional, unnecessary query, suggesting an n+1 query problem.

TLDR:

DB.Joins("Company").Preload("Company.Addresses").Find(&result).Error

is resulting in

2024/04/23 15:08:19 /Users/alexisviscogliosi/dev/go-playground/db.go:59
[1.205ms] [rows:2] SELECT * FROM "addresses" WHERE "addresses"."company_id" = 1

2024/04/23 15:08:19 /Users/alexisviscogliosi/dev/go-playground/db.go:59
[0.634ms] [rows:2] SELECT * FROM "addresses" WHERE "addresses"."company_id" = 2

2024/04/23 15:08:19 /Users/alexisviscogliosi/dev/go-playground/db.go:59
[3.667ms] [rows:2] SELECT "users"."id","users"."created_at","users"."updated_at","users"."deleted_at","users"."name","Company"."id" AS "Company__id","Company"."name" AS "Company__name","Company"."user_id" AS "Company__user_id" FROM "users" LEFT JOIN "companies" "Company" ON "users"."id" = "Company"."user_id

Note: if there is more than 2 companies it results in N queries where N is the number of companies.

Instead of

SELECT `users`.`id`,`users`.`created_at`,`users`.`updated_at`,`users`.`deleted_at`,`users`.`name`,`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name`,`Company`.`user_id` AS `Company__user_id` FROM `users
		LEFT JOIN `companies` `Company` ON `users`.`id` = `Company`.`user_id`
		WHERE `users`.`deleted_at` IS NULL
SELECT * FROM `companies` WHERE `companies`.`id` IN (1, 2)

In version v1.25.6 the select of addresses where using a IN clause but have the problem of doing a second query with companies (#6715 (comment))