How to query a one to many relationship with go-pg

Question

I would like to query a one to many relationship. I have the following structs:

type AppointmentsParticipants struct {
    AppointmentsID int `sql:",pk"`
    UserID int `sql:",pk"`
    Approved bool
    ReviewedAt time.Time
    ReviewedBy int
    Comment string
    Cancelled bool

}

type Appointments struct {
    ID int `sql:",pk"`
    Pending bool
    StartTime time.Time
    EndTime time.Time
    auditData
    InitialAppointmentID int
    SessionID string
    Type string
    AppointmentParticipants []*AppointmentsParticipants `json:"participants"`
}

I'm writing the query as follows:

var app Appointments
err = database.DB().
        Model(&app).
        Column("*").
        Where("appointments_participants.user_id = ?", id).
        Join("JOIN appointments_participants ON appointments.id = appointments_participants.appointments_id").
        Select()

This does return all values for the Appointments struct with the exception of the AppointmentParticipants slice. It returns an empty slice. I've taken the query which go-pg writes and verified in psql that it does return a record from the appointment_participants table.

This is the output: {140 true 2018-09-01 00:00:00 +0000 UTC 2018-09-01 01:00:00 +0000 UTC {2018-08-15 22:52:11.326775 +0000 UTC 5 0001-01-01 00:00:00 +0000 UTC 0} 0 Session []}

Interestingly enough this query does return an error: pg: can't find column=appointments_id in model=Appointments However I have tried using struct tags to try and resolve this but to no avail.

Answer 1

Try query like this:

err = database.DB().
    Model(&app).
    Column("AppointmentsParticipants").
    Relation("AppointmentsParticipants", func(q *orm.Query) (*orm.Query, error) {
        return q.Where("user_id = ?", id), nil
    }).
    Select()

If it does not work, try to work with ids, their field names and tags, go-pg is very sensitive to this.