Load data in to nested struct (many to many)

[jb-alvarado]

Hello community,

I try something like a many to many selection. I know I have to construct this by my self because diesel has not this function out of the box.

To illustrate I use the example code below.

Models:

struct Movie {
    id: i32,
    name: String,
    genre: String,
}

struct Actor {
    id: i32,
    name: String,
}

struct MovieActors {
    movie_id: i32,
    actor_id: i32,
}

Custom Serializer:

struct MovieSerializer {
    movie_id: i32,
    movie_name: String,
    movie_genre: String,
    actors: Vec<Actor>,
}

impl From<(Movie, Vec<Actor>)> for MovieSerializer {
    fn from(values: (Movie, Vec<Actor>)) -> Self {
        Self {
            movie_id: values.0.id,
            movie_name: values.0.name,
            movie_genre: values.0.genre,
            actors: values.1,
        }
    }
}

Query:

fn select_movie(search: &str) -> Result<Vec<MovieSerializer>, Error> {
    use super::schema::movie;
    use super::schema::actor;
    use super::schema::movie_actors;

    let conn = &mut connection();

    let data: Vec<MovieSerializer> = movie::dsl::movie
        .inner_join(movie_actors::dsl::movie_actors.inner_join(actor::dsl::actor)
        .filter(movie::name.eq(search))
        .load::<(Movie, Vec<Actor>)>(conn)
        .map(|x| x.into_iter().map(MovieSerializer::from).collect())?;

    Ok(data)
}

Please ignore the missing parts.

As you see I would like to build a struct with MovieSerializer with the containing movie name, genre and then a vector which should hold the actors.

My query doesn't work like that, but this was the closest I got.

Have you an example of how I can do this?

[weiznich]

My query doesn't work like that, but this was the closest I got.

First of all: The query you've included in your post is not valid at all. It will not return a Vec<(Movie, Vec<Actor>)>, as SQL just don't work like that. SQL queries return only plain records, so something like Vec<(Movie, Actor)>, which duplicates Movie for any matching actor. That's a fundamental property of how databases work and that's nothing any library builded on top can ever change.

A naive approach for loading data into the structure you like, would be to issue multiple queries. The first query would load all wanted movies and "just" initialize the actor field with some default value. Then you would loop over all loaded movies and load a list of actors for each movie. I wrote "naive" solution here, as this solution has a really bad performance characteristic. Such an approach suffers from the N + 1 query problem that is quite common for ORM's in other languages. The underlying problem mostly comes from exactly such datastructures as you want to load.

Now how to handle such cases in a better way? Diesel provides diesel::associations and disallows such default values on load, which allows you to perform similar operations. Internally it performs exactly one query to load the associated data for all movie entries at once. It then groups those manually, which requires a separate list. This results in functions returning (Movie, Vec<Actor>) instead of your "desired" structure. If you really want to construct that structure that must be done manually in a later step.

As an side note: If you believe to need that structure because of serde, checkout the #[serde(flatten)] attribute, which greatly simplifies the mapping there.

[jb-alvarado]

Thank you @weiznich, now I got somethings more clear! I come more close on the result I wanted, but I'm still not getting: (Movie, Vec<Actor>), instead I get now: (Movie, Vec<(MovieActor, Actor)>), with this code:

let movies = movie::dsl::movie
    .filter(movie::name.eq("Luthers Kampf um die Wahrheit"))
    .load::<Movie>(conn)?;

let actors = MovieActor::belonging_to(&movies)
    .inner_join(actor::dsl::actor)
    .load::<(MovieActor, Actor)>(conn)?
    .grouped_by(&movies);

let data = movies.into_iter().zip(actors).collect::<Vec<_>>();

I could do:

let actors = MovieActor::belonging_to(&movies)
    .inner_join(actors::dsl::actors)
    .select(actors::all_columns)
    .load::<Actor>(conn);

But then my structure is also wrong.

Any idea?

[jb-alvarado]

Ok, I have it I think:

let movie = movies::dsl::movies.load::<Movie>(conn)?;

let actor = MovieActor::belonging_to(&movie)
    .inner_join(actors::dsl::actors)
    .load::<(MovieActors, Actor)>(conn)?
    .grouped_by(&movie)
    .into_iter()
    .map(|i| i.into_iter().map(|(_, s)| s).collect::<Vec<_>>());

let data = movie.into_iter().zip(actor).collect::<Vec<_>>();

Please let me know, if there is a more efficient way.

[jb-alvarado]

I'm facing now the next problem, when I add another table, let's say Category {id: i32, name: String}, and my movie has a foreign key to that table. When I inner_join that category to my movie, I can not use belonging_to(&movie) for my actor anymore. How would work this?

[jb-alvarado]

Ok just for the record, I found the solution on a old chat command. In diesel 1.* and I guess in diesel 2.0 belonging_to on a tuple is not supported, so I had to extract my movies and than I can use belonging_to to it.

let movie_obj = movies::dsl::movies
    .inner_join(categories::table)
    .load::<(Movie, Category)>(conn)?;

let movie = movie_obj
    .clone()
    .into_iter()
    .map(|(m, _)| m)
    .collect::<Vec<_>>();

let actor = MovieActors::belonging_to(&movie)
    .inner_join(actors::dsl::actors)
    .load::<(MovieActors, Actor)>(conn)?
    .grouped_by(&movie)
    .into_iter()
    .map(|i| i.into_iter().map(|(_, s)| s).collect::<Vec<_>>());

let data = movie_obj.into_iter().zip(actor).collect::<Vec<_>>();