find_linked() generates invalid SQL for self referencing associations

[nicoulaj]

The implementation of find_linked() does not use aliases on join statements, so for self referencing many to many associations such as TABLE -> RELATES_TO -> TABLE the generated query is invalid. Other ORMs typically generate autoincremented aliases for each relation in this case.

[tyt2y3]

Thanks. Can you give a more concrete example on your entity and query?

[nicoulaj]

Example join table entity:

#[derive(Clone, Debug, PartialEq, DeriveActiveModelBehavior, DeriveEntityModel)]
#[sea_orm(table_name = "tag_relates_to_tag")]
pub struct Model {
    #[sea_orm(primary_key)]
    pub master_tag_id: i64,
    #[sea_orm(primary_key)]
    pub slave_tag_id: i64,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(belongs_to = "crate::entity::tag::Entity", from = "Column::SlaveTagId", to = "crate::entity::tag::Column::Id")]
    SlaveTag,
    #[sea_orm(belongs_to = "crate::entity::tag::Entity", from = "Column::MasterTagId", to = "crate::entity::tag::Column::Id")]
    MasterTag,
}

Linked impl:

#[derive(Default)]
pub struct TagRelatesToTagLink;

impl Linked for TagRelatesToTagLink {
    type FromEntity = crate::entity::Tag;
    type ToEntity = crate::entity::Tag;

    fn link(&self) -> Vec<RelationDef> {
        vec![
            Relation::MasterTag.def().rev(),
            Relation::SlaveTag.def(),
        ]
    }
}

Example queries:

TagRelatesToTagLink::default()
    .find_linked()
    .filter(...)
    .all(db)
    .await;

some_tag_model
    .find_linked()
    .all(db)
    .await;

[tyt2y3]

Sorry for not able to make it into 0.2.2. I am still confident we could fix this before 0.3.

[billy1624]

Would adding alias: Option<String> to Relation be a good idea?

[tyt2y3]

If you are talking RelationDef, then no, not a good idea.

[nicoulaj]

If that helps, I use this trait to workaround the issue (also has some unrelated filtering logic, but you get the idea):

pub trait FindLinkedFrom<E: EntityTrait> {
    fn find_linked_from_id<L: Linked<ToEntity=E>>(self, id: Key<L::FromEntity>) -> Self;
}

impl<E: EntityTrait> FindLinkedFrom<E> for Select<E> {
    fn find_linked_from_id<L: Linked<ToEntity=E>>(self, id: Key<L::FromEntity>) -> Self
    {
        let mut res = self;

        let link = L::default().link();
        let count = link.len();

        for (i, rel) in link.into_iter().rev().enumerate() {
            let from_tbl = rel.from_tbl.clone();
            let from_tbl_alias = Alias::new(&format!("r{}", i));
            let from_tbl_iden = from_tbl_alias.clone().into_iden();

            let to_tbl_iden = if i == 0 {
                unpack_table_ref(&rel.to_tbl)
            } else {
                Alias::new(&format!("r{}", i - 1)).into_iden()
            };

            let mut condition = Condition::all()
                .add(join_condition(rel, from_tbl_iden.clone(), to_tbl_iden));

            if i >= count - 1 {
                condition = condition.add(condition_filter_by_id::<L::FromEntity, _>(from_tbl_iden.clone(), &id));
            }

            QuerySelect::query(&mut res)
                .join_as(JoinType::InnerJoin, from_tbl, from_tbl_alias, condition);
        }

        res
    }
}