Perhaps a bit oddly, with rapid business growth, some relationships between tables now require a review/approval process. However, at the project's inception, we didn't reserve a dedicated review status field within the relevant tables. Now, as a lot of content requires review, we've decided to add a new reviews table and use Eloquent's Polymorphic Relations (morph) to link it to the models that need reviewing. We've also created a Reviewable Trait, which quickly and effectively implemented the core requirement.

However, a problem has arisen: I'm unsure how to correctly query the intermediate/junction table based on the review status.

For example, we have users and roles tables, which are linked by an intermediate table, user_role_assigns.

The act of linking (the assignment itself) requires review and should be invisible to others until it is approved. The user_role_assigns table is the one that has the morph relationship to the reviews table via the Reviewable trait. My question is: how can I write a query to retrieve, for example, a user's roles, but only include the roles where the corresponding assignment record in user_role_assigns has been approved?

Currently, my code looks like this:

User::query()
    ->whereHas('roles', function (Builder $query) use ($isQueryCurrentUser) {
        if ($isQueryCurrentUser) {
            // this ok
            $query->with([
                'assigns' => function (Builder $query) {
                    $query->with(['review']);
                },
            ]);
        } else {
            // problem in here, assigns includes all assigns
            // so if someone assigned, will be included in result, show to others, this is wrong
            // but in query can't access attributes, don't know how to filter for current user list
            $query->whereHas('assigns', function (Builder $query) {
                $query->whereHas('review', function (Builder $query) {
                    $query->where('status', 'approved');
                });
            });
        }
    })
    ->get();