Mapping n:m relations into Vec using sqlx

Viewed 436

I have 2 tables (workplaces and workers) with a n:m relationship. My goal is to have a workplace struct with a Vec that contains all the related workers. I want to do this using sqlx. Using Diesel is not an option for me.

This is what i came up with on the database side:

CREATE TABLE workplaces (
    id BIGSERIAL PRIMARY KEY,
    place TEXT NOT NULL
);

CREATE TABLE workers (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE workplaces_workers (
    workplace_id BIGINT NOT NULL REFERENCES workplaces (id) ON DELETE CASCADE,
    workers_id BIGINT NOT NULL REFERENCES workers (id) ON DELETE CASCADE,
    PRIMARY KEY (workplace_id, workers_id)
);

This is my rust code so far:

pub struct Workplace {
    pub id: i64,
    pub place: String,
    pub workers: Option<Vec<Worker>>
}

pub struct Worker {
    pub id: i64,
    pub name: String
}

I tried using the sqlx::query_as macro, but i can't figure out a way to map the Vec<Worker>

pub async fn find_workplace(pool: &PgPool, id: &i64) -> Result<Option<Workplace>> {
  return Ok(query_as!(Workplace, "SELECT * FROM workplaces wp 
      JOIN workplaces_workers wpws ON wp.id = wpws.workplace_id
      JOIN workers ws ON wpws.workers_id = ws.id 
      WHERE wp.id = $1", id).fetch_optional(pool).await?)
}
0 Answers
Related