Skip to content
This repository has been archived by the owner on Jul 16, 2021. It is now read-only.

[Proposal] Add whereNot() to the Query Builder #708

Closed
DrowningElysium opened this issue Jul 31, 2017 · 9 comments
Closed

[Proposal] Add whereNot() to the Query Builder #708

DrowningElysium opened this issue Jul 31, 2017 · 9 comments

Comments

@DrowningElysium
Copy link

Background

In the Query Builder there are some type of NOT queries, like whereNotIn(), whereNotNull(), whereNotBetween(), and whereNotExists(). But there is no whereNot().

This makes it impossible to prioritize a query and then not allowing any of it to be true.
Like the following query:
select * from table where b=true and NOT (e != 1 and f < 5 and (g != null and h >= 150))

So my suggestion is to add this type of query.
@joshmanders
Copy link

Do it up, buttercup. Submit that PR. 🥇

@gibtang
Copy link

gibtang commented Oct 10, 2017 •
edited
Loading

I don't understand the SQL statement 'select * from table where b=true and NOT (e != 1 and f < 5 and (g != null and h >= 150))'. How does whereNo() fit in when people can use "where('status', '<>', 1)"

@DrowningElysium
Copy link
Author

whereNot() would be usefull if the sub part is not allowed to return true, it would make queries like these a lot simpler as it is hard to turn it around so it will return false instead of true.

@gibtang
Copy link

gibtang commented Oct 11, 2017

I see. So whereNot will convert the result of the subquery from true to false and vice versa?

@DrowningElysium
Copy link
Author

No it will just add the not before the subquery brackets () which you currently can't do in eloquent

@ghost
Copy link

ghost commented Jan 13, 2018

Looking for this myself. Upvote. Would love to know where the short term workaround is.

@taylorotwell
Copy link
Member

Acceptance just depends on how complicated it is to implement.

jonasof added a commit to jonasof/laravelframework that referenced this issue May 1, 2018
@jonasof
Add whereNot operator
03c74eb 
By adding this operator we can negate a complex query in
a expressive way. For example, the query pointed by
@DrowningElysium:

`select * from table where b=true and NOT (e != 1 and
f < 5 and (g != null and h >= 150))`

To be done currently in laravel query builder it's
necessary to invert all operators (`and` to `or`, `!=`
to `==`, `<` to `>=`, `>=` to `<`). If we need to do
the negation dynamically we will need to maintain two
querys, increasing the possibility of bugs. For example:

```
DB::table('table')
  ->where('b', true)
  ->when($invert, function ($query) {
    return $query
      ->where('e', '!=', 1)
      ->where('f', '<', 5)
      ->where(function ($query) {
        return $query->whereNotNull('g')
          ->where('h', '>=', 150);
      });
   }, function ($query) {
    return $query
      ->where('e', '=', 1)
      ->orWhere('f', '>=', 5)
      ->orWhere(function ($query) {
        return $query->whereNull('g')
          ->orWhere('h', '<', 150);
      });
   });
```

With whereNot we can writte this query as:

```
$subquery = function ($query) {
  return $query
    ->where('e', '!=', 1)
    ->where('f', '<', 5)
    ->where(function ($query) {
      return $query->whereNotNull('g')
        ->where('h', '>=', 150);
    });
};

DB::table('table')
  ->where('b', true)
  ->when($invert, function ($query) use ($subquery) {
      return $query->whereNot($subquery);
  }, function ($query) use ($subquery) {
      return $query->where($subquery);
  });

# Or just:

DB::table('table')
  ->where('b', true)
  ->{$invert ? 'where' : 'whereNot'}($subquery);
 ```

Which is more simple and legible to read.

We only allow calling whereNot in callback form:
$query->whereNot($callback); because allowing the
use as column `whereNot($column)` can confuse the
developer since the behaviour of not() in some fields,
like varchar, is different from  php !$string.

Resolve laravel/ideas#708
jonasof added a commit to jonasof/laravelframework that referenced this issue May 1, 2018
@jonasof
Add whereNot operator
ddec5ba 
By adding this operator we can negate a complex query in a expressive way.
For example, the query pointed by @DrowningElysium:

`select * from table where b=true and NOT (e != 1 and f < 5 and (g != null and h >= 150))`

To be done currently in laravel query builder it's necessary to invert all
operators (`and` to `or`, `!=` to `==`, `<` to `>=`, `>=` to `<`). If we
need to do the negation dynamically we will need to maintain two querys,
increasing the possibility of bugs. For example:

```
DB::table('table')
  ->where('b', true)
  ->when($invert, function ($query) {
    return $query
      ->where('e', '!=', 1)
      ->where('f', '<', 5)
      ->where(function ($query) {
        return $query->whereNotNull('g')
          ->where('h', '>=', 150);
      });
   }, function ($query) {
    return $query
      ->where('e', '=', 1)
      ->orWhere('f', '>=', 5)
      ->orWhere(function ($query) {
        return $query->whereNull('g')
          ->orWhere('h', '<', 150);
      });
   });
```

With whereNot we can writte this query as:

```
$subquery = function ($query) {
  return $query
    ->where('e', '!=', 1)
    ->where('f', '<', 5)
    ->where(function ($query) {
      return $query->whereNotNull('g')
        ->where('h', '>=', 150);
    });
};

DB::table('table')
  ->where('b', true)
  ->when($invert, function ($query) use ($subquery) {
      return $query->whereNot($subquery);
  }, function ($query) use ($subquery) {
      return $query->where($subquery);
  });

DB::table('table')
  ->where('b', true)
  ->{$invert ? 'where' : 'whereNot'}($subquery);
 ```

Which is more simple and legible to read.

We only allow calling whereNot in callback form: $query->whereNot($callback);
because allowing the use as column `whereNot($column)` can confuse the developer
since the behaviour of not() in some fields, like varchar, is different
from php !$string.

Resolve laravel/ideas#708
jonasof added a commit to jonasof/laravelframework that referenced this issue May 1, 2018
@jonasof
Add whereNot operator
8bb0957 
By adding this operator we can negate a complex query in a expressive way.
For example, the query pointed by @DrowningElysium:

`select * from table where b=true and NOT (e != 1 and f < 5 and (g != null and h >= 150))`

To be done currently in laravel query builder it's necessary to invert all
operators (`and` to `or`, `!=` to `==`, `<` to `>=`, `>=` to `<`). If we
need to do the negation dynamically we will need to maintain two querys,
increasing the possibility of bugs. For example:

```
DB::table('table')
  ->where('b', true)
  ->when($invert, function ($query) {
    return $query
      ->where('e', '!=', 1)
      ->where('f', '<', 5)
      ->where(function ($query) {
        return $query->whereNotNull('g')
          ->where('h', '>=', 150);
      });
   }, function ($query) {
    return $query
      ->where('e', '=', 1)
      ->orWhere('f', '>=', 5)
      ->orWhere(function ($query) {
        return $query->whereNull('g')
          ->orWhere('h', '<', 150);
      });
   });
```

With whereNot we can writte this query as:

```
$subquery = function ($query) {
  return $query
    ->where('e', '!=', 1)
    ->where('f', '<', 5)
    ->where(function ($query) {
      return $query->whereNotNull('g')
        ->where('h', '>=', 150);
    });
};

DB::table('table')
  ->where('b', true)
  ->when($invert, function ($query) use ($subquery) {
      return $query->whereNot($subquery);
  }, function ($query) use ($subquery) {
      return $query->where($subquery);
  });

DB::table('table')
  ->where('b', true)
  ->{$invert ? 'where' : 'whereNot'}($subquery);
 ```

Which is more simple and legible to read.

We only allow calling whereNot in callback form: $query->whereNot($callback);
because allowing the use as column `whereNot($column)` can confuse the developer
since the behaviour of not() in some fields, like varchar, is different
from php !$string.

Resolve laravel/ideas#708
jonasof added a commit to jonasof/laravelframework that referenced this issue May 1, 2018
@jonasof
Add whereNot operator
cdbc83a 
By adding this operator we can negate a complex query in a expressive way.
For example, the query pointed by @DrowningElysium:

`select * from table where b=true and NOT (e != 1 and f < 5 and (g != null and h >= 150))`

To be done currently in laravel query builder it's necessary to invert all
operators (`and` to `or`, `!=` to `==`, `<` to `>=`, `>=` to `<`). If we
need to do the negation dynamically we will need to maintain two querys,
increasing the possibility of bugs. For example:

```
DB::table('table')
  ->where('b', true)
  ->when($invert, function ($query) {
    return $query
      ->where('e', '!=', 1)
      ->where('f', '<', 5)
      ->where(function ($query) {
        return $query->whereNotNull('g')
          ->where('h', '>=', 150);
      });
   }, function ($query) {
    return $query
      ->where('e', '=', 1)
      ->orWhere('f', '>=', 5)
      ->orWhere(function ($query) {
        return $query->whereNull('g')
          ->orWhere('h', '<', 150);
      });
   });
```

With whereNot we can writte this query as:

```
$subquery = function ($query) {
  return $query
    ->where('e', '!=', 1)
    ->where('f', '<', 5)
    ->where(function ($query) {
      return $query->whereNotNull('g')
        ->where('h', '>=', 150);
    });
};

DB::table('table')
  ->where('b', true)
  ->when($invert, function ($query) use ($subquery) {
      return $query->whereNot($subquery);
  }, function ($query) use ($subquery) {
      return $query->where($subquery);
  });

DB::table('table')
  ->where('b', true)
  ->{$invert ? 'where' : 'whereNot'}($subquery);
 ```

Which is more simple and legible to read.

We only allow calling whereNot in callback form: $query->whereNot($callback);
because allowing the use as column `whereNot($column)` can confuse the developer
since the behaviour of not() in some fields, like varchar, is different
from php !$string.

Resolve laravel/ideas#708
jonasof added a commit to jonasof/laravelframework that referenced this issue May 1, 2018
@jonasof
Add the whereNot operator
195b203 
By adding this operator we can negate a complex query in a expressive way.
For example, the query pointed by @DrowningElysium:

`select * from table where b=true and NOT (e != 1 and f < 5 and (g != null and h >= 150))`

To be done currently in laravel query builder it's necessary to invert all
operators (`and` to `or`, `!=` to `==`, `<` to `>=`, `>=` to `<`). If we
need to do the negation dynamically we will need to maintain two querys,
increasing the possibility of bugs. For example:

```
DB::table('table')
  ->where('b', true)
  ->when($invert, function ($query) {
    return $query
      ->where('e', '!=', 1)
      ->where('f', '<', 5)
      ->where(function ($query) {
        return $query->whereNotNull('g')
          ->where('h', '>=', 150);
      });
   }, function ($query) {
    return $query
      ->where('e', '=', 1)
      ->orWhere('f', '>=', 5)
      ->orWhere(function ($query) {
        return $query->whereNull('g')
          ->orWhere('h', '<', 150);
      });
   });
```

With whereNot we can write this query as:

```
$subquery = function ($query) {
  return $query
    ->where('e', '!=', 1)
    ->where('f', '<', 5)
    ->where(function ($query) {
      return $query->whereNotNull('g')
        ->where('h', '>=', 150);
    });
};

DB::table('table')
  ->where('b', true)
  ->when($invert, function ($query) use ($subquery) {
      return $query->whereNot($subquery);
  }, function ($query) use ($subquery) {
      return $query->where($subquery);
  });

// or just

DB::table('table')
  ->where('b', true)
  ->{$invert ? 'where' : 'whereNot'}($subquery);
 ```

Which is more simple and legible.

We only allow calling whereNot in callback form: $query->whereNot($callback);
because allowing the use as column `whereNot($column)` can confuse the developer
since the behaviour of not() in some fields, like varchar, is different
from php !$string.

Resolve laravel/ideas#708
@jonasof jonasof mentioned this issue May 1, 2018
Closed
jonasof added a commit to jonasof/laravelframework that referenced this issue May 1, 2018
@jonasof
Add the whereNot operator
2b8e7f8 
By adding this operator we can negate a complex query in a expressive way.
For example, the query pointed by @DrowningElysium:

`select * from table where b=true and NOT (e != 1 and f < 5 and (g != null and h >= 150))`

To be done currently in laravel query builder it's necessary to invert all
operators (`and` to `or`, `!=` to `==`, `<` to `>=`, `>=` to `<`). If we
need to do the negation dynamically we will need to maintain two querys,
increasing the possibility of bugs. For example:

```
DB::table('table')
  ->where('b', true)
  ->when($invert, function ($query) {
    return $query
      ->where('e', '!=', 1)
      ->where('f', '<', 5)
      ->where(function ($query) {
        return $query->whereNotNull('g')
          ->where('h', '>=', 150);
      });
   }, function ($query) {
    return $query
      ->where('e', '=', 1)
      ->orWhere('f', '>=', 5)
      ->orWhere(function ($query) {
        return $query->whereNull('g')
          ->orWhere('h', '<', 150);
      });
   });
```

With whereNot we can write this query as:

```
$subquery = function ($query) {
  return $query
    ->where('e', '!=', 1)
    ->where('f', '<', 5)
    ->where(function ($query) {
      return $query->whereNotNull('g')
        ->where('h', '>=', 150);
    });
};

DB::table('table')
  ->where('b', true)
  ->when($invert, function ($query) use ($subquery) {
      return $query->whereNot($subquery);
  }, function ($query) use ($subquery) {
      return $query->where($subquery);
  });

// or just

DB::table('table')
  ->where('b', true)
  ->{$invert ? 'where' : 'whereNot'}($subquery);
 ```

Which is more simple and legible.

We only allow calling whereNot in callback form: $query->whereNot($callback);
because allowing the use as column `whereNot($column)` can confuse the developer
since the behaviour of not() in some fields, like varchar, is different
from php !$string.

Resolve laravel/ideas#708
@aknosis
Copy link

aknosis commented Jun 11, 2019

Would have liked to have this feature as I'm having to do a null safe NOT equal test (https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_equal-to).

For standard not equal you can do <> or != but for null safe you can only test "equality" as <=>. So to truly say is NOT null safe equal I need computed SQL NOT column <=> 'value' but I can't do this with the builder.

(<=> works with query builder operator now but I have no way to prepend the NOT)

@spawnia spawnia mentioned this issue Jan 8, 2020
Open
@waska14
Copy link

waska14 commented Apr 14, 2020 •
edited
Loading

Updated on 2020.06.15:
You can use this HasWhereNotTrait in your model and whereNot and orWhereNot local scopes will be added on the model.

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Bank extends Model
{
    use \App\Traits\HasWhereNotTrait;
    // Other stuff
}

I know, this is an old question, but from my point of view, you can achieve your goal with where().

As I understand, you want something like this:

DB::table('temp')->where('column_a', 1)->whereNot(function ($query) {
    $query->where('column_b', 2)->orWhere('column_c', 3);
});

SQL query will be:

SELECT *
FROM temp
WHERE column_a = 1
    AND NOT (column_b = 2 OR column_c = 3);

But there is no whereNot, right?

If so, you can simply use where method and pass string $boolean parameter as 'and not' (default is 'and'.

DB::table('temp')->where('column_a', 1)->where(function ($query) {
    $query->where('column_b', 2)->orWhere('column_c', 3);
}, null, null, 'and not');

SQL query will be the same as I mentioned above.

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
6 participants
@aknosis @joshmanders @taylorotwell @gibtang @DrowningElysium @waska14