How to map SQLx pivot table relationship to rust Vec using sqlx

I have a user obj

#[derive(Serialize, Deserialize, FromRow)]
pub struct User {
    pub uid: Uuid,
    pub name: String,
    pub username: Option<String>,
    pub email: Option<String>,
    pub provider: String,
       *.... other fields*
}

And a role obj

#[derive(Serialize, Deserialize, FromRow)]
pub struct Role {
    pub uid: Uuid,
    pub name: String,
}

I want to get a user with its roles Vec<Role> like :

struct User {
  user: User,
  roles: Vec<Role>
}

this is my Postgres SQL

        let user_with_roles: UserWithRoles = sqlx::query(
            r#"
            SELECT users.*, ARRAY_AGG(roles.*) AS roles of type Vec<Role>
            FROM users 
            JOIN users_roles ON users_roles.user_uid = users.uid
            JOIN roles ON users_roles.role_uid = roles.uid
            WHERE users.uid = $1
            GROUP BY users.uid;
            "#,
        )
        .bind(self.uid)
        .fetch_one(db)
        .await?;

And this from_row

#[derive(Serialize, Deserialize)]
pub struct UserWithRoles {
    pub user: User,
    pub roles: Vec<Role>,
}

impl FromRow<'_, PgRow> for UserWithRoles {
    fn from_row(row: &PgRow) -> sqlx::Result<Self> {
        let user = User {
            uid: row.get("uid"),
            name: row.get("name"),
            username: row.get("username"),
            email: row.get("email"),
            provider: row.get("provider"),
            *.... other fields*
        };
        let roles: Vec<Role> = row.get("roles");

        Ok(Self { user, roles })
    }
}

I have this err :

error[E0277]: the trait bound `Role: PgHasArrayType` is not satisfied
   --> src/users.rs:168:36
    |
168 | ... = row.get("roles");
    |           ^^^ the trait `PgHasArrayType` is not implemented for `Role`
    |
    = help: the following other types implement trait `PgHasArrayType`:
              bool
              i8
              i16
              i32
              i64
              u8
              f32
              f64
            and 41 others
    = note: required for `Vec<Role>` to implement `sqlx::Type<Postgres>

How to make mapping for Vec<Role> in rust SQLx crate ?

as I understand SQLx does not understand the array returned from Postgres. and I tried from_row to map each to other BUT failed .

Solution

After 3 days of search and a lot of trait impl suggestions which are complex for me and do not work .. because I think this is a common need and should done easily ..

This will work for complex relations that has pivot tabla ( one to many ) or ( many to many ).

the solution is very simple :

Do the array thing in Postgres as JSON_AGG .
Get that JSON from db as a string - So no need to impl any boring traits.
convert that string to the Vec you please.

Here is the code :

    pub async fn with_roles(&self, state: &AppState) -> Result<UserWithRoles> {
    let row = sqlx::query(
        r#"
        SELECT users.*, JSON_AGG(roles.*) AS roles
        FROM users 
        JOIN users_roles ON users_roles.user_uid = users.uid
        JOIN roles ON users_roles.role_uid = roles.uid
        WHERE users.uid = $1
        GROUP BY users.uid;
        "#,
    )
    .bind(self.uid)
    .fetch_one(&state.db)
    .await?;
    let user = User {
        uid: row.get("uid"),
        name: row.get("name"),
        username: row.get("username"),
        email: row.get("email"),
        provider: row.get("provider"),
        password: None,
        access_token: None,
        refresh_token: None,
        created_at: row.get("created_at"),
        updated_at: row.get("updated_at"),
        deleted_at: row.get("deleted_at"),
        last_sign: row.get("last_sign"),
    };

   // Here is the trick ---v
    let roles: Vec<Role> = serde_json::from_value(row.get("roles")).unwrap();

    Ok(UserWithRoles { user, roles })
}

Code for the Sruct I am mapping for

#[derive(Serialize, Deserialize, Debug)]
pub struct UserWithRoles {
    pub user: User,
    pub roles: Vec<Role>,
}

Edit: Another better solution:

is to impl FromRow after adding Derive(Default, FromRow) to the User and Role structs.

Note that I have changed Vec to be Option<Vec> which is better because sometimes User has no roles. I also used Json type from Sqlx.

Default trait is needed especially if you have Options in your struct.

impl FromRow<'_, PgRow> for UserWithRoles {
    fn from_row(row: &PgRow) -> sqlx::Result<Self> {
        let user: User = User::from_row(row).expect("deser user failed");

        let roles = row
            .try_get::<Json<Vec<Role>>, _>("roles")
            .map(|x| if x.is_empty() { None } else { Some(x.0) })
            .unwrap_or(None);

        println!("{:?}", roles);

        Ok(Self { user, roles })
    }
}

This feature is needed : serde_json = { version = "1.0.114", features = ["raw_value"] } Note we do not use query_as! macro because - as per docs - it does not depend on FromRow.

Note that: Mapping SQL results is not as easy as I thought. But here I introduced some ideas, may it helps.