sqlx structscan join query

Question

I'm new to go and sqlx and I would want to know if its posible to strutscan a master -> detail join clause in one query. A patient has many episodes so I want to query all patients and embed an Episode array in each Patient struct

Something like this:

type Episode struct {
        EpisodeID int    `db:"episode_id"`
        PatientID int    `db:"patient_id"`
        Status    string `db:"status"`
    }

    type Patient struct {
        PatientID    int            `db:"patient_id"`
        FirstName    string         `db:"first_name"`
        MiddleName   sql.NullString `db:"middle_name"`
        LastName     string         `db:"last_name"`
        Episodes     []Episode
    }

    query := `SELECT 
    mr_patients.patient_id, 
    mr_patients.first_name, 
    mr_patients.middle_name, 
    mr_patients.last_name, 
    mr_episode_statuses.status, 
    mr_episodes.episode_id, 
    mr_episodes.created_at 
    FROM mr_patients 
    INNER JOIN mr_episodes ON mr_patients.patient_id = mr_episodes.patient_id 
    INNER JOIN mr_episode_statuses ON mr_episodes.status_id = mr_episode_statuses.status_id
    ORDER BY mr_patients.patient_id, mr_episodes.episode_id`

    rows, err := db.Queryx(query)

    if err != nil {
        log.Fatal(err)
    }

    var patients []Patient

    for rows.Next() {
        var p Patient
        err = rows.StructScan(&p)
        if err != nil {
            log.Fatalln(err)
        } else {
            patients = append(patients, p)
        }
    }

Thanks.

Answer 1

It's not going to work since database/sql or sqlx aren't object relational mappers and don't know how to "populate" your type []Episodes. sqlx can assign single struct fields within a parent struct with the "structName.fieldName" syntax but that's it. It can't take all your rows, know what is what and assign all Episodes to the correct Patient. You need an ORM for that.