Laravel中BelongsToMany关联的递归多层whereHas查询需求

Got it, let's work through this recursive hierarchy problem you're facing. The nested whereHas approach you tried won't cut it because it only handles a fixed number of levels—and you need to cover unlimited hierarchy depth, all at the database layer for performance.

The right tool here is recursive Common Table Expressions (CTEs). They let you query dynamic, multi-level relationships directly in SQL, which aligns perfectly with your requirements.

Step-by-Step Implementation #

First, let's build a recursive CTE that finds all users (direct and indirect) who have the target user anywhere in their line manager hierarchy. We'll wrap this in a whereExists clause to integrate it with your existing User query.

$targetUserId = $this->getAuthenticatedUser()->id;

$users = User::query()
    ->with('lineManagers')
    ->orderBy('first_name')
    ->orderBy('last_name')
    ->havingEmploymentStatus(UserEmploymentStatus::EMPLOYED)
    ->whereExists(function ($query) use ($targetUserId) {
        $query->select(DB::raw(1))
            ->from(DB::raw('(
                -- Anchor: Direct reports to the target user
                SELECT user_id
                FROM line_manager_user
                WHERE line_manager_id = ?

                UNION ALL

                -- Recursive: Indirect reports (reports of reports)
                SELECT lmu.user_id
                FROM line_manager_user lmu
                JOIN recursive_reports rr ON rr.user_id = lmu.line_manager_id
            ) AS recursive_reports'))
            ->whereColumn('recursive_reports.user_id', 'users.id')
            ->setBindings([$targetUserId], 'select');
    })
    ->get();

How This Works #

1. Anchor Member: The first part of the CTE grabs all users who directly report to your target user (via line_manager_user where line_manager_id matches the target ID).
2. Recursive Member: This part joins the line_manager_user table back to the CTE results, pulling in users who report to anyone already in the recursive set—this repeats until no more indirect reports are found.
3. whereExists Check: We verify that the current User in our main query exists in the full set of recursive reports.

Performance Optimization Tips #

To keep this query fast, make sure you have the right indexes:

• Add a composite index on line_manager_user(line_manager_id, user_id)—this speeds up both the anchor and recursive joins.
• If your users table is large, ensure users.id is indexed (it should be by default in Laravel).

Reusable Scope (Optional) #

To make this logic reusable across your app, wrap it in a model scope:

// In your User model
public function scopeHasLineManagerInHierarchy($query, $targetUserId)
{
    return $query->whereExists(function ($subquery) use ($targetUserId) {
        $subquery->select(DB::raw(1))
            ->from(DB::raw('(
                SELECT user_id
                FROM line_manager_user
                WHERE line_manager_id = ?

                UNION ALL

                SELECT lmu.user_id
                FROM line_manager_user lmu
                JOIN recursive_reports rr ON rr.user_id = lmu.line_manager_id
            ) AS recursive_reports'))
            ->whereColumn('recursive_reports.user_id', 'users.id')
            ->setBindings([$targetUserId], 'select');
    });
}

Now you can simplify your query to:

$users = User::query()
    ->with('lineManagers')
    ->orderBy('first_name')
    ->orderBy('last_name')
    ->havingEmploymentStatus(UserEmploymentStatus::EMPLOYED)
    ->hasLineManagerInHierarchy($this->getAuthenticatedUser()->id)
    ->get();

Notes #

• This works with MySQL 8.0+, PostgreSQL, SQL Server, and other databases that support recursive CTEs. If you're stuck on an older MySQL version (5.7 or below), you'll need to upgrade your database or use a less efficient loop-based approach—but recursive CTEs are the cleanest, most performant solution here.

内容的提问来源于stack exchange，提问作者ThorntonStuart