How can i build a subquery in a gorm belongs-to relationship?-CodePudding

I am using GORM in go for mysql queries. I have the following structs

type Patient struct {
    gorm.Model
}


type ClinicalTrialPatient struct {
    gorm.Model
    PatientID uint
    Patient Patient
}

I would like go generate a SQL Statement like

SELECT * FROM `clinical_trial_patients` WHERE patient_id IN (SELECT ID FROM Patients WHERE pvs_pat_id = '1' AND DELETED_AT is null) AND `clinical_trial_patients`.`deleted_at` IS NULL

Unfortunately I dont get it how i can generate a statement like this. I was trying something like this:

result := orm.Where(&ClinicalTrialPatient{
        Patient: Patient{ 
            PvsPatId: "1",
        },
    }).Find(&trialPatients)

But the following statement was generated

SELECT * FROM `clinical_trial_patients` WHERE `clinical_trial_patients`.`deleted_at` IS NULL

CodePudding user response：

You can use Preload to do belongs-to relationship.

type Patient struct {
    gorm.Model
    ClinicalTrialPatients []ClinicalTrialPatient // add this field
}


type ClinicalTrialPatient struct {
    gorm.Model
    PatientID uint
    Patient Patient
}

...

var result Patient

err := orm.Preload("ClinicalTrialPatients").First(&result, 1).Error
if err != nil {
    panic(err)
}

fmt.Println(result.ClinicalTrialPatients)

...

Or based on your question, you can use sub-query like this:


    pvsPatId := "1"

    err := orm.
        Where(
            "patient_id in (?)",
            db.Table("Patients").
                Select("id").
                Where("pvs_pat_id = ?", pvsPatId),
        ).
        Find(&trialPatients).Error
    if err != nil {
        panic(err)
    }