Laravel Many-to-Many (on the same users table/Model): Query scopes to include related for the specified user

Question

Users can block each other. One user can block many (other) users, and one user can be blocked by many (other) users. In User model I have these many-to-many relationships:

/**
 * Get the users that are blocked by $this user.
 *
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function blockedUsers()
{
    return $this->belongsToMany(User::class, 'ignore_lists', 'user_id', 'blocked_user_id');
}

/**
 * Get the users that blocked $this user.
 *
 * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
 */
public function blockedByUsers()
{
    return $this->belongsToMany(User::class, 'ignore_lists', 'blocked_user_id', 'user_id');
}

(ignore_lists is the pivot table and it has id, user_id, 'blocked_user_id' columns)

I want to create the following Query Scopes:

1) To include users that are blocked by the specified user ($id):

/**
 * Scope a query to only include users that are blocked by the specified user.
 *
 * @param \Illuminate\Database\Eloquent\Builder $query
 * @param $id
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function scopeAreBlockedBy($query, $id)
{
    // How to do this? :)
}

Example of usage: User::areBlockedBy(auth()->id())->where('verified', 1)->get();

2) To include users that are not blocked by the specified user ($id):

/**
 * Scope a query to only include users that are not blocked by the specified user.
 *
 * @param \Illuminate\Database\Eloquent\Builder $query
 * @param $id
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function scopeAreNotBlockedBy($query, $id)
{
    // How to do this? :)
}

Example of usage: User::areNotBlockedBy(auth()->id())->where('verified', 1)->get();

3) To include users that blocked the specified user ($id):

/**
 * Scope a query to only include users that blocked the specified user.
 *
 * @param \Illuminate\Database\Eloquent\Builder $query
 * @param $id
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function scopeWhoBlocked($query, $id)
{
    // How to do this? :)
}

Example of usage: User::whoBlocked(auth()->id())->where('verified', 1)->get();

4) To include users that did not block the specified user ($id):

/**
 * Scope a query to only include users that did not block the specified user.
 *
 * @param \Illuminate\Database\Eloquent\Builder $query
 * @param $id
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function scopeWhoDidNotBlock($query, $id)
{
    // How to do this? :)
}

Example of usage: User::whoDidNotBlock(auth()->id())->where('verified', 1)->get();

---

How would you do this? I didn't find anything in the Laravel docs about this (maybe I missed it). (I'm using Laravel 6.x)

I'm not sure, but I think this could be done in two ways: Using Left Join or using raw queries in whereIn... I may be wrong, but I think the "left join" solution would be better as far as performance is concerned, right? (not sure about this, maybe I'm totally wrong).

Answer 1

Use join(inner join) performance is better than whereIn subquery.

In MySQL, subselects within the IN clause are re-executed for every row in the outer query, thus creating O(n^2).

I think use whereHas and whereDoesntHave for query will be more readable.

1) The relationship method blockedUsers() has already include users that are blocked by the specified user ($id), you can use this method directly:

User::where('id', $id)->first()->blockedUsers();

Considerate about applying the where('verified', 1) at first, so you can use query like User::where('verified', 1)->areBlockedBy(auth()->id()), the scope can be like this:

public function scopeAreBlockedBy($query, $id)
{
    return $query->whereHas('blockedByUsers', function($users) use($id) {
               $users->where('ignore_lists.user_id', $id);
           });
}

// better performance: however, when you apply another where condition, you need to specify the table name ->where('users.verified', 1)
public function scopeAreBlockedBy($query, $id)
{
    return $query->join('ignore_lists', function($q) use ($id) {
               $q->on('ignore_lists.blocked_user_id', '=', 'users.id')
                 ->where('ignore_lists.user_id', $id);
           })->select('users.*')->distinct();
}

We use join for the second query that will improve the performance because it doesn't need to use where exists.

Example for 300,000+ records in users table:

Explain the first query whereHas which scan 301119+1+1 rows and takes 575ms:

Explain the second query join which scan 3+1 rows and takes 10.1ms:

2) To include users that are not blocked by the specified user ($id), you can use whereDoesntHave closure like this one:

public function scopeNotBlockedUsers($query, $id)
{
    return $query->whereDoesntHave('blockedByUsers', function($users) use ($id){
           $users->where('ignore_lists.user_id', $id);
     });
}

I prefer to use whereDoesntHave instead of leftJoin here. Because when you use leftjoin like this below:

User::leftjoin('ignore_lists', function($q) use ($id) {                                                            
     $q->on('ignore_lists.blocked_user_id', '=', 'users.id') 
       ->where('ignore_lists.user_id', $id);
})->whereNull('ignore_lists.id')->select('users.*')->distinct()->get();

Mysql need to create an temporary table for storing all the users' records and combine some ignore_lists.And then scan these records and find out the records which without ignore_lists. whereDosentHave will scan all users too. For my mysql server, where not exists is a little faster than left join. Its execution plan seems good. The performance of these two queries are not much different.

For whereDoesntHave is more readable. I will choose whereDoesntHave.

3) To include users that blocked the specified user ($id), to use whereHas blockedUsers like this:

public function scopeWhoBlocked($query, $id)
{
    return $query->whereHas('blockedUsers', function($q) use ($id) {
                $q->where('ignore_lists.blocked_user_id', $id);
           });
}

// better performance: however, when you apply another where condition, you need to specify the table name ->where('users.verified', 1)
public function scopeWhoBlocked($query, $id)
{
    return $query->join('ignore_lists', function($q) use ($id) {
               $q->on('ignore_lists.user_id', '=', 'users.id')
                 ->where('ignore_lists.blocked_user_id', $id);
           })->select('users.*')->distinct();
}

4) To include users that did not block the specified user ($id), use whereDoesntHave for blockedByUsers:

public function scopeWhoDidNotBlock($query, $id)
{
    return $query->whereDoesntHave('blockedUsers', function($q) use ($id) {
                $q->where('ignore_lists.blocked_user_id', $id);
           });
}

PS: Remember to add index on foreign_key for ignore_lists table.

Answer 2

You can use Querying Relationship Existence whereHas and Querying Relationship Absence whereDoesntHave query builder functions to build your result queries.

I have included each query generated SQL code and query time in milliseconds tested on a dual Xeon dedicated server on a table that has 1000 users.

We don't want to get current user in the results when querying with areNotBlockedBy and whoDidNotBlock, so these functions will exclude the user with $id.

1. To include users that are blocked by the specified user ($id):

   /**
    * Scope a query to only include users that are blocked by the specified user.
    *
    * @param \Illuminate\Database\Eloquent\Builder $query
    * @param $id
    * @return \Illuminate\Database\Eloquent\Builder
    */
   public function scopeAreBlockedBy($query, $id)
   {
       return User::whereHas('blockedByUsers', function($q) use($id) {
           $q->where('user_id', $id);
       });
   }
   

   Executing:

   User::areBlockedBy(auth()->id())->where('verified', 1)->get();
   

   Will generate the following SQL:

   -- Showing rows 0 - 3 (4 total, Query took 0.0006 seconds.)
   select * from `users` where exists (select * from `users` as `laravel_reserved_9` inner join `ignore_lists` on `laravel_reserved_9`.`id` = `ignore_lists`.`user_id` where `users`.`id` = `ignore_lists`.`blocked_user_id` and `user_id` = ?) and `verified` = ?
   

2. To include users that are not blocked by the specified user ($id):

   /**
    * Scope a query to only include users that are not blocked by the specified user.
    *
    * @param \Illuminate\Database\Eloquent\Builder $query
    * @param $id
    * @return \Illuminate\Database\Eloquent\Builder
    */
   public function scopeAreNotBlockedBy($query, $id)
   {
       // It will exclude the user with $id
       return User::where('id', '!=', $id)
           ->whereDoesntHave('blockedByUsers', function($q) use($id) {
               $q->where('user_id', $id);
           });
   }
   

   Executing:

   User::areNotBlockedBy(auth()->id())->where('verified', 1)->get();
   

   Will generate the following SQL:

   -- Showing rows 0 - 24 (990 total, Query took 0.0005 seconds.)
   select * from `users` where `id` != ? and not exists (select * from `users` as `laravel_reserved_0` inner join `ignore_lists` on `laravel_reserved_0`.`id` = `ignore_lists`.`user_id` where `users`.`id` = `ignore_lists`.`blocked_user_id` and `user_id` = ?) and `verified` = ?
   

3. To include users that blocked the specified user ($id):

   /**
    * Scope a query to only include users that blocked the specified user.
    *
    * @param \Illuminate\Database\Eloquent\Builder $query
    * @param $id
    * @return \Illuminate\Database\Eloquent\Builder
    */
   public function scopeWhoBlocked($query, $id)
   {
       return User::whereHas('blockedUsers', function($q) use($id) {
           $q->where('blocked_user_id', $id);
       });
   }
   

   Executing:

   User::whoBlocked(auth()->id())->where('verified', 1)->get();
   

   Will generate the following SQL:

   -- Showing rows 0 - 1 (2 total, Query took 0.0004 seconds.)
   select * from `users` where exists (select * from `users` as `laravel_reserved_12` inner join `ignore_lists` on `laravel_reserved_12`.`id` = `ignore_lists`.`blocked_user_id` where `users`.`id` = `ignore_lists`.`user_id` and `blocked_user_id` = ?) and `verified` = ?
   

4. To include users that did not block the specified user ($id):

   /**
    * Scope a query to only include users that did not block the specified user.
    *
    * @param \Illuminate\Database\Eloquent\Builder $query
    * @param $id
    * @return \Illuminate\Database\Eloquent\Builder
    */
   public function scopeWhoDidNotBlock($query, $id)
   {
       // It will exclude the user with $id
       return User::where('id', '!=', $id)
           ->whereDoesntHave('blockedUsers', function($q) use($id) {
               $q->where('blocked_user_id', $id);
           });
   }
   

   Executing:

   User::whoDidNotBlock(auth()->id())->where('verified', 1)->get();
   

   Will generate the following SQL:

   -- Showing rows 0 - 24 (992 total, Query took 0.0004 seconds.)
   select * from `users` where `id` != ? and not exists (select * from `users` as `laravel_reserved_1` inner join `ignore_lists` on `laravel_reserved_1`.`id` = `ignore_lists`.`blocked_user_id` where `users`.`id` = `ignore_lists`.`user_id` and `blocked_user_id` = ?) and `verified` = ?