Apply scopes conditionally when fields with `@scope` are queried

[GregPeden]

What problem does this feature proposal attempt to solve?

There is currently not a solution for invoking a query scope when a particular field is queried. It might not seem intuitive as to WHY someone would do this, but query scopes can be used to overcome the N+1 in a tricky way when the desired value is abstracted or inferred through database relationships.

Consider the example here using "addSelect" inside a query scope to fake out Eloquent.

https://reinink.ca/articles/dynamic-relationships-in-laravel-using-subqueries#dynamic-relationships-via-subqueries

Here is the implementation of this strategy which I am seeking to use, which looks up when the user is next required to complete a specific deliverable task:

  public function scopeWithProjectRecordDue(Builder $query): void
  {
    $query->addSelect([
      'project_record_due' => Project::select('record_due_at')
        ->whereNotNull('record_due_at')
        ->hasByNonDependentSubquery('team', fn (Builder $q) => $q
            ->leftJoin('role_user', 'teams.id', 'role_user.team_id')
            ->whereColumn('role_user.user_id', 'users.id')
            ->where('role_user.role_id', 9))
        ->orderBy('record_due_at')
        ->orderBy('created_at')
        ->limit(1)
    ]);
  }

  public function getProjectRecordDueAttribute(): ?Carbon
  {
    if (!array_key_exists('project_record_due', $this->attributes)) {
      return $this->attributes['project_record_due'] = self::select([])
          ->where('id', $this->id)
          ->withProjectRecordDue()
          ->applyScopes()
          ->getQuery()
          ->first()
          ->project_record_due
          ?? null;
    }

    return $this->attributes['project_record_due'];
  }

Note hasByNonDependentSubquery is from another Laravel package which performance optimizes some of Laravel's relationship queries, it can be considered as equivalent to whereHas.

So this makes the query scope available, and in the case where it was not invoked eagerly, the custom accessor notices and runs it before returning the result. When the scope is applied to the original bulk query, this causes the retrieval of this value to be done within the initial batch query, and it much more efficient than running this same query separately for each Eloquent model. This is definitely not the design intent of query scopes but this is the best available solution to solve this problem within Laravel's offerings.

Right now there is not a way to invoke this query scope conditionally only when the related field is requested in the query. The best available solution to emulate this is to include an input field, something like a boolean "includeLastLoginScope" which is bound to the @scope directive.

Which possible solutions should be considered?

Enable the existing @scope directive to be usable on a query field.

Another solution is to cache the value of this on the user model using a queued job which re-computes the value when the relevant data set is updated, I'm just trying to avoid this.

I'm sure the response will be "a PR is welcome" but first I'm just surveying for the appetite to consider this because it is a very off-spec use case.

[spawnia]

I am confused what behaviour you would like out of @scope. Something about depending on arguments, but also using it on a query field (as opposed to a mutation field?)? Please clarify with an example.

[spawnia]

@GregPeden what would you image the schema definition to look like? I am assuming something like this:

type Task {
  ...
  project_record_due: DateTime @scope(name: "withProjectRecordDue")
}

With the semantics being: whenever Task.project_record_due is queried, the scope will be applied to the query builder for Task. Is that accurate?

[SuperDJ]

Possibly another example:

I have a users table with the following fields: first_name, insertion, last_name. I quite often need to get the full name. Of course it is possible to select all fields individually and then use an if-statement to create the full name that way. Something like the following in blade:

{{ $user->first_name }} @if($user->insertion){{ $user->insertion }}@endif {{ $user->last_name }}

But doing that each time a full name is required is quite cumbersome as well. Therefor I created the following scope method:

public function scopeWithFullName( Builder $query ): Builder
{
   return $query->selectRaw("CONCAT_WS(' ', `first_name`, `insertion`, `last_name`) AS `full_name`");
}

This allows me to do the following and show the full name.

$user = User::withFullName()->first();
$user->full_name;

So it would be nice if you could declare a graphql type like below:

type User
{
  first_name: String
  insertion: String
  last_name: String!
  full_name: String @scope(name: "withFullName")
}

Which possible solutions should be considered?

Make it possible to use the @scope directive on a field definition.

[GregPeden]

Yeah @SuperDJ's example is simpler to understand than mine. In my case I was wanting to run a aggregation as a subquery to avoid N+1 problem. Since then you have added the @aggregate directive which possibly runs in a similar way. But there will still be use cases where it's more effective to define it as a scope.

For my particular application I ended up switching to a pre-compute solution, so the aggregate value "record_due_at" is refreshed on the model every time some relevant data is updated.

I know it's a super hacky way to use scopes but it fits within Laravel's ecosystem and it works.