How to work with nested struct

[ikrivosheev]

Greetings and thanks for your project!

Could you tell me how can I make this structure for query response?

struct Image {
    url: String,
    width: i64,
    heigth: i64
}

struct Profile {
    id: i64,
    image: Option<Image>
}

struct User {
    id: i64,
    profile: Profile
}

I try this query, but this is not the correct syntax for postgres:

let user = sqlx::query_as!(User, r#"
    SELECT u.id (p.id, (i.url, i.width, i.heigth) as "image!: Image") as "profile!: Profile"
    FROM users u
    JOIN profile p ON u.id = p.user_id
    JOIN image i ON p.image_id = i.id
    WHERE u.id = $1"#, 1).fetch_one(&pool).await.unwrap();

I can convert image to json in query or make flat struct for query result and then in rust convert to nested , but maybe there is another solution to the problem?

[Overdash]

Wish there was an answer to this - been trying to figure it out for months. SQLx doesn't support flattening right now so I don't think it's possible with SQLx at the moment :(

[ikrivosheev]

Thank you for response.

[enheit]

@ikrivosheev did you find a solution?

[bk138]

You can implement the from-row function yourself, roughly like so (in this case, the data is stored flat in the 'cars' table):

 
    fn car_from_row(row: PgRow) -> Car {
        // map the row into a user-defined domain type
        let config = CarConfig {
            config_one: row.get("config_one"),
            config_two: row.get("config_two"),
        };
        Car {
            id: row.get("id"),
            name: row.get("name"),
            config,
        }
    }

   // Get a Car is then
   pub async fn Car(&self, car_id: i64) -> Result<Car> {
        let Car = sqlx::query("SELECT * FROM cars WHERE id = $1")
            .bind(car_id)
            .map(car_from_row)
            .fetch_one(&self.pool)
            .await?;
        Ok(Car)
   }

[ikrivosheev]

@enheit hello! I think sqlx is good runtime for database. At this moment i am working in https://github.com/SeaQL and create issue: SeaQL/sea-orm#739 for support these opportunities

[nikolal]

Hey @ikrivosheev ,

I know this is an old issue but do you know if this is possible now using query_as! ?

[Overdash]

Here with an update - you can use #[sqlx(flatten)] macro (see Docs) on fields that also implement FromRow. @nikolal I'd imagine this approach would also work with the query_as! macro - but I haven't tested it (only used it with the trait fn).

NB: Be careful of conflicting fields, lets say you flatten 3 structs with an overlapping field name (e.g. id being a common one), you might get unexpected results - in my case I found every flattened struct had the same value for the overlapping field. (And the value always resolved to the last declared type in the struct) e.g. (contrived example)

#[derive(sqlx::FromRow)]
struct Foo {
  a: i32,
  b: i32,
}

#[derive(sqlx::FromRow)]
struct Bar {
  a: i32,
  c: i32,
}

#[derive(sqlx::FromRow)]
struct Baz {
  a: i32,
  d: i32,
}

#[derive(sqlx::FromRow)]
struct FooBarBaz {
  #[sqlx(flatten)]
  foo: Foo,

  #[sqlx(flatten)]
  bar: Bar,

  #[sqlx(flatten)]
  baz: Baz,
}

Since field a is present in all of them, and of the same type. foo.a and bar.a will get the same value as baz.a (because it's declared last in FooBarBaz. I'm not sure if this behaviour has changed, but this is was I found back in August 2023 when testing some stuff.

[jonnyso]

Sadly that wont work with the query_as! macro :(

Its also, as far as I can tell, not suited to use with @enheit approach, as it doesn't return a Row at all, its an anonymous struct that I don't know how to work with so far.

This macro does not use FromRow; in fact, no trait implementations are required at all, though this may change in future versions.

https://docs.rs/sqlx/latest/sqlx/macro.query_as.html

[sebtomba]

@jonnyso

Sadly that wont work with the query_as! macro :(

However, it should work with the query_as() function, which sets a FromRow constraint on the mapped Rust type.