1:N issue

[BastienVigneron]

Hello,

I'm trying to understand how to manage 1:N relationships, I've read all open issues on this subject but can't find a clear response.

For exemple :

pub struct Patient {
    pub id: Uuid,
    pub patient_id: String,
    pub patient_name: Option<String>,
    pub studies: Option<Vec<Study>>,
}

pub struct Study {
    pub id: Uuid,
    pub study_id: Option<String>,
    pub patient_id: Uuid,

}

Is there a way to retrieve a Patient and his studies via query_as!
If yes how ?

If not, is there a way to tell SQLX to ignore the studies field in Patient (and then fill them manually via a second query_as!) ?

[abonander]

SQLx doesn't inherently support 1:N queries like this, as that actually takes more than one query to accomplish most of the time and SQLx isn't that implicit.

One option, if you're fetching a single Patient, is to just query the Patient and related Study's separately and then combine them when you return them. Reusing the Patient type is only possible with Postgres currently as you could simply add an empty/null array to the query:

let patient = sqlx::query_as!(
    Patient, 
   r#"
        SELECT id, patient_id, patient_name, NULL::string[] as "studies: Vec<Study>
        FROM patients
        ...
    "#
)
    .fetch_one(&mut conn)
    .await?;

patient.studies = Some(sqlx::query_as!(Study, "SELECT ...").fetch_all(&mut conn).await?);

If you're fetching multiple Patients, each with their associated Studys, then it's possibly more efficient to combine them into one query (which will duplicate some data but saves the overhead of the extra query):

pub struct PatientStudy {
    patient_id: String,
    patient_name: Option<String>,
    study_id: Option<String>
}

let patient_studies = sqlx::query_as!(
    PatientStudy, 
    "SELECT patient_id, patient_name, study_id 
     FROM patients 
     INNER JOIN studies ON patients.id = studies.patient_id
     ..."
)
    .fetch_all(&mut conn)
    .await?;