Feature Request: Introduce has / has_filter-like Subquery-Based Filtering, Plus Optional Count-Based Variants

[mpyw]

I’d like to propose a feature that would significantly improve the user experience when filtering records by related entities. In frameworks like Laravel, we often rely on has and whereHas methods to filter entities based on the existence or attributes of their relations, without depending on complex JOIN logic. A similar approach in SeaORM could be incredibly beneficial.

• Related: Where exists on relations? #1106

---

Core Proposal: has and has_filter

1) has(Relation)

• Filters the parent entity to include only rows that have at least one related record.
• Internally, this could translate to WHERE EXISTS(...) or WHERE parent.id IN (...) subqueries.
• Example:

  // Filter users who have at least one related session.
  let users_with_sessions = Users::find()
      .has(Sessions)
      .all(db)
      .await?;

2) has_filter(Relation, subquery_builder)

• Filters the parent entity based on additional conditions applied to the related entity.
• Example:

  // Filter users who have any sessions with token = "xxx".
  let users_with_xxx_sessions = Users::find()
      .has_filter(Sessions, |sessions| {
          sessions.filter(Column::Token.eq("xxx"))
      })
      .all(db)
      .await?;

---

Why Use Subqueries Instead of INNER JOIN?

1. Reduced Risk of Duplicate Joins
   If multiple parts of the code add the same JOIN, you risk collisions or naming conflicts. Subqueries isolate that logic.
2. Clearer Separation of Concerns
   Subqueries keep your primary query simpler, instead of expanding it with multiple JOIN clauses.
3. Modern Query Optimizers
   Many databases handle WHERE EXISTS(...), WHERE IN(...), and JOIN similarly, so performance differences are often minimal.
   (Ref: 【ガチ検証】Eloquent whereHas() はもう遅くないよ！)

---

Why This Is Useful

1. Cleaner Syntax & Less Boilerplate
   Instead of manually constructing subqueries or joins, has and has_filter would let you directly reuse existing relationship definitions.
2. Familiarity & Intuitiveness
   Users coming from ORMs like Laravel’s Eloquent (or similar frameworks) will feel right at home.
3. Flexibility for Advanced Cases
   We can extend the same pattern to count-based filtering or more complex subquery conditions without complicating userland code.

---

Optional: Count-Based Variants

In some frameworks, you can specify conditions like “users who have more than N sessions.” In Rust, we might prefer separate methods rather than a single function with optional parameters:

• has_more_than(Relation, n) → filters entities whose related record count is > n
• has_less_than(Relation, n) → < n
• has_no_less_than(Relation, n) → >= n
• has_no_more_than(Relation, n) → <= n
• has_exact(Relation, n) → = n

And possibly for has_filter variants: has_filter_more_than, has_filter_less_than, has_filter_no_less_than, has_filter_no_more_than, has_filter_exact.

Example Usage

// Filter users who have more than 2 sessions.
let users_with_many_sessions = Users::find()
    .has_more_than(Sessions, 2)
    .all(db)
    .await?;

// Filter users who have at least 3 sessions.
let users_with_at_least_three = Users::find()
    .has_no_less_than(Sessions, 3)
    .all(db)
    .await?;

// Filter users who have exactly 1 session.
let users_with_exact_1 = Users::find()
    .has_exact(Sessions, 1)
    .all(db)
    .await?;

These could be implemented using subqueries with COUNT(*) checks, for example:

WHERE (
  SELECT COUNT(*)
  FROM sessions
  WHERE sessions.user_id = users.id
) = 1

Whether to rely on EXISTS, IN, or explicit COUNT is a design/implementation detail, but having a straightforward SeaORM API would reduce repetitive query code.

---

Final Thoughts

• Main Request: Please consider adding has and has_filter for subquery-based filtering, leveraging existing relationship definitions.
• Potential Extensions: Count-based variants like has_more_than, has_exact, etc. for advanced use cases.
• Implementation Detail: These can build on SeaQuery’s subquery operators (EXISTS, IN, COUNT, etc.) while offering a user-friendly, relationship-driven syntax.

Thank you for your time and consideration!

[tyt2y3]

Love this idea!