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

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

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)
	}