Mapping n:m relations into Vec using sqlx

Question

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?)
}

Wouldn't this return a list of rows containing all the relevant columns in order? You would need to then group the matching records together. — Teymour, Apr 24 '21 at 17:45
@TeymourAldridge would you mind expounding on your comment with perhaps some code as an answer? — martinomburajr, Jul 21 '21 at 18:47

score 1 · Answer 1 · answered Jul 17 '23 at 14:50

As far as I know, SQLX, being Not An ORM™, provides no further convenience for constructing domain-types from your queries beyond type-matching flat structs to tables. This is good news and bad - we can have the schema that we want, and the domain types that we want, and have full control over both. But of course, we need to figure out for ourselves how to go from one to the other. To have our cake and eat it too, we will have to do the baking, so to speak. Fortunately, baking is not hard.

Below is a working solution for your schema, structs, and queries. Bear in mind that this is by far not the only or "best" solution: We could have an intermediary type representing the query result defined within rust and use query_as! to get static type checking on it, and then impl Into<Workplace> on our intermediary type. Another approach would be to impl Into<Option<Workplace>> on Vec<PgRow> directly. It depends on your use case and the API that you ultimately want to expose for your data persistence.

use sqlx::Row;

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

#[derive(Debug, sqlx::FromRow)]
pub struct Workplace {
    pub id: i64,
    pub place: String,
    //no need for Option, Vec can accommodate for zero workers
    pub workers: Vec<Worker>,
}

impl Workplace {
    pub async fn find_by_id(id: i64, pool: &sqlx::PgPool) -> Option<Self> {
        let rows = sqlx::query(
            "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",
        )
        .bind(id)
        .fetch_all(pool)
        .await
        .unwrap_or_default(); 
        if rows.is_empty() {
            return None;
        }
        let place = rows[0].get("place");
        let mut workers: Vec<Worker> = Vec::with_capacity(rows.len());
        for row in rows {
            workers.push(Worker {
                id: row.get("id"),
                name: row.get("name"),
            });
        }
        Some(Workplace { id, place, workers })
    }
}

Mapping n:m relations into Vec using sqlx

1 Answers1