Example of Parameter Grouping in Laravel Eloquent combining AND and OR clauses

Background

Eloquent provides a beautiful wrapper around MySQL queries, but venturing into more complex queries needs a bit of thinking. This article demonstrates a simple use case for Task Management. Say for example you want to find all New and In Progress tasks created by a specific user ID and also assigned to the same user ID.

In plain English: Find all current tasks that I have created or that have been assigned to me.

In MySQL it will look like this:

SELECT
   id, user_id, assigned_to_id, status
FROM tasks
   WHERE
      (user_id = 1 OR assigned_to_id = 1)
   AND
      (status = 'in_progress' OR status = 'new')

In Eloquent it’s called Parameter Grouping and as a global scope it will look like this:

class DefaultTasksScope implements Scope
{
    /**
     * Only display current tasks created by or assigned to auth() user
     *
     * The scope can be removed by using `withoutGlobalScope`
     *
     * @param  \Illuminate\Database\Eloquent\Builder $builder
     * @param  \Illuminate\Database\Eloquent\Model $model
     * @return void
     */
    public function apply(Builder $builder, Model $model)
    {
        $builder
            ->where(function ($query) {
                $query
                    ->where('status', Tasks::NEW)
                    ->orWhere('status', Tasks::IN_PROGRESS);
            })
            ->where(function ($query) {
                $query
                    ->whereUserId(auth()->id())
                    ->orWhere('assigned_to_id', auth()->id());
            });
    }
}

Leave us a reply with any other interesting Parameter Grouping queries you’ve had to build.