One to Many (1:N) clarification

[flopshot]

Hello. Don't mean to bother for clarification, but I can't seem to find a straight forward answer about sqlx support for One to Many deserialization.

I have a one to many relation between two tables:

CREATE TABLE consumable (
    id UUID NOT NULL DEFAULT uuid_generate_v1(),
    name VARCHAR(255),
    PRIMARY KEY (id)
);

CREATE TABLE serving (
    id SERIAL PRIMARY KEY,
    consumable_id UUID NOT NULL,
    amount DECIMAL(12,4) NOT NULL,
    kcal DECIMAL(12,4) NOT NULL,
    CONSTRAINT fk_consumable
          FOREIGN KEY(consumable_id)
    	  REFERENCES consumable(id)
    	  ON DELETE CASCADE
);

And these are the corresponding structs

#[derive(serde::Serialize, serde::Deserialize, sqlx::FromRow)]
#[sqlx(rename = "consumable")]
pub struct Consumable {
    pub id: Uuid,
    pub name: String,
    pub servings: Vec<Serving>
}

#[derive(sqlx::FromRow, serde::Serialize, serde::Deserialize, sqlx::FromRow)]
#[sqlx(rename = "serving")]
pub struct Serving {
    pub id: String,
    pub amount: BigDecimal,
    pub kcal: BigDecimal
}

Is there a way to write a query with query_as! that will directly write to an instance of Consumable

Something like this

pub async fn consumable_by_id(path: web::Path<String>, pool: web::Data<PgPool>) -> impl Responder {
    let  id = path.0;
    let consumable_or_error = sqlx::sqlx::query_as!(Consumable,
        r#"
        <SOME_QUERY_NOT_SURE_WHAT>
        WHERE consumable.id = $1
        "#,
        id
    )
    .fetch_one(connection.get_ref())
    .await;

    match consumable_or_error {
        Ok(consumable) => HttpResponse::Ok()
            .content_type("application/json")
            .json(consumable),
        Err(_) => HttpResponse::InternalServerError().finish(),
    }
}

Is this possible with sqlx or would I just have to query the tables separately and
compose the structs in rust?

[moh-abk]

Shouldn't it be query_as!(Consumable, "SELECT * FROM consumable WHERE id = $1", id) instead of = ?

[flopshot]

@abonander

What I've Tried
According to #714 it seems like this query should work:

    let consumable_or_error = query_as!(
        Consumable,
        r#"
        SELECT
            c.*,
            (
                s.id,
                s.consumable_id,
                s.amount,
                s.kcal,
            ) AS "servings: Vec<Serving>"
        FROM consumable AS c
        LEFT OUTER JOIN serving AS s
                ON c.id = s.consumable_id
        WHERE id = $1
        "#,
        id
    )
    .fetch_one(pool.get_ref())
    .await;

but I still get compilation errors. error returned from database: syntax error at or near ")"

From #298 , I'm not even sure if what I'm asking is possible but I'm pretty much stuck wither way.

[jayhuang75]

@flopshot any update on this? I have the same issues. seems the nested struct is not supporting even I use the sqlx::type

#[derive(Debug, Clone, FromRow)]
pub struct Note {
    pub id: String,                            
    pub level1: Vec<level2>, 
}

#[derive(Debug, FromRow, Type)]
pub struct leve2 {
    pub id: String,
    pub note: String,
}

Thanks a lot for the help.

[flopshot]

@jayhuang75 No I haven’t. Honestly, I gave up on this library as being too complicated for 1:Many relationships. And if you have something like 1:Many:Many relationships, forget it.

Just use Diesel. It is way more user friendly and has better documentation and blog posts. If you need to use it with async concurrency, follow this

[mehcode]

SQLx is a SQL library. We will not attempt to generate SQL and do not currently have a clean way to assist with these kinds of queries. You should make them the same you would in any raw SQL library.

• Query for the outer level items
• Query for the inner ones
• Use group_by to combine them

I'm going to close this only because we aren't planning to provide any kind of assistance here directly.

@flopshot @jayhuang75 Please look at https://docs.rs/ormx/0.2.0/ormx/ though for where this kind of functionality could be added.

[mehcode]

#856 (comment)

@flopshot your original error there is because you have an error in your SQL, you can't have a , ) see:

                s.kcal,
            ) AS "servings: Vec<Serving>"

[jayhuang75]

@mehcode @flopshot Thank you both for the response, really appreciated. I after some further dig in, I finally can manage the right out as expected. Thank you for your help again.

[flopshot]

I appreciate the response @mehcode.