How to construct a complex LEFT JOIN with aliasing using sea-query in Rust?

Question

I'm working on a Rust project where I use sea-query to build SQL queries. I have the following SQL query:

SELECT * FROM tblcomponent AS com1 
LEFT JOIN tblcomponent AS com2 ON com1.intParentComponentId_fk=com2.intId_pk 
LEFT JOIN tblcomponent AS com3 ON com2.intParentComponentId_fk=com3.intId_pk 
LEFT JOIN tblcomponent AS com4 ON com3.intParentComponentId_fk=com4.intId_pk 
LEFT JOIN tblcomponentdef AS comdef2 ON com2.intComponentDefId_fk=comdef2.intId_pk
LEFT JOIN tblcomponentdef AS comdef3 ON com3.intComponentDefId_fk=comdef3.intId_pk
LEFT JOIN tblcomponentdef AS comdef4 ON com4.intComponentDefId_fk=comdef4.intId_pk

The main challenge I'm facing is to set up multiple LEFT JOIN clauses using the same table (tblcomponent) with different aliases (com1, com2, com3, com4).

Does anyone know how to replicate the above SQL using sea-query in Rust?

As you can see it is a nested self-join. That is why we need multiple aliases. Any help would be greatly appreciated!

This is kinda how it looks like

use entities::{tblcomponent,tblcomponentdef}
tblcomponent::Entity::find()
        .join(
            JoinType::LeftJoin,
            //???
todo!()
        )

Answer 1

Assuming that you have declared the entity and relation as below:

#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Eq)]
#[sea_orm(table_name = "tblcomponent")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub id: i32,
    pub tblcomponentdef_id: i32,
    pub parent_id: i32,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(
        belongs_to = "Entity",
        from = "Column::ParentId",
        to = "Column::Id"
    )]
    Parent,
    #[sea_orm(
        belongs_to = "super::tblcomponentdef::Entity",
        from = "Column::TblcomponentdefId",
        to = "super::tblcomponentdef::Column::Id"
    )]
    Tblcomponentdef,
}

Then you can construct the join query using the code below:

use sea_orm::sea_query::Alias;

let com2 = Alias::new("com2");
let com3 = Alias::new("com3");
let com4 = Alias::new("com4");
let comdef2 = Alias::new("comdef2");
let comdef3 = Alias::new("comdef3");
let comdef4 = Alias::new("comdef4");

let select = tblcomponent::Entity::find()
    .join_as(
        JoinType::LeftJoin,
        tblcomponent::Relation::Parent.def(),
        com2.clone(),
    )
    .join_as(
        JoinType::LeftJoin,
        tblcomponent::Relation::Parent
            .def()
            .from_alias(com2.clone()),
        com3.clone(),
    )
    .join_as(
        JoinType::LeftJoin,
        tblcomponent::Relation::Parent
            .def()
            .from_alias(com3.clone()),
        com4.clone(),
    )
    .join_as(
        JoinType::LeftJoin,
        tblcomponent::Relation::Tblcomponentdef
            .def()
            .from_alias(com2),
        comdef2,
    )
    .join_as(
        JoinType::LeftJoin,
        tblcomponent::Relation::Tblcomponentdef
            .def()
            .from_alias(com3),
        comdef3,
    )
    .join_as(
        JoinType::LeftJoin,
        tblcomponent::Relation::Tblcomponentdef
            .def()
            .from_alias(com4),
        comdef4,
    )
    .into_model::<tblcomponent::Model>();
let stmt = select.into_statement(DbBackend::MySql).to_string();
println!("{}", stmt);

The query statement generated is like below:

SELECT
`tblcomponent`.`id`, `tblcomponent`.`tblcomponentdef_id`, `tblcomponent`.`parent_id`
FROM `tblcomponent`
LEFT JOIN `tblcomponent` AS `com2` ON `tblcomponent`.`parent_id` = `com2`.`id`
LEFT JOIN `tblcomponent` AS `com3` ON `com2`.`parent_id` = `com3`.`id`
LEFT JOIN `tblcomponent` AS `com4` ON `com3`.`parent_id` = `com4`.`id`
LEFT JOIN `tblcomponentdef` AS `comdef2` ON `com2`.`tblcomponentdef_id` = `comdef2`.`id`
LEFT JOIN `tblcomponentdef` AS `comdef3` ON `com3`.`tblcomponentdef_id` = `comdef3`.`id`
LEFT JOIN `tblcomponentdef` AS `comdef4` ON `com4`.`tblcomponentdef_id` = `comdef4`.`id`