Gorm How to preload in many to many with the condition

Your Question

let me explain the problem with an example i have user and course in many to many relationship. every user can buy many courses and a course can belong to many users every bought course has a expiration date on many to many relationship

type User struct {
    ...
    Courses     []*Course     `gorm:"many2many:course_user;"`
}
type Course struct {
    ...
    Users              []*User    `json:"-" gorm:"many2many:course_user;"`
}
type CourseUser struct {
    // the gorm:foreignKey just creates a relationship between tables and works just fine
    UserID         int `gorm:"foreignKey:ID"`
    CourseID       int `gorm:"foreignKey:ID"`
    ExpirationDate time.Time
}

it works in preload like this:

D.DB().Model(&user).
        Preload("Courses").
        Find(&user)

and returns the user with all of bought courses but when i want to preload only courses that their expiration_date is > now i like this i get error:

D.DB().Model(&user).
        Preload("Courses", "expiration_date > ?", time.Now()).
        Find(&user)

error:

ERROR: column "expiration_date" does not exist (SQLSTATE 42703)
SELECT * FROM "courses" WHERE "courses"."id" = 69 AND expiration_date > '2023-07-23 23:59:41.564'

Expected answer

preload all user's courses with many to many expiration_date > now with sql like this

SELECT * FROM "course_user" WHERE "course_user"."user_id" = 9 AND expiration_date > '2023-07-23 23:59:41.564'

Comment From: saeidee

You can use join and perload together.

Comment From: mostafa-binesh

You can use join and perload together.

Can you give me the code based on my example

Comment From: shanel262

@mostafa-binesh Did you ever find an answer to this? It still isn't properly documented and I've been searching for a solution all morning

Comment From: tomassommareqt

@mostafa-binesh same here.

Comment From: mostafa-binesh

it's been a while so i don't remember how i handled it but as far as i remember, you cannot join these tables easily, so you have to get the data directly from the many2many table and handle it accordingly, like this:

// i want all bought courses from user X. i cannot join it with user model, i have to get the bought courses ids first and then handle it accordingly
var courseIDs []uint
if err := D.DB().Model(&CourseUser{}).Where("user_id = ? AND expiration_date > ?", userID, time.Now()).
        Pluck("course_id", &courseIDs).Error; err != nil {
        return nil, err
}

@tomassommareqt @shanel262

Comment From: fari-99

@mostafa-binesh so it didn't use preload at all? but you collect the course id first, then on preload you put that on the conditions preload? seems strange to me, why we cant add conditions to preload many2many for that join table.