Preload with Joins Can Cause Naming Collisions

[moneszarrugh]

There's an issue that can make the primary key column ambiguous when using Prelaod.

Here's an exaple of "Has Many" where each "transaction" has many "payments"

db.Preload("Transactions", func (db *gorm.DB) *gorm.DB {
	return db.
		Table("transactions t").
		Select("t.*, SUM(p.amount) paid").
		Joins("LEFT JOIN payments p ON t.id = p.transaction_id").
		Group("t.id")
} )

This would generate the following query:

SELECT t.*, SUM(p.amount) paid FROM transactions t LEFT JOIN payments p ON t.id = p.transaction_id WHERE (id IN (4,8,6)) GROUP BY t.id

Note the "WHARE" clause. This query will fail if both "transactions" and "payments" had the same column name "id" for their primary key.

The workaround is to rename one of the column names, for example change "id" in "payments" to be "payment_id". But this is limiting and non conventional.

The optimal solution is to use the fully qualified name of the primary key in the where clause, which should generate this sql instead:

SELECT t.*, SUM(p.amount) paid FROM transactions t LEFT JOIN payments p ON t.id = p.transaction_id WHERE (transactions.id IN (4,8,6)) GROUP BY t.id

[github-actions]

This issue will be automatically closed because it is marked as GORM V1 issue, we have released the public testing GORM V2 release and its documents https://v2.gorm.io/docs/ already, the testing release has been used in some production services for a while, and going to release the final version in following weeks, we are still actively collecting feedback before it, please open a new issue for any suggestion or problem, thank you

Also check out https://github.com/go-gorm/gorm/wiki/GORM-V2-Release-Note-Draft for how to use the public testing version and its changelog