Nested Associations

1

Given following database structure:

struct User {
  id: Uuid,
  name: String
}

struct Post {
  id: Uuid,
  user_id: Uuid,
  subject: String,
  comment: String
}

struct AssignedPosts {
  id: Uuid,
  user_id: Uuid,
  post_id: Uuid,
  comment: String
}

I would like to extend the User and Post structs so that they can hold a list of posts, assigned users and assigned posts like this:

struct User {
  id: Uuid,
  name: String,
  posts: Option<Vec<Post>>,
  assignedPosts: Option<Vec<Post>>
}

struct Post {
  id: Uuid,
  user_id: Uuid,
  subject: String,
  comment: String,
  user: Option<User>,
  assignments: Option<Vec<AssignedPosts>>
}

struct AssignedPosts {
  id: Uuid,
  user_id: Uuid,
  post_id: Uuid,
  comment: String,
  user: Option<User>,
  post: Option<Post>
}

This is like rails handles associations and lets the user fetch associated items with optimized sql calls.

I currently have no idea of how to fetch the assigned users to all posts a user created. So considering I have a user id in first place, I want to fetch the posts, what can be done by calling belonging_to(&user) but I do not see a way to easily fetch the associated users through AssignedPosts from there on.

The only idea I have is following:

  1. Mapping all post ids and fetch the AssignedPost using eq_any.
  2. Looping through the AssignedPost items, collecting user ids and fetching the users with eq_any.
  3. Looping through the fetched AssignedPost items and assigning the User matching user_id.
  4. Looping through the posts and adding AssignedPost items matching by post_id.

The obvious reason for needing AssignedPost ist that it holds meta data for the connection between user and post.

Is there a better way?

2

First of all the create of diesel was one of the maintainers of rails for a while, so we are quite aware how things are handled there. I cannot speak for them, but I assume that the design of diesel associations API is quite heavily influenced by their work there.

The main problem I see myself with such an API is that it hides database queries. This will lead to subtle N + 1 query bugs, for example when you are trying to all posts for all users. Diesel site steps this problem by providing an API that requires you to explicitly load those data separately.

Now for you specific question: AssignedPosts::belonging_to(&user) creates a ordinary query which just has a where clause with an condition to load all required AssignedPosts. That means you just can add whatever additional query part you like, for example just join the posts table (and even only select the corresponding columns from post). So something like AssignedPosts::belonging_to(&user).inner_join(posts::table).select(posts::all_columns) will just load all posts belonging to a specific user.

3

Thank you for your answer.

I just mentioned the rails part for a reference of what I am trying to accomplish, not for referencing a better system or bad handling of sql.

My main problem is not getting an sql query but getting the results from the query into my structs. Do I have to do this manually or does diesel provide some convenience functions for this?

4

For matching a list of Post's to the corresponding User's there is diesel::associations::GroupedBy.
For actually constructing the structs your are on your own, because diesel has different opinions about those structure there. You likely need to implement Queryable manually to set the corresponding associated fields to None and then iterate over everything to populate the values.

5

I already followed your tip of using #[serde(flatten)] which I found on github.

I consider this solved, thank you.

Yet I am interessted in the reasoning of the grouping and zipping method I saw in the docs, because maybe I am on a wrong path with what I am trying to mimic.

6

That’s just the same thing that other ORM’s tend to to behind your back, which normally requires either having something to batch queries or leads to N+1 query bugs. We choose to make that explicit, because there are use cases where you can just skip those steps.

7

Thanks for the point.