[SOLVED] Use INNER JOIN instead of LEFT JOIN when using gorm .Joins

Use INNER JOIN instead of LEFT JOIN when using gorm .Joins

I have the following structs:

type User struct {
     ID   uuid.UUID `gorm:"type:uuid"` 
     BirthDate *BirthDate `gorm:"<-"`
     BirthDateID uuid.UUID  
}

type BirthDate struct {
    ID   uuid.UUID `gorm:"type:uuid"` 
    Year int
    Month int
    Day int
}

(Those are make up structs). So basically there is a one to one relationship between user and birthdate, let's assume two users can't be born the same day.

I want to be able to retrieve all the users that were born in 2022, so then I have the following:

var result []*User
birthDateExample := BirthDate{Year:2022}
DB.Debug().Joins("BirthDate", s.Database.Select("id").Where(birthDateExample)).Preload(clause.Associations).Find(&result)

But it's making a left join query, so I am getting more results that I want:

SELECT `users`.`id`,`users`.`birth_date_id`,`BirthDate`.`id` AS `BirthDate__id`,`BirthDate`.`year` AS `BirthDate__year`,`BirthDate`.`month` AS `BirthDate__month`,`BirthDate`.`day` AS `BirthDate__day` FROM `reports` LEFT JOIN `birth_dates` `BirthDate` ON `users`.`birth_date_id` = `BirthDate`.`id` AND `BirthDate`.`year` = "2022"

How can I specify that I want a inner join? Because if I write the query as the following it works:

DB.Debug().Joins("JOIN birth_dates on birth_dates.id = users.billing_month_id and birth_dates.year = ?", 2022).Preload(clause.Associations).Find(&result)

But I would rather use the previous approach, Joins("BirthDates", query).

Solution

The behavior of Joins is to do a LEFT JOIN, for whatever reason. To get an inner join instead, you can use the InnerJoins method:

DB.Debug()
  .InnerJoins("BirthDate", s.Database.Select("id").Where(birthDateExample))
  .Preload(clause.Associations)
  .Find(&result)