Issue: Add NOT IN operator.
Author: rammie
State: closed
Created: 2013-06-13 10:05:15

I'm not sure whether to file this in flask_peewee or peewee. But the lack of a not in operator in the DJANGO map prevents such queries from the rest api. Django allows this by using exclude. Adding NOT IN as an operator probably isn't the most elegant fix. But it would mitigate my problem.


If you would like to negate an expression and are using the "django-style" lookups, you will need to wrap it in a `DQ` object and use unary negation:

``` python

# this query
.where(~DQ(foo__in=[list of stuff]))

# translates into this
-- where NOT foo IN [list of stuff]
```

Unfortunately, I don't think this helps with your use-case of the flask-peewee rest api.  I'm not sure I like the idea of adding NOT IN, but I see your point about the difficulty.  Will think about it.


One possibility is to make the rest operators a superset of the django ones. This seems somewhat principaled. More so than adding it to the DJANGO_MAP.


Out of interest, why doesn't peewee use Python's `in` operator (i.e., overload `__contains__`) to represent SQL "x in (1, 2, 3)" instead of the weird-looking `<<`? This would also work for SQL's NOT IN -- same as Python. Relatedly, it'd be nice if `x == None` (can't use `x is None` fo this) translated to SQL `x IS NULL` to avoid the extra `>>` operator.


Using `in` and `__contains__` won't work (unfortunately) because python will always convert the return value of contains to a boolean.  I'd rather not special-case `==` to handle `NULL` because semantically `IS NULL` is distinct.


Huh, I didn't know that about `__contains__` -- pity. Fair enough about not special-case `==` -- seems a shame to use a whole operator for this though.


Just an FYI: I asked about why `__contains__` was like that on Python-Dev, and got [this helpful answer](http://mail.python.org/pipermail/python-dev/2013-July/127297.html) from Nick Coghlan, a Python core dev.


Thanks @benhoyt I was not aware of the rationale for this behavior.


@rammie, if you pull the latest peewee and flask-peewee, I have added support for negating expressions in the REST api by prefixing the left-hand lookup with "-", so:

```
/api/user/?username=foo  --  users named "foo"
/api/user/?-username=foo  --  users *not* named "foo"
```

Give it a try and let me know how it works for you.


Thanks a lot. I really like the solution. Is legit. I'll play with it a
little and let you know tomorrow. I hope a version bump follows soon.
On Jul 21, 2013 7:46 PM, "Charles Leifer" notifications@github.com wrote:

> @rammie https://github.com/rammie, if you pull the latest peewee and
> flask-peewee, I have added support for negating expressions in the REST api
> by prefixing the left-hand lookup with "-", so:
> 
> /api/user/?username=foo  --  users named "foo"
> /api/user/?-username=foo  --  users _not_ named "foo"
> 
> Give it a try and let me know how it works for you.
> 
> —
> Reply to this email directly or view it on GitHubhttps://github.com/coleifer/peewee/issues/204#issuecomment-21319460
> .


Tried it, works well for my use cases. Thanks!

On Sun, Jul 21, 2013 at 10:27 PM, Ram Mehta ram.mehta@gmail.com wrote:

> Thanks a lot. I really like the solution. Is legit. I'll play with it a
> little and let you know tomorrow. I hope a version bump follows soon.
> On Jul 21, 2013 7:46 PM, "Charles Leifer" notifications@github.com
> wrote:
> 
> > @rammie https://github.com/rammie, if you pull the latest peewee and
> > flask-peewee, I have added support for negating expressions in the REST api
> > by prefixing the left-hand lookup with "-", so:
> > 
> > /api/user/?username=foo  --  users named "foo"
> > /api/user/?-username=foo  --  users _not_ named "foo"
> > 
> > Give it a try and let me know how it works for you.
> > 
> > —
> > Reply to this email directly or view it on GitHubhttps://github.com/coleifer/peewee/issues/204#issuecomment-21319460
> > .