What is the standard pattern to relate three tables (many-to-many relation) within Diesel?

Question

Database - Postgres

I have the following relation:

users <—>> users_organizations <<—> organizations

Schema:

table! {
    organizations (id) {
        id -> Int4,
        name -> Varchar,
    }
}

table! {
    users (id) {
        id -> Int4,
        name -> Varchar,
        email -> Varchar,
        password -> Varchar,
    }
}

table! {
    users_organizations (id, user_id, organization_id) {
        id -> Int4,
        user_id -> Int4,
        organization_id -> Int4,
    }
}

Models:

#[derive(Identifiable, Queryable, Debug, Serialize, Deserialize)]
pub struct Organization {
    pub id: i32,
    pub name: String,
}


#[derive(Identifiable, Queryable, PartialEq, Debug, Serialize, Deserialize)]
pub struct User {
    pub id: i32,
    pub name: String,
    pub email: String,
    pub password: String,
}


#[derive(Identifiable, Queryable, Debug, Associations, Serialize, Deserialize)]
#[belongs_to(User)]
#[belongs_to(Organization)]
#[table_name = "users_organizations"]
pub struct UserOrganization {
    pub id: i32,
    pub user_id: i32,
    pub organization_id: i32,
}

I want to create an organization. To support such relation, I have to manually add ids of user and organization to the users_organizations table. Is there any better approach to implement such relation?

let new_organization = NewOrganization { name: &msg.name };
let organization = insert_into(organizations::table)
    .values(&new_organization)
    .get_result::(conn)
    .map_err(|_| error::ErrorInternalServerError(“Error creating organization”))?;

let new_user_org = NewUserOrganizationIDs {
    user_id: msg.user_id,
    organization_id: organization.id,
};

insert_into(users_organizations::table)
    .values(&new_user_org)
    .get_result::<UserOrganization>(conn)
    .map_err(|_| error::ErrorInternalServerError("Error creating user-organization data"))

Same question here. In case of selecting all organizations that relate to user (and vice verse) I came up with the following code:

let user = users::table.filter(users::id.eq(&msg.user_id))
        .get_result::<User>(conn)
        .map_err(|_| error::ErrorNotFound("User doesn't exist"))?;

let user_organizations = UserOrganization::belonging_to(&user)
    .get_results::<UserOrganization>(conn)
    .map_err(|_| error::ErrorNotFound("User doesn't have any organization"))?;

let mut organization_ids = vec![];
for user_org in &user_organizations {
    organization_ids.push(user_org.organization_id);    
}

organizations::table.filter(organizations::id.eq_any(organization_ids))
    .get_results::<Organization>(conn)
    .map_err(|_| error::ErrorNotFound("Organizations don't exist"))

Answer 1

This answer is from the Diesel chat by @SRugina and @weiznich (edited and adapted for the question).

How do I write a many-to-many relationship with Diesel?

I normally combine belonging_to and join, so something like:

UserOrganization::belonging_to(&organizations)
    .inner_join(user::table)

Is there anything akin to belonging_to_many?

No, belonging_to_many does not exist because Diesel does not try to hide the database from you. Doing that would cause issues as soon as you want to do complex or non-standard things. Depending on your exact use case, joining all three tables could also be an option.

How do I use inner_join in this scenario?

You have three tables: users, organizations and user_organizations and you want to get all organizations for a specific user.

There are two variants doing that. The first variant is only one query, but may not match your required data layout if you want to do that for all users:

users::table
    .inner_join(user_organizations::table.inner_join(organizations::table))
    .filter(users::id.eq(user_id))
    .select(organizations::all_columns)
    .load::<Organization>(conn)?;

The second variant allows grouping the results for each user using the built-in associations API:

let user = users::table
    .find(user_id)
    .first::<User>(conn)?;

UserOrganization::belonging_to(&user)
    .inner_join(organizations::table)
    .select(organizations::all_columns)
    .load::<Organization>(conn)?;

Inserting requires three separate inserts. We do not try to hide that because in the end it's a user choice how to handle data consistency in case of a failed insert there. Using a transaction is a common choice.