How to filter joined related rows and still keep pagination/count?

[For8stt]

I have a query where I need to load entities together with an optional related row.

The related row should only be loaded if it matches some condition, for example owned_by = current_user_id.

At first I tried to solve it with find_also_related, but it became hard to apply custom filters to the joined row without changing the result behavior.

I also need pagination and total count for the same query.

What is the recommended SeaORM approach here?

[For8stt]

I found the answer while testing this a bit more.

For this kind of case, find_also_related is probably not the best fit. It is nice for simple relation loading, but when the joined row needs its own custom condition, a manual LEFT JOIN gives much better control.

The important part is to put the condition for the joined table into the ON clause, not into WHERE.

If the condition is added to WHERE, the LEFT JOIN can behave like an INNER JOIN, because rows without a matching related record will be filtered out.

Example idea:

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

let query = food::Entity::find()
    .filter(food::Column::Source.ne(Source::User))
    .join(
        JoinType::LeftJoin,
        food::Relation::FoodOverride
            .def()
            .on_condition(move |_left, right| {
                Expr::col((right, food_override::Column::OwnedBy))
                    .eq(user_id)
                    .into_condition()
            }),
    );

So the logic becomes:

load all Food rows
optionally join FoodOverride
but only join override rows owned by this user

For pagination and total count, I ended up using SeaORM pagination:

let paginator = query.paginate(db, 50);

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

So the final approach is:

• use find_also_related for simple relation loading
• use manual joins when the joined table needs custom filtering
• put related-table filters into ON if the relation should stay optional
• use paginate() for count + page

That solved it for me.