Allow for inner or right join in Join preloading

[williamfinn]

Describe the feature

Would like to be able to specify a Join type / direction when using Joins Preloading.

Motivation

Currently the documentation says that an "inner" join is carried out but in fact a left join is carried out. I think that it would be an excellent feature to have other join types allowed for join preloading for example: right joining between multiple entities will only return a row if all of the right joins are satisfied.

Code example

type Card struct {
	Id           int          `json:"id,omitempty" gorm:"column:id"`
	Amount       float64      `json:"amount,omitempty" gorm:"column:amount"`
	Currency     string       `json:"currency,omitempty" gorm:"column:currency"`
	GiftCard     Giftcard     `json:"giftCard,omitempty" gorm:"foreignKey:CardId"`
	CardConsumer CardConsumer `json:"consumer,omitempty" gorm:"foreignKey:CardId"`
}

// note that the entity name is specified "GiftCard" and "CardConsumer" to facilitate join preloading
r.db.Joins("right join GiftCard").Joins("right join CardConsumer").Find(&card)

[github-actions[bot]]

This issue has been automatically marked as stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days

[mnussbaum]

This is indeed a discrepancy between the docs and the code, and the ability to do inner joins would be very helpful

[shcw]

@jinzhu 大佬 这个问题还挺有帮助的 想要使用关联关系的时候 right join 怎么操作呀

I also had the same problem

[williamfinn]

On second thought, doesn't make much sense for right join preloading (as you could have no parent object to preload on). If you want to simulate inner or right joins currently I suggest just doing this in where conditions (CardConsumer.id != null for example). Ability to do inner join would be a useful feature though

[tbistr]

Why does gorm replace "JOIN" with "LEFT JOIN"? Just "JOIN" means "INNER JOIN" in MySQL and PostgressSQL at least.

I had the same problem.

minimam sample

type A struct {
	ID uint
	B  B
}

type B struct {
	ID   uint
	AID  uint
	Data string
}

db.Create(&A{B: B{Data: "data1"},})
db.Create(&A{B: B{Data: "data2"},})
db.Create(&A{B: B{Data: "data3"},})
As := []A{}
db.Debug().Joins("B", db.Where(&B{Data: "data3"})).Find(&As)
fmt.Println(As)

This code execute SQL like bellow.

SELECT `as`.`id`,`B`.`id` AS `B__id`,`B`.`a_id` AS `B__a_id`,`B`.`data` AS `B__data` FROM `as` LEFT JOIN `bs` `B` ON `as`.`id` = `B`.`a_id` AND `B`.`data` = 'data3'

Then returns [{1 {0 0 }} {2 {0 0 }} {3 {3 3 data3}}]. And just remove "LEFT" solves this problem, returns [{3 {3 3 data3}].

I think the default should be "JOIN" instead "LEFT JOIN". Are there any issues that I'm not taking into account?

[ChaminW]

As @tbistr mentioned, shouldn't it be "JOIN" as the default instead "LEFT JOIN" for .Join()?

[shcw]

On second thought, doesn't make much sense for right join preloading (as you could have no parent object to preload on). If you want to simulate inner or right joins currently I suggest just doing this in where conditions (CardConsumer.id != null for example). Ability to do inner join would be a useful feature though

but it still feels useful 😂

[shashankkumarIITB]

Still facing the same issue.

[delvatt]

Facing the same issue. we definitely need more clarity on this, either by specifying in documentation that all JOINS are in fact LEFT JOINS or by implementing the ability to specify the JOIN nature/direction.

[OskarsPakers]

Inner joins are supported with .InnerJoins method https://github.com/go-gorm/gorm/pull/5583/files I have submitted PR to add it to documentation https://github.com/go-gorm/gorm.io/pull/635