Join on EntityLoader `Entity::load().join(LeftJoin, child::Entity)`

Motivation

With a many to many relation like the following, if you want to filter on tag column like note::Entity::load().with(tag::Entity).filter(tag::Column::Name.eq(query)), the query errs with "Query Error: error returned from database: missing FROM-clause entry for table \"tag\" at line 3603". To avoid this, I cannot use EntityLoader but the old EntityTrait::find.

Note

#[sea_orm::model]
#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel, Serialize, Deserialize)]
#[sea_orm(table_name = "note")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: Uuid,
    #[sea_orm(column_type = "Text")]
    pub text: String,
    #[sea_orm(has_many, via = "tags_notes")]
    pub tags: HasMany<super::tag::Entity>,
}

Tag

#[sea_orm::model]
#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel, Serialize, Deserialize)]
#[sea_orm(table_name = "tag")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: Uuid,
    #[sea_orm(column_type = "Text", unique_key = "name_user")]
    pub name: String,
    #[sea_orm(has_many, via = "tags_notes")]
    pub notes: HasMany<super::note::Entity>,
}

TagsNotes (Middle table)

#[sea_orm::model]
#[derive(Clone, Debug, PartialEq, Eq, DeriveEntityModel, Serialize, Deserialize)]
#[sea_orm(table_name = "tags_notes")]
pub struct Model {
    #[sea_orm(primary_key, auto_increment = false)]
    pub id: Uuid,
    pub note_id: Uuid,
    pub tag_id: Uuid,
    #[sea_orm(belongs_to, from = "note_id", to = "id", on_update = "NoAction", on_delete = "Cascade")]
    pub note: HasOne<super::note::Entity>,
    #[sea_orm(belongs_to, from = "tag_id", to = "id", on_update = "NoAction", on_delete = "Cascade")]
    pub tag: HasOne<super::tag::Entity>,
}

Proposed Solutions

If we can join on EntityLoader like this note::Entity::load().join(tags_notes::Relation::Note.def().rev()).join(tags_notes::Relation::Tag.def()).filter(tag::Column::Name.eq(query)), or even like this note::Entity::load().join(note::Relation::Tag.def()).filter(tag::Column::Name.eq(query)), I can use EntityLoader with complex filters.

Additional Information

cargo tree | grep sea- output, if this is helpful.

│   │   ├── sea-orm v2.0.2
│   │   │   ├── sea-orm-macros v2.0.2 (proc-macro)
│   │   │   │   ├── sea-bae v0.2.2 (proc-macro)
│   │   │   ├── sea-query v1.0.2
│   │   │   │   ├── sea-query-derive v1.0.0 (proc-macro)
│   │   │   ├── sea-query-sqlx v0.9.1
│   │   │   │   ├── sea-query v1.0.2 (*)
│   │   │   ├── sea-schema v0.18.1
│   │   │   │   ├── sea-query v1.0.2 (*)
│   │   │   │   ├── sea-query-sqlx v0.9.1 (*)
│   │   │   │   ├── sea-schema-derive v0.3.0 (proc-macro)
│   ├── sea-orm v2.0.2 (*)
│   │   │   ├── sea-orm v2.0.2 (*)
│   │   ├── sea-orm v2.0.2 (*)
│       ├── sea-orm v2.0.2 (*)

Start by reproducing the EntityLoader many-to-many example with note, tag, and tags_notes, using the proposed join and filter expressions. Trace the EntityLoader query-building entry point and relation traversal until the missing FROM-clause error is explained; done means the loader can join the relation path and filter on tag::Column::Name without that error.