Add data to Diesel results when user is logged in without using N+1 queries

Question

I have a movie database where users can mark movies as watched and favorite using the following models:

#[derive(Serialize, Deserialize, Identifiable, Queryable, Associations)]
#[table_name = "movies"]
struct Movie {
    id: 32
}

#[derive(Serialize, Deserialize, Identifiable, Queryable, Associations)]
#[table_name = "users"]
struct User {
    id: 32
}

#[derive(Serialize, Deserialize, Identifiable, Queryable, Associations)]
#[table_name = "favorite_movies"]
struct FavoriteMovie {
    movie_id: i32,
    user_id: i32,
}

#[derive(Serialize, Deserialize, Identifiable, Queryable, Associations)]
#[table_name = "watched_movies"]
struct WatchedMovie {
    movie_id: i32,
    user_id: i32,
    date: DateTime,
}

How would I handle the case where I want to list the 100 latest movies added to the database and also include user data for each movie if a user is logged in while also avoiding the N+1 query problem?

I'm thinking I could add another struct for the movies table where I also include optional user data but I'm not sure how I would use it together with the Diesel ORM.

struct UserData {
   favorite: bool,
   watched: Option<DateTime>,
}

struct UserMovie {
   id: i32,
   user_data: Option<UserData>,
}

Answer 1

Using left_join:

struct UserMovie {
    movie: Movie,
    favorite_movie: Option<FavoriteMovie>,
    watched_movie: Option<WatchedMovie>,
}

let conn = get_db_connection();
let user_movies: Vec<UserMovie> = movies::table
    .left_join(
        favorite_movies::table.on(favorite_movies::movie_id
            .eq(movies::id)
            .and(favorite_movies::user_id.eq(user_id))),
    )
    .left_join(
        watched_movies::table.on(watched_movies::movie_id
            .eq(movies::id)
            .and(watched_movies::user_id.eq(user_id))),
    )
    .load(conn)?
    .into_iter()
    .map(
        |(movie, favorite_movie, watched_movie): (Movie, Option<FavoriteMovie>, Option<WatchedMovie>)| UserMovie {
            movie,
            favorite_movie,
            watched_movie,
        }
    )
    .collect()