Using query_as! with a Vec<_> attribute

[rcelha]

I am having a hard time using the query_as! macro.

I have the following schema

CREATE TABLE IF NOT EXISTS foos (
    id BIGSERIAL PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS blas (
    id BIGSERIAL PRIMARY KEY,
    name text NOT NULL,
    foo_id BIGINT,
    CONSTRAINT fk_foo FOREIGN KEY (foo_id) REFERENCES foos (id) ON DELETE CASCADE
);

And these models:

struct Foo {
    id: i64,
    blas: Vec<Bla>,
}

struct Bla {
    id: i64,
    name: String,
}

I want to write a query that returns Foo and its Blas, but I have no idea how to do this.
I tried something like, but without success.

    let _foos = sqlx::query_as!(
        Foo,
        r#"
        SELECT
            f.id,
            (b.id, b.name) as "blas!: Vec<Bla>"
        FROM foos as f
        LEFT JOIN blas b ON b.foo_id = f.id
        "#
    )
    .fetch_all(&pool);

I'd appreciate some guidance here.

[nouman2075]

Try this

use sqlx::types::Json;

let _foos = sqlx::query_as!(
    Foo,
    r#"
        SELECT
            F.id,
            COALESCE(
                JSON_AGG(
                    JSON_BUILD_OBJECT(
                        'id', B.id,
                        'name', B.name
                    )
                ) FILTER (WHERE b.id IS NOT NULL),
                '[]'
            ) as "blas!: Json<Vec<Bla>>"
        FROM foos AS F
        LEFT JOIN blas AS B ON B.foo_id = F.id
    "#
)
.fetch_all(&pool);

[rcelha]

Thanks for trying to help.

First it complains about not having a group by:

error: error returned from database: column "f.id" must appear in the GROUP BY clause or be used in an aggregate function at line 1438
  --> src/main.rs:31:17
   |
31 |       let _foos = sqlx::query_as!(
   |  _________________^
32 | |         Foo,
33 | |         r#"
34 | |         SELECT
...  |
47 | |     "#
48 | |     )
   | |_____^
   |

If I add the GROUP BY, it gives me another error:

error[E0277]: the trait bound `Bla: serde::Deserialize<'de>` is not satisfied
   --> src/main.rs:32:17
    |
 32 |       let _foos = sqlx::query_as!(
    |  _________________^
 33 | |         Foo,
 34 | |         r#"
 35 | |         SELECT
...   |
 49 | |     "#
 50 | |     )
    | |_____^ unsatisfied trait bound
    |
help: the trait `serde_core::de::Deserialize<'_>` is not implemented for `Bla`
   --> src/main.rs:10:1
    |
 10 | struct Bla {
    | ^^^^^^^^^^
    = note: for local types consider adding `#[derive(serde::Deserialize)]` to your `Bla` type
    = note: for types from other crates check whether the crate offers a `serde` feature flag
    = help: the following other types implement trait `serde_core::de::Deserialize<'de>`:
              &'a JsonRawValue
              &'a Path
              &'a [u8]
              &'a str
              ()
              (T,)
              (T0, T1)
              (T0, T1, T2)
            and 161 others
    = note: required for `Vec<Bla>` to implement `serde_core::de::Deserialize<'_>`
    = note: required for `Json<Vec<Bla>>` to implement `sqlx::Decode<'_, Postgres>`
note: required by a bound in `try_get_unchecked`
   --> /Users/rcelha/.cargo/registry/src/index.crates.io-1949cf8c6b5b557f/sqlx-core-0.9.0/src/row.rs:156:12
    |
153 |     fn try_get_unchecked<'r, T, I>(&'r self, index: I) -> Result<T, Error>
    |        ----------------- required by a bound in this associated function
...
156 |         T: Decode<'r, Self::Database>,
    |            ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `Row::try_get_unchecked`
    = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

error[E0277]: the trait bound `Vec<Bla>: From<Json<Vec<Bla>>>` is not satisfied
  --> src/main.rs:32:17
   |
32 |       let _foos = sqlx::query_as!(
   |  _________________^
33 | |         Foo,
34 | |         r#"
35 | |         SELECT
...  |
49 | |     "#
50 | |     )
   | |_____^ the trait `From<Json<Vec<Bla>>>` is not implemented for `Vec<Bla>`
   |
   = help: the following other types implement trait `From<T>`:
             `Vec<T, A>` implements `From<BinaryHeap<T, A>>`
             `Vec<T, A>` implements `From<Box<[T], A>>`
             `Vec<T, A>` implements `From<VecDeque<T, A>>`
             `Vec<T>` implements `From<&[T; N]>`
             `Vec<T>` implements `From<&[T]>`
             `Vec<T>` implements `From<&mut [T; N]>`
             `Vec<T>` implements `From<&mut [T]>`
             `Vec<T>` implements `From<Cow<'_, [T]>>`
           and 7 others
   = note: required for `Json<Vec<Bla>>` to implement `Into<Vec<Bla>>`
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

Even if I add Deserialize to Bla I still get an error

error[E0277]: the trait bound `Vec<Bla>: From<Json<Vec<Bla>>>` is not satisfied
  --> src/main.rs:32:17
   |
32 |       let _foos = sqlx::query_as!(
   |  _________________^
33 | |         Foo,
34 | |         r#"
35 | |         SELECT
...  |
49 | |     "#
50 | |     )
   | |_____^ the trait `From<Json<Vec<Bla>>>` is not implemented for `Vec<Bla>`
   |
   = help: the following other types implement trait `From<T>`:
             `Vec<T, A>` implements `From<BinaryHeap<T, A>>`
             `Vec<T, A>` implements `From<Box<[T], A>>`
             `Vec<T, A>` implements `From<VecDeque<T, A>>`
             `Vec<T>` implements `From<&[T; N]>`
             `Vec<T>` implements `From<&[T]>`
             `Vec<T>` implements `From<&mut [T; N]>`
             `Vec<T>` implements `From<&mut [T]>`
             `Vec<T>` implements `From<Cow<'_, [T]>>`
           and 7 others
   

[nouman2075]

Yeah you need group by (sorry i forget to add it). Here's the complete tested code

use sqlx::types::Json;

struct Foo {
    id: i64,
    blas: Json<Vec<Bla>>,
}

#[derive(Deserialize)]
struct Bla {
    id: i64,
    name: String,
}

let foos = sqlx::query_as!(
    Foo,
    r#"
        SELECT
            F.id,
            COALESCE(
                JSON_AGG(
                    JSON_BUILD_OBJECT(
                        'id', B.id,
                        'name', B.name
                    )
                ) FILTER (WHERE b.id IS NOT NULL),
                '[]'
            ) as "blas!: Json<Vec<Bla>>"
        FROM foos AS F
        LEFT JOIN blas AS B ON B.foo_id = F.id
        GROUP BY F.id
    "#
)
.fetch_all(&pool)
.await?;

[rcelha]

Still some weirdness going on:

error[E0277]: the trait bound `Vec<Bla>: From<Json<Vec<Bla>>>` is not satisfied
  --> src/main.rs:21:16
   |
21 |       let foos = sqlx::query_as!(
   |  ________________^
22 | |         Foo,
23 | |         r#"
24 | |         SELECT
...  |
38 | |     "#
39 | |     )
   | |_____^ the trait `From<Json<Vec<Bla>>>` is not implemented for `Vec<Bla>`
 

thanks again

[rcelha]

@nouman2075 The only way I could get this working is with an intermediary representation of my object:

use sqlx::PgPool;

#[derive(Debug)]
struct Foo {
    id: i64,
    blas: Vec<Bla>,
}

#[derive(Debug)]
struct Bla {
    id: i64,
    name: String,
}

#[derive(Debug)]
struct FooBlaRow {
    id: i64,
    bla_id: Option<i64>,
    bla_name: Option<String>,
}

fn foos_from_row(rows: Vec<FooBlaRow>) -> Vec<Foo> {
    let mut foos: Vec<Foo> = vec![];
    for row in rows {
        if foos.last().map(|i| i.id) != Some(row.id) {
            foos.push(Foo {
                id: row.id,
                blas: vec![],
            });
        }
        if let (Some(id), Some(name)) = (row.bla_id, row.bla_name) {
            foos.last_mut().map(|i| i.blas.push(Bla { id, name }));
        }
    }
    foos
}

#[tokio::main]
async fn main() {
    let pool = PgPool::connect("postgres://test:test@localhost:25432/test")
        .await
        .unwrap();

    let rows = sqlx::query_as!(
        FooBlaRow,
        r#"
        SELECT
            F.id as "id!",
            B.id as "bla_id?",
            B.name as "bla_name?"
        FROM foos AS F
        LEFT JOIN blas AS B ON B.foo_id = F.id
    "#
    )
    .fetch_all(&pool)
    .await
    .unwrap();

    println!("Rows {:#?}", rows);
    let foos = foos_from_row(rows);
    println!("foos {:#?}", foos);
}