How to map SQLx many to many to rust Vec using sqlx

1

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 .
2

Try deriving SQLX's type for Role:

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

same error

You can not just add sqlx::Type from nowhere

4

No dice , Boss

pub async fn with_roles(&self, db: &PgPool) -> Result<UserWithRoles> {
let user_with_roles: UserWithRoles = sqlx::query(
r#"
SELECT users.*, ARRAY_AGG((roles.uid, roles.name)) AS "roles!: 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?;
Ok(user_with_roles)
}

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"),
            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"),
        };

        let roles = row.get("roles");

        Ok(Self { user, roles })
    }
}

impl PgHasArrayType for Role {
    fn array_type_info() -> PgTypeInfo {
        PgTypeInfo::with_name("_roles")
    }
}

tried roles and _role no dice

5

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.