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)

Comment From: 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

Comment From: mnussbaum

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

Comment From: shcw

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

I also had the same problem

Comment From: 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

Comment From: 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?

Comment From: ChaminW

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

Comment From: 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 😂

Comment From: shashankkumarIITB

Still facing the same issue.

Comment From: 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.

Comment From: 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