It is very easy to do multiple joins

[numfin]

I hope i will help someone like me.

Motivation

I have 3 tables: users, apartments, house
I want to select users and have access to apartment and house

I looked at source code of find_also_related() and found private helper function prepare_select_two().

I copy-modified this function in my code like this

use sea_orm::prelude::*;
use sea_orm::sea_query::{Alias, IntoIden, SelectExpr, SelectStatement};
use sea_orm::{EntityTrait, QueryTrait};
fn add_columns_with_prefix<S: QueryTrait<QueryStatement = SelectStatement>, T: EntityTrait>(
    selector: &mut S,
    prefix: &'static str,
) {
    for col in <T::Column as sea_orm::entity::Iterable>::iter() {
        let alias = format!("{}{}", prefix, col.to_string());
        selector.query().expr(SelectExpr {
            expr: col.select_as(col.into_expr()),
            alias: Some(Alias::new(&alias).into_iden()),
            window: None,
        });
    }
}

And now i am able to use it like this

let mut select = user::Entity::find()
    .select_only(); // it removes all selects so we can manually declare all prefixes

add_columns_with_prefix::<_, user::Entity>(&mut select, "user");
add_columns_with_prefix::<_, apartment::Entity>(&mut select, "apartment");
add_columns_with_prefix::<_, house::Entity>(&mut select, "house");

// now all we have to do is just apply our joins via
let result = select
    .join(JoinType::LeftJoin, user::Relation::Apartment.def())
    .join(JoinType::LeftJoin, apartment::Relation::House.def())
    .into_model::<Response>() // Don't forget your custom model. "Response" code below
    .all(&db)
    .await?;

All we left to do is just declare our new custom model:

#[derive(Debug, Serialize)]
pub struct Response {
    pub user: user::Model,
    pub apartment: apartment::Model,
    pub house: house::Model,
}
impl FromQueryResult for Response {
    fn from_query_result(res: &sea_orm::QueryResult, _pre: &str) -> Result<Self, sea_orm::DbErr> {
        let user = user::Model::from_query_result(res, "user")?;
        let apartment = apartment::Model::from_query_result(res, "apartment")?;
        let house = house::Model::from_query_result(res, "house")?;

        Ok(Self {
            user ,
            apartment ,
            house,
        })
    }
}

Proposed Solutions

Please make this usefull "modified" function public :) It is very usefull

Additional Information

You can do all same above without prefixes, but final struct will have incorrect data if your models have similar column names.
Example:

User { id: 1, name: "Bob" }
Apartment {  id: 2,  name: "301" }
House {  id: 1,  name: "CoolHouse" }

Then your final data will be incorect:

User { id: 1, name: "CoolHouse" }
Apartment {  id: 1,  name: "CoolHouse" }
House {  id: 1,  name: "CoolHouse" }

Prefixes fixing that :)

I hope i helped ❤️

[numfin]

Played a little bit more with this technique and now i don't even need to remember all prefixes myself :)

My prefixer

use sea_orm::prelude::*;
use sea_orm::sea_query::{Alias, IntoIden, SelectExpr, SelectStatement};
use sea_orm::{EntityTrait, QueryTrait};

pub struct Prefixer<S: QueryTrait<QueryStatement = SelectStatement>> {
    pub selector: S,
}
impl<S: QueryTrait<QueryStatement = SelectStatement>> Prefixer<S> {
    pub fn new(selector: S) -> Self {
        Self { selector }
    }
    pub fn add_columns<T: EntityTrait>(mut self, entity: T) -> Self {
        for col in <T::Column as sea_orm::entity::Iterable>::iter() {
            let alias = format!("{}{}", entity.table_name(), col.to_string()); // we use entity.table_name() as prefix
            self.selector.query().expr(SelectExpr {
                expr: col.select_as(col.into_expr()),
                alias: Some(Alias::new(&alias).into_iden()),
                window: None,
            });
        }
        self
    }
}

And this is how i use it:

let select = account::Entity::find()
    .filter(account::Column::UserId.eq(user.id))
    .order_by_asc(account::Column::CreatedAt)
    .select_only();
let result = Prefixer::new(select)
    .add_columns(account::Entity) // This is what changed 
    .add_columns(flat::Entity) // This is what changed 
    .add_columns(house::Entity) // This is what changed 
    .selector
    .join(JoinType::LeftJoin, account::Relation::Flat.def())
    .join(JoinType::LeftJoin, flat::Relation::House.def())
    .into_model::<Response>()
    .all(&db)
    .await?;

#[derive(Debug, Serialize)]
pub struct Response {
    pub account: SafeAccount,
    pub flat: flat::Model,
    pub house: house::Model,
}
impl FromQueryResult for Response {
    fn from_query_result(res: &sea_orm::QueryResult, _pre: &str) -> Result<Self, sea_orm::DbErr> {
        let account = account::Model::from_query_result(res, account::Entity.table_name())?.into();
        let flat = flat::Model::from_query_result(res, flat::Entity.table_name())?;
        let house = house::Model::from_query_result(res, house::Entity.table_name())?;

        Ok(Self {
            account,
            flat,
            house,
        })
    }
}

[numfin]

More abstractions to the god of abstractions:

use sea_orm::{EntityTrait, FromQueryResult};

pub fn parse_query_to_model<M: FromQueryResult, E: EntityTrait>(
    res: &sea_orm::QueryResult,
) -> Result<M, sea_orm::DbErr> {
    M::from_query_result(res, E::default().table_name())
}

impl FromQueryResult for AccountUnwindSafe {
    fn from_query_result(res: &sea_orm::QueryResult, _pre: &str) -> Result<Self, sea_orm::DbErr> {
        let account = parse_query_to_model::<account::Model, account::Entity>(res)?.into();
        let flat = parse_query_to_model::<flat::Model, flat::Entity>(res)?;
        let house = parse_query_to_model::<house::Model, house::Entity>(res)?;

        Ok(Self {
            account,
            flat,
            house,
        })
    }
}

[tyt2y3]

Thank you for your sharing! I have great inspirations from it.

I think it only works for one-to-one-to-one (and so on) cases, now I am convinced that this can be done with a composite struct API.

[Pascualex]

I stumbled upon this while trying to do something similar (but not this great) myself, thank you. I think these chains of one-to-one-to-one tables deserve this kind of support :)

[DusterTheFirst]

It would be nice to be able to pass a tuple to the find_also_related to be able to select multiple tables like this

[anshap1719]

@numfin After wrecking my brain for 3 hours, I just came across this and wanted to say thank you!

[numfin]

glad i helped <3