Create nested Structures with query_as function

[jb-alvarado]

Is your feature request related to a problem? Please describe.
The problem is a bit related to: #1014 so I will use the same example code.
With macro query_as! it is possible to create nested structs, like:

#[derive(Debug, sqlx::FromRow)]
struct AccountNoPost {
    account_id: i64,
    user_name: String,
    address: Address,
}

#[derive(Debug, sqlx::Type, sqlx::FromRow)]
struct Address {
    address_id: i64,
    street: String,
    street_number: String,
    city: String,
    country: String,
}

And with this query:

let account = sqlx::query_as!(AccountNoPost, r#"
    select ac.account_id, ac.user_name,
      (ad.address_id, ad.city, ad.country, ad.street, ad.street_number) as "address!: Address"
    from accounts as ac
    join address as ad using(address_id)
    where ac.account_id = 1;
    "#).fetch_one(pool).await?;

The problem here is, that is not possible to build dynamic query, for example: I use this query in a API, and with the route query, the user can choice which fields he wants to get, or which field he wants to use for the order.

Describe the solution you'd like
I would be nice, if it would also be possible to create nested structs with the query_as() function. Then we can build our query string in a separate step outside and add/subtract functionality.

Describe alternatives you've considered
The only alternative I see at the moment is, to create different functions with fixed query strings and stick with the macro.

[frederikhors]

Did you find a way?

[jb-alvarado]

Yes, you can check my answer here: #1014 at the end.

You need to create your own FromRow trait, is not to complicate.

[frederikhors]

@jb-alvarado I finally tried your code today: it works! But I also found out something terrible: #2291.

The limit for the tuple fields is 9!

I have many bigger structs.

Can I ask you what are currently using? Still this solution?

Is there a way to not us tuple at all?

[jb-alvarado]

@frederikhors, yes I run into same issue :-). You can use instead:

row.get::<Vec<Option<(..., ..., ...)>>, &str>("big_table")

This:

row.get_unchecked::<Vec<Option<BigTableSerializer>>, &str>("big_table")

BigTableSerializer represent a struct, with all needed fields.

I had also to implement sqlx::decode::Decode and sqlx::Type<::sqlx::Postgres> for BigTableSerializer, but maybe for you is not necessary.

[frederikhors]

I'm having this error if I try to use struct instead of a tuple:

I tried using a struct (the same struct I'm trying to retrieve). But I have this error now:

error[E0277]: the trait bound `pg::PgCoach: sqlx::Decode<'_, sqlx::Postgres>` is not satisfied
   --> src\pg.rs:628:21
    |
628 |                 id: row.get_unchecked::<PgCoach, &str>("coach").id,
    |                     ^^^ ------------- required by a bound introduced by this call
    |                     |
    |                     the trait `sqlx::Decode<'_, sqlx::Postgres>` is not implemented for `pg::PgCoach`
    |
    = help: the following other types implement trait `sqlx::Decode<'r, DB>`:
              <&'r [u8] as sqlx::Decode<'r, sqlx::Postgres>>
              <&'r [u8] as sqlx::Decode<'r, sqlx_core::any::database::Any>>
              <&'r sqlx::types::JsonRawValue as sqlx::Decode<'r, DB>>
              <&'r str as sqlx::Decode<'r, sqlx::Postgres>>
              <&'r str as sqlx::Decode<'r, sqlx_core::any::database::Any>>
              <() as sqlx::Decode<'r, sqlx::Postgres>>
              <(T1, T2) as sqlx::Decode<'r, sqlx::Postgres>>
              <(T1, T2, T3) as sqlx::Decode<'r, sqlx::Postgres>>
            and 43 others
note: required by a bound in `sqlx::Row::get_unchecked`
   --> C:\Users\Fred\.cargo\registry\src\github.com-1ecc6299db9ec823\sqlx-core-0.6.2\src\row.rs:91:12
    |
91  |         T: Decode<'r, Self::Database>,
    |            ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `sqlx::Row::get_unchecked`

So I think I need to implement Decode, right? But how?

The code I'm using is the same as in this question: #2292

[frederikhors]

Thank you very much for your help. I would like to pay you if you want.

[frederikhors]

Can you show me part of your code? To better understand the BigTableSerializer part?

[frederikhors]

Isn't #[derive(sqlx::FromRow)] enough on PgCoach struct?

[frederikhors]

This is the code I'm using:

As you can see I'm using a Box<T> for struct fields.

use sqlx::{postgres::PgRow, FromRow, QueryBuilder, Row};

// PgCoach has ONLY scalar values

#[derive(Debug, Default, sqlx::FromRow)]
pub struct PgCoach {
    pub team_id: String,
    pub id: String,
    pub created_at: Time::OffsetDateTime,
    pub updated_at: Option<Time::OffsetDateTime>,
    pub firstname: Option<String>,
    pub lastname: Option<String>,
    pub motto: Option<String>,
    pub first_case: Option<String>,
    pub second_case: Option<String>,
    pub third_case: Option<String>,
    pub birth_date: Option<Time::OffsetDateTime>,
    pub sex: Option<i64>,
    pub phone: Option<String>,
    pub email_address: Option<String>,
    pub address: Option<String>,
    pub picture: Option<String>,
    pub notes: Option<String>,
}

#[derive(Debug, Default)]
pub struct PgPlayer {
    pub team_id: String,
    pub id: String,
    pub created_at: Time::OffsetDateTime,
    pub updated_at: Option<Time::OffsetDateTime>,
    pub score: i64,
    pub birth_date: Time::OffsetDateTime,
    pub code: String,
    pub payed: bool,
    pub coach_id: String,
    pub coach: Option<Box<PgCoach>>,
    pub skills: Option<Vec<PgSkill>>,
}

impl FromRow<'_, PgRow> for PgPlayer {
    fn from_row(row: &PgRow) -> sqlx::Result<Self> {
        let mut res = Self {
            team_id: row.get("team_id"),
            id: row.get("id"),
            created_at: row.get("created_at"),
            updated_at: row.get("updated_at"),
            score: row.get("score"),
            birth_date: row.get("birth_date"),
            code: row.get("code"),
            payed: row.get("payed"),
            coach_id: row.get("coach_id"),
            ..Default::default()
        };

        if row.try_get_raw("coach").is_ok() {
            res.coach = Some(Box::new(PgCoach {
                id: row.get_unchecked::<PgCoach, &str>("coach")?.1,
                firstname: row.get_unchecked::<PgCoach, &str>("coach")?.4,
                lastname: row.get_unchecked::<PgCoach, &str>("coach")?.5,
                ..Default::default()
            }));
        }

        Ok(res)
    }
}

but I'm getting that error.

[jb-alvarado]

Sorry, this evening I have not so much time. You can use cargo-expand to get details about sqlx traits. You can run it like: cargo expand --lib db::serializer.

First I would try to use a sqlx::Type on your PgCoach, when there are coming still errors (I don't remember which ones), then you need to create your own decoder. I would use this Type on a simple struct, run cargo expand ... and see how the decoder works, and apply it to PgCoach.

Here an example from me:

impl<'r> ::sqlx::decode::Decode<'r, ::sqlx::Postgres> for ProgramBlockEntrySerializer {
    fn decode(
        value: ::sqlx::postgres::PgValueRef<'r>,
    ) -> ::std::result::Result<
        Self,
        ::std::boxed::Box<
            dyn ::std::error::Error + 'static + ::std::marker::Send + ::std::marker::Sync,
        >,
    > {
        let mut decoder = ::sqlx::postgres::types::PgRecordDecoder::new(value)?;
        let id = decoder.try_decode::<i64>()?;
        let block_id = decoder.try_decode::<i64>()?;
        let start = decoder.try_decode::<DateTime<Utc>>()?;
        let special = decoder.try_decode::<bool>()?;
        let visible = decoder.try_decode::<bool>()?;
        let length = decoder.try_decode::<i32>()?;
        let backup_duration = decoder.try_decode::<i32>()?;
        let backup_name = decoder.try_decode::<Option<String>>()?;
        let backup_series = decoder.try_decode::<Option<String>>()?;
        let backup_speaker = decoder.try_decode::<Option<String>>()?;
        let backup_thumbnail = decoder.try_decode::<Option<String>>()?;
        let backup_type_id = decoder.try_decode::<i32>()?;
        let media_id = decoder.try_decode::<Option<i32>>()?;
        ::std::result::Result::Ok(ProgramBlockEntrySerializer {
            id,
            block_id,
            start,
            special,
            visible,
            length,
            backup_duration,
            backup_name,
            backup_series,
            backup_speaker,
            backup_thumbnail,
            backup_type_id,
            media_id,
        })
    }
}

impl ::sqlx::Type<::sqlx::Postgres> for ProgramBlockEntrySerializer {
    fn type_info() -> ::sqlx::postgres::PgTypeInfo {
        ::sqlx::postgres::PgTypeInfo::with_name("ProgramBlockEntrySerializer")
    }
}

Bit of background: Which I understand there is a compiler bug, which hinder compilation because of Option<String> and maybe other types, for this is the workaround. There are some issues, which are related to that:

#1268
#1031
rust-lang/rust#82219

[frederikhors]

Thanks for the reply, I had already written the code yesterday looking for it in the issues.

Also I think it's a bug because it doesn't make sense that it doesn't work if it works by deriving the sqlx::FromRow.

Anyway I asked on rust-lang.org forum about this and a user answered saying this is not a great way to handle these kind of SQL situation.

Quoting:

There's not really a great way to structure this kind of code just due to the nature of SQL.

The problem you're going to run into is that no matter how you write the query, you're going to have column name conflicts (both PgCoach and PgPlayer have columns named id, for example).

You could rename the columns so there's no overlap between the column names of the two tables, make a query that JOINs the coach table on the foreign key, and then just call PgCoach's FromRow implementation directly in PgPlayer's impl. That doesn't really scale though. The other problem with this approach is that you'll decode multiple copies of the same coach, one for each player that has that coach (and of course, receive multiple copies of that row data from the database server). That may not be what you want.

The most common approach to solving this problem in general looks more like an ORM. You decode all the basic table values from the query, then go through and find all the ids of rows from other tables you need to fill in the object graph, make those queries, and then use the results of all those queries to build up an object graph.

It seems to me that he is talking about multiple queries instead of just one. So far my searches have been directed towards mono-big-query instead of mutliple-small ones.

A few minutes ago I was also reading the diesel documentation about associations for the first time and it talks about the same approach:

The same as SeaORM does:

What do you think?

[jb-alvarado]

I'm really not an DB nor Rust expert, so do not take everything at face value what I say. Yes Many to Many request are sub-optimal in single requests, but sometime still the better option (specially in combination with postgres array_agg). Before I switch from diesel to sqlx I had the same problem there: diesel-rs/diesel#3350. After switching I also notice, that sqlx is slightly slower, but I'm still happy that I'm using now sqlx and not diesel.

Before I started using Rust, I used python django and SQL-Alchemy. Using ORMs have some downsides and everybody have to weigh the pros and cons for him self. What I like by sqlx is:

• you don't have to learn another framework
• you get a better understanding about SQL and whats going on, on you database.
• you have more freedom and no framework gives you restrictions.
• you can build dynamical your requests. For me is a big pro for Backends which serves Web Apps.

[absolutejam]

I've also been struggling with this as I like to fetch nested records (1-n) with LEFT JOIN LATERAL.

As a workaround for the time-being, you can use json_agg. Be aware that this will add some performance overhead because the DB engine must wrap it in JSON and then sqlx must decode it, but this is the most ergonomic solution I've found so far that doesn't deviate away from my usual pattern.

// models.rs

#[derive(Debug, Serialize, Deserialize, PartialEq, Eq, ToSchema, FromRow)]
pub struct LocationModel {
    pub id: Uuid,
    pub chronicle_id: Uuid,
    pub default_version_id: Uuid,

    pub versions: Vec<LocationVersionModel>,
}

#[derive(Debug, Serialize, Deserialize, PartialEq, Eq, ToSchema)]
pub struct LocationVersionModel {
    pub id: Uuid,
    pub name: String,
    pub content: Option<String>,

    pub created_at: DateTime<Utc>,
    pub created_by: Uuid,
    pub updated_at: Option<DateTime<Utc>>,
    pub updated_by: Option<Uuid>,
    pub deleted_by: Option<Uuid>,
    pub deleted_at: Option<DateTime<Utc>>,
}

//------------------------------------------------------------------------------

// queries.rs

use sqlx::types::Json;

// Intermediate struct just for the query results, but you could also
// use it instead of the original if you don't mind accessing into the `Json` tuple
// (this should also be transparent when being sent back as an API response)

#[derive(Debug, Serialize, Deserialize, PartialEq, Eq)]
struct Location {
    pub id: Uuid,
    pub chronicle_id: Uuid,
    pub default_version_id: Uuid,

    pub versions: Json<Vec<LocationVersionModel>>,
}

pub async fn get_locations(
    conn: impl PgExecutor<'_>,
    chronicle_id: &ChronicleId,
    limit: i64,
) -> Result<Vec<LocationModel>, sqlx::Error> {
    let locations = sqlx::query_as!(
        Location,
        r#"
            SELECT
                l.id,
                l.chronicle_id,
                l.default_version_id,

                json_agg(lv) AS "versions: Json<Vec<LocationVersionModel>>"
            FROM locations AS l
            LEFT JOIN LATERAL (
                SELECT * 
                FROM location_versions AS lv
                WHERE l.id = lv.location_id
            ) AS lv ON TRUE
            WHERE
                l.chronicle_id = $1
            GROUP BY l.id, l.chronicle_id, l.default_version_id
            LIMIT $2
            "#,
        chronicle_id.into_inner(),
        limit,
    )
    .fetch_all(conn)
    .await?;

    println!("Locations: {:#?}", locations);

    // You could implement `Into<_>`
    Ok(locations
        .into_iter()
        .map(|l| LocationModel {
            id: l.id,
            chronicle_id: l.chronicle_id,
            default_version_id: l.default_version_id,
            versions: l.versions.0,
        })
        .collect())
}

EDIT: An easier way is to json_agg inside the subquery so it doesn't force you to GROUP BY in the outer query.

 SELECT
      l.id,
      l.chronicle_id,
      l.default_version_id,

      lv.versions AS "versions!: Json<Vec<LocationVersionModel>>"
  FROM locations AS l
  LEFT JOIN LATERAL (
      SELECT 
          coalesce(json_agg(lv.*), '[]'::json) AS versions
      FROM location_versions AS lv
      WHERE l.id = lv.location_id
  ) AS lv ON TRUE
  WHERE
      l.chronicle_id = $1
  LIMIT $2

🤔 And at this point it might just be doable as a LEFT JOIN/INNER JOIN

[musjj]

How about a simpler approach by using dot notation instead:

struct Bar {
    b: String,
}

struct Foo {
    a: String,
    bar: Bar,
}

sqlx::query_as!(
    Foo,
    r#"
    SELECT
        a,
        b as "bar.b"
    FROM
        foobars
    "#
)
.fetch_one(&pool)
.await?

Here b is aliased as bar.b, which is interpreted as the equivalent of Foo.bar.b = b. I feel that this is more intuitive.