CODEWITHSUNDEEP
phplaraveleloquentscope
tomloprod
tomloprod

Reputation: 7892

Eloquent "Query Builder" with "OR" operator neutralizes my Global Scope - Laravel

⛳ What I need:

I am developing an application in Laravel 5.4 and I want a global scope that allows me to filter different elements of the application depending on the user that created them.

🌍 My global scope:

I have a class BaseEloquentModel.php who extends Eloquent and all my models extends from this class. I have a global scope as follow:

protected static function boot()
{
    parent::boot();
    static::addGlobalScope('', function(\Illuminate\Database\Eloquent\Builder $builder) use($userId) {
        /**
        * I get the name of the table with <code>(with(new static))->getTable()</code> 
        * and then filter the query for the <b>user_id</b> field
        */
        $builder->where(
            (with(new static))->getTable() . '.user_id', 
            '=',
            $userId
        );
    });
}

⛔ The problem

When I have a query like this, with or operator, the global scope is "neutralized": 

$qBuilderCars = Car::whereRaw("name like ? or id = ?", [
    '%' . $searchCriteria. '%',
    $searchCriteria
]);

If I call the toSql() method on $qBuilderCars I see that it "correctly" adds the AND operator to the end of the query.

select * from `cars` where name like ? or id = ? and user_id = ?

Maybe you've already noticed my problem ... If the element's builder, in this case cars, has used an OR operator, then the global scope will not help, since there is no parenthesis between where name like ? or id = ?. So the resulting query would be something similar to the following:

select * from `cars` where name like ? (or id = ? and user_id = ?)

So this query will return all cars whose name matches or whose ID is the one received and has been created by the user...

When what I need is:

select * from `cars` where (name like ? or id = ?) and user_id = ?

👎 My attempts

I tried to alter my global scope to try to make the AND operator that I add the most restrictive in the query, but without any success.

I can not manually add parentheses to all the application's queries, so ... Is there a way to add global parentheses from the global scope to the builder?

💡 The solution

The solution is to add parentheses to all the raw queries.

Upvotes: 8

Views: 1140

Answers (2)

tomloprod
tomloprod

Reputation: 7892

😕 The solution...?

I found a "solution", before apply my global scope I loop through all where clauses whose type is raw: 

protected static function boot()
{
    parent::boot();
    static::addGlobalScope('', function(\Illuminate\Database\Eloquent\Builder $builder) use($userId) {

       /**
        * My workaround to prevent a raw query from neutralizing the global scope.
        * We go through all the queries and, if they are raw, encapsulate them in parentheses.
        */
        $wheres = $builder->getQuery()->wheres;
        foreach ($wheres as $iWhere => $where) {
            // If where clause is "raw" I wrap with parenthesis.
            if ($where['type'] == 'raw') {
                $builder->getQuery()->wheres[$iWhere]["sql"] = "({$where['sql']})";
            }                                                      
        }

       /**
        * I get the name of the table with <code>(with(new static))->getTable()</code> 
        * and then filter the query for the <b>user_id</b> field
        */
        $builder->where(
            (with(new static))->getTable() . '.user_id', 
            '=',
            $userId
        );
    });
}

I do not know if this solution will have any unexpected repercussion or if it will affect in excess the performance of the queries...

Will return the next SQL:

select * from `cars` where (name like ? or id = ?) and `cars`.`user_id` = ?"

Upvotes: 0

Styx
Styx

Reputation: 10086

Well, it seems that your solution to add parentheses is the best workaround, but I have a suggestion how to do that slightly better way.

  1. Create new class QueryBuilder. For example, in \App\Models\ namespace (app/Models/ folder):

    namespace App\Models;

use Illuminate\Database\Query\Builder as EloquentQueryBuilder;

class QueryBuilder extends EloquentQueryBuilder {

  public function whereRaw($sql, $bindings = [], $boolean = 'and')
  {
    return parent::whereRaw('('.$sql.')', $bindings, $boolean);
  }

}

  2. Add this code to your BaseEloquentModel class:

    use Illuminate\Database\Eloquent\Model;
use App\Models\QueryBuilder; // <-- addition

class BaseEloquentModel extends Model {
  // ...
  protected function newBaseQueryBuilder()
  {
    $connection = $this->getConnection();

    return new QueryBuilder(
        $connection,
        $connection->getQueryGrammar(),
        $connection->getPostProcessor()
    );
  }
  // ...
}

Now, all whereRaw() calls will automatically have parentheses around query.

Upvotes: 2

Related Questions