Many to Many as vector

[publicarray]

Hi I have a query with a few joins and would like my many to many relationship to be returned as a Vec what is the best way to do this?

#[derive(Serialize, Deserialize, Queryable, Debug, Clone)]
pub struct Build {
    pub id: DbId,
    pub package: String,
    pub upstream_version: String,
    pub revision: Db32,
    pub architectures: String,
    pub firmware: String,
    pub publisher: String,
    pub insert_date: NaiveDateTime,
    pub active: Option<bool>,
}

impl DbBuild {
    pub fn find_all(conn: &Connection) -> Vec<Build> {
        build::table
        .limit(20)
        .offset(0)
        .inner_join(version::table.inner_join(package::table))
        .inner_join(firmware::table)
        .inner_join(user::table)
        .inner_join(build_architecture::table.inner_join(architecture::table))
        .select((
            build::id,
            package::name,
            version::upstream_version,
            version::ver,
            architecture::code, // need as an array or vector
            firmware::version,
            user::username,
            build::insert_date,
            build::active))
        .order(build::id.asc())
        .load::<Build>(conn)
        .expect("Error loading builds from db")
    }
}

[weiznich]

There is unfortunately now database independent way to express this behavior as part of your sql query. Postgres provides the array_agg aggregate function that could be used in combination with group by to express such a behavior. I'm not aware similar extensions for other database systems.
Another solution for this problem is to use separate queries here based in the API provided by diesel::associations. Entity::belonging_to(...) only returns a query that can be used with other QueryDsl methods like joins or select clauses.

[publicarray]

Thanks the project that I'm working on is supposed to support multiple Databases I don't mind separate queries. I just don't like my solution either:

       let builds_by_id = build::table
            .select(build::id)
            .load::<DbId>(conn)?;

        let mut architectures: Vec<Vec<String>> = Vec::new();
        for build_id in builds_by_id {
            let arch = build_architecture::table
                .filter(build_architecture::build_id.eq(build_id))
                .inner_join(architecture::table)
                .select(architecture::code)
                .load::<String>(conn)?;
            architectures.push(arch)
        }

        let db_builds = build::table
            .inner_join(version::table.inner_join(package::table))
            .inner_join(firmware::table)
            .inner_join(user::table)
            .inner_join(build_architecture::table.inner_join(architecture::table))
            .select((
                build::id,
                package::name,
                version::upstream_version,
                version::ver,
                // architecture::code, // as an array or vector
                firmware::version,
                user::username,
                build::insert_date,
                build::active))
            .order_by(build::id.asc())
            .load::<BuildTmp>(conn)
            .expect("Failed to get builds from db");

        let mut builds: Vec<Build> = Vec::new();
        for (i, b) in db_builds.iter().enumerate() {
            builds.push(
                Build {
                    id: b.id,
                    package: b.package.clone(),
                    upstream_version: b.upstream_version.clone(),
                    revision: b.revision.clone(),
                    architectures: architectures[i].clone(),
                    firmware: b.firmware.clone(),
                    publisher: b.publisher.clone(),
                    insert_date: b.insert_date,
                    active: b.active,
                }
            )
        }
        return Ok(builds);

[weiznich]

As written above: diesel::associations provides the API to do that grouping for you in an efficient manner. Basically you would fetch All information linked to build_id first. After that you can use BeloningToDsl to fetch the associated architectures. In the end you can use the grouped_by + zip syntax to easily build the final data structure.

[publicarray]

Thanks @weiznich I've learned a bit more about diesel :)

In case someone else has a query like this I'll just post this here:

#[derive(Serialize, Deserialize, Associations, Identifiable, Queryable)]
#[table_name = "build"]
pub struct BuildTmp {
    pub id: DbId,
    pub package: String,
    pub upstream_version: String,
    pub revision: Db32,
    pub firmware: String,
    pub publisher: String,
    pub insert_date: NaiveDateTime,
    pub active: Option<bool>,
}

#[derive(Serialize, Deserialize, Associations, Identifiable, Queryable)]
#[table_name = "build"]
pub struct Build {
    pub id: DbId,
    pub package: String,
    pub upstream_version: String,
    pub revision: Db32,
    pub architectures: Vec<String>,
    pub firmware: String,
    pub publisher: String,
    pub insert_date: NaiveDateTime,
    pub active: Option<bool>,
}

impl DbBuild {
    pub fn new_build (b: BuildTmp, architectures: Vec<String>) -> Build {
        Build {
             id: b.id,
             package: b.package.clone(),
             upstream_version: b.upstream_version.clone(),
             revision: b.revision,
             architectures,
             firmware: b.firmware.clone(),
             publisher: b.publisher.clone(),
             insert_date: b.insert_date,
             active: b.active,
        }
    }

    pub fn find_all(conn: &Connection, limit: i64, offset: i64) -> QueryResult<Vec<Build>> {
        // https://docs.diesel.rs/1.4.x/diesel/associations/index.html
        let builds_tmp = build::table
            .limit(limit)
            .offset(offset)
            .inner_join(version::table.inner_join(package::table))
            .inner_join(firmware::table)
            .inner_join(user::table)
            .select((
                build::id,
                package::name,
                version::upstream_version,
                version::ver,
                firmware::version,
                user::username,
                build::insert_date,
                build::active,
            ))
            .load::<BuildTmp>(conn)?;

            let builds_architectures: = BuildArchitecture::belonging_to(&builds_tmp)
                .inner_join(architecture::table)
                .load::<(BuildArchitecture, DbArchitecture)>(conn)?
                .grouped_by(&builds_tmp);

        let builds = builds_tmp
            .into_iter()
            .zip(builds_architectures)
            .map(|(b, ba_a)| {
                DbBuild::new_build(
                    b,
                    ba_a.into_iter()
                        .map(|(_, a)| a.code)
                        .collect::<Vec<_>>(),
                )
            })
            .collect::<Vec<_>>();
        Ok(builds)
    }
}

I think there is still room for improvement. 🤷