ARTICLE AD BOX
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:
$id = 1; // from request $isQueryCurrentUser = $id === Auth::id(); 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'); }); }); } }) ->where('id', $id) ->get();