Pagination and relationship filtering in sea-orm

[im-trisha]

Hello everyone, thanks for this wonderful library!

I have two HUGE questions:
1.Using .find_with_related.... How am I supposed to filter the related model? I mean like “Find related only if condition X is met, else None”, here's my example:

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(
        belongs_to = "Entity",
        from = "Column::Overrides",
        to = "Column::Id",
        on_update = "NoAction",
        on_delete = "SetNull"
    )]
    SelfRef,
}

impl Related<Entity> for Entity {
    fn to() -> RelationDef {
        Relation::SelfRef.def()
    }
}

let (food, food_override) = foods::Entity::find_by_id(food_id)
    .filter(foods::Column::Source.ne(Sources::User))
    .find_also_related(foods::Entity)
    // Here, I would like to do something like .filter(related_entity.owned_by.eq(user_id))
    .one(&self.database.clone())
    .await?
    .ok_or_else(|| ServiceError::FoodServiceError::FoodNotFound))?

2. I can't understand how i can get, in a query with .limit and .skip, the total count (without taking into account the limit and skip) without doing two queries that are really slow. Here's what i am currently using, which is obviously super slow:

let query = foods::Entity::find().filter(SomeEnormousAndSlowFilter)
let foods_count = query.clone().count(&self.database.clone()).await?;
let foods = query.limit(100).skip(100)
    .find_also_related(foods::Entity)
    // something like .filter(related_entity.owned_by.eq(user_id))
    .all(&self.database.clone())
    .await?
    .into_iter()
    .map(|(food, food_override)| FoodWithOverride {
        food,
        food_override,
    });

[For8stt]

For the first part, I’d probably not use find_also_related if you need custom filtering on the joined/related row.

find_also_related is nice for simple relation loading, but once you need conditions on the related alias, manual join is usually clearer.

Example idea:

use sea_orm::{
    sea_query::Alias,
    ColumnTrait, EntityTrait, JoinType, QueryFilter, QuerySelect,
};

let override_alias = Alias::new("food_override");

let row = foods::Entity::find()
    .filter(foods::Column::Id.eq(food_id))
    .filter(foods::Column::Source.ne(Sources::User))
    .join_as(
        JoinType::LeftJoin,
        foods::Relation::SelfRef.def(),
        override_alias.clone(),
    )
    // condition on the joined override row
    .filter(Expr::col((override_alias.clone(), foods::Column::OwnedBy)).eq(user_id))
    .one(db)
    .await?;

Small note: putting the related condition in WHERE can turn the LEFT JOIN into an effective INNER JOIN. If you want “return food anyway, but override = None when it does not match”, the condition should be part of the ON clause instead of WHERE.

Something like:

let rel = foods::Relation::SelfRef
    .def()
    .on_condition(move |_left, right| {
        Expr::col((right, foods::Column::OwnedBy))
            .eq(user_id)
            .into_condition()
    });

let rows = foods::Entity::find()
    .filter(foods::Column::Source.ne(Sources::User))
    .join(JoinType::LeftJoin, rel)
    .all(db)
    .await?;

For the second part: total count + paginated rows usually means two queries. That’s normal. SeaORM also has paginate() for this:

let paginator = foods::Entity::find()
    .filter(SomeEnormousAndSlowFilter)
    .paginate(db, 100);

let total = paginator.num_items().await?;
let page = paginator.fetch_page(1).await?;

If the count query is too slow, the fix is usually DB-side:

• add/check indexes for the filter columns
• simplify the filter
• cache approximate counts if exact count is not required
• use a window function/custom query like COUNT(*) OVER() if your DB/query shape fits it

But for most APIs I’d keep it simple: one query for count, one query for data. Trying to force both into the ORM relation loader usually makes the query harder to control.

[im-trisha]

@For8stt wow thanks! I won't be able to use this now, but it's surely interesting, I didn't know either answers :)