How to load multiple related entities

[etienne1698]

Hello,

I am trying to load multiple related entities efficiently in SeaORM. My goal is to retrieve a list of events, along with their associated bookings (which contain participants) and users.

Here is my current approach:

use std::collections::HashMap;

use backend_common::AppResult;
use backend_models::*;

use sea_orm::{
    ColumnTrait, Condition, DatabaseConnection, EntityTrait, FromQueryResult, PrimaryKeyTrait,
    QueryFilter, Related,
};
use serde::{Deserialize, Serialize};
use utoipa::ToSchema;

use crate::events_dto::{FilledEvent, FilledEventBooking, GetAllEventsRequestDTO};

pub fn associate<P, C, K, F, G>(
    mut parents: Vec<P>,
    children: Vec<C>,
    parent_key: F,
    child_key: G,
    mut associate_child: impl FnMut(&mut P, C),
) -> Vec<P>
where
    K: Eq + std::hash::Hash,
    F: Fn(&P) -> K,
    G: Fn(&C) -> K,
{
    let parent_id_map: HashMap<K, usize> = parents
        .iter()
        .enumerate()
        .map(|(i, p)| (parent_key(p), i))
        .collect();

    for child in children {
        if let Some(&index) = parent_id_map.get(&child_key(&child)) {
            associate_child(&mut parents[index], child);
        }
    }

    parents
}

pub async fn find_all(
    db: &DatabaseConnection,
    filters: GetAllEventsRequestDTO,
    id_current_workspace: i32,
) -> AppResult<Vec<FilledEvent>> {
    let mut event = events::Entity::find()
        .filter(events::Column::IdWorkspace.eq(id_current_workspace))
        .filter(
            Condition::any()
                .add(events::Column::Dtstart.between(&filters.start_start, &filters.start_end))
                .add(events::Column::RecurrenceFreq.is_not_null()),
        )
        .into_model::<FilledEvent>()
        .all(db)
        .await?;
    let event_ids = event.iter().map(|s| s.id).collect::<Vec<_>>();
    let u = events_users::Entity::find()
        .find_also_related(user::Entity)
        .filter(events_users::Column::IdEvent.is_in(event_ids.clone()))
        .all(db)
        .await?;
    let mut b = bookings::Entity::find()
        .filter(bookings::Column::IdEvent.is_in(event_ids))
        .into_model::<FilledEventBooking>()
        .all(db)
        .await?;
    let b_p = bookings_participants::Entity::find()
        .filter(
            bookings_participants::Column::IdBooking
                .is_in(b.iter().map(|s| s.id).collect::<Vec<_>>()),
        )
        .all(db)
        .await?;

    b = associate(
        b,
        b_p,
        |parent| parent.id,
        |child| child.id_booking.unwrap_or_else(|| 0),
        |p, c| p.participants.push(c),
    );
    event = associate(
        event,
        b,
        |parent| parent.id,
        |child| child.id_event,
        |p, c| p.bookings.push(c),
    );
    Ok(associate(
        event,
        u,
        |p| p.id,
        |c| c.0.id_event,
        |p, c| {
            if let Some(user) = c.1 {
                p.users.push(user);
            }
        },
    ))
}

Is this the best approach to load multiple related entities in SeaORM?
Would a JOIN query be more efficient than fetching related entities separately and then associating them?
Is there a more idiomatic way to achieve this using SeaORM's relations API?

Thanks in advance for your help!