16

The documentation shows here how to use the IN operator, but I couldn't find how to use the NOT IN operator.

If I put a not << I get a syntax error.

If I put a not <FieldName> << there is a WHERE False instead of a subquery like WHERE (<FieldName> NOT IN (SELECT ....

Here is the output with the documentation examples. The first one is correct, the second and third are wrong.

>>> Tweet.select().where(Tweet.user << a_users).sql()
('SELECT t1."id", t1."user_id", t1."message", t1."created_date", t1."is_published" FROM "tweet" AS t1 WHERE (t1."user_id" IN (SELECT t2."id" FROM "user" AS t2 WHERE (Lower(Substr(t2."username", ?, ?)) = ?)))', [1, 1, 'a'])
>>> Tweet.select().where(not Tweet.user << a_users).sql()
('SELECT t1."id", t1."user_id", t1."message", t1."created_date", t1."is_published" FROM "tweet" AS t1 WHERE ?', [False])
>>> Tweet.select().where(Tweet.user not << a_users).sql()
SyntaxError: invalid syntax
stenci
  • 8,290
  • 14
  • 64
  • 104
  • `not in` is literally a single operator named `not in`, it doesn't mean you can put `not` before any other operator. The fact that Peewee reinterprets `<<` to mean a SQL `IN` doesn't mean it can change Python syntax. – abarnert Sep 08 '14 at 23:46
  • @abarnert I know... hence my question – stenci Sep 09 '14 at 03:43

3 Answers3

33

Simple:

Tweet.select().where(Tweet.user.not_in(a_users))

For slightly different semantics (NOT (x in y)) as opposed to (x NOT IN y):

Tweet.select().where(~(Tweet.user << a_users))
coleifer
  • 24,887
  • 6
  • 60
  • 75
  • 1
    Yeah, that makes sense, `~` is overrideable and doesn't need to return a Boolean. It doesn't seem to be documented anywhere though. – kindall Sep 09 '14 at 20:25
  • Kindall, can you kindly update or remove you answer? It is not correct and may cause confusion to other readers. – coleifer Sep 09 '14 at 20:28
  • Is there an `exists` operator? I find it weird that you support window functions but not `not` and `exists` – ypercubeᵀᴹ Sep 09 '14 at 20:31
  • `fn.EXISTS()` is how you would accomplish that. – coleifer Sep 09 '14 at 20:39
  • You cannot override `not` and `in` because the Python interpreter coerces the return value to a boolean. Obviously I would have preferred to do it that way. – coleifer Sep 09 '14 at 20:40
  • I did think I would need an unary negation operator, and I tried `!`. I didn't think of `~`. Thanks – stenci Sep 10 '14 at 14:00
4

I know that this is a "necro-posting", but this question is first hit in Google for peewee not in query, so I would like to add it here:

You can also use not_in method which is described in the doc:

Tweet.select().where(Tweet.user.not_in(a_users))

As for me, it looks much more readable than ~ ... << construct.

MarSoft
  • 3,555
  • 1
  • 33
  • 38
  • 1
    Hey, thanks for mentioning this! I've updated my answer to include your edits so hopefully more folks will see it. – coleifer Jan 07 '16 at 04:15
1

This has nothing to do with Peewee, really. Peewee is using some Python operators for its own purposes. << is a numeric operator normally, and it doesn't make any sense to take its logical negation. Thus not << is never valid Python syntax.

Your second example is close, but not applies only to Tweet.user (not having higher precedence than <<). Add some parentheses and you get:

Tweet.select().where(not (Tweet.user << a_users)).sql()

Now this still isn't right, as you've discovered (readers: see the comments for some discussion on this). not returns a Boolean value, which is not what is wanted and won't work. Peewee repurposes the ~ operator for this; take a look at @coleifer's answer.

coleifer
  • 24,887
  • 6
  • 60
  • 75
kindall
  • 178,883
  • 35
  • 278
  • 309
  • In case you're wondering _why_ Peewee would use `<<` instead of just using `in` (in which case you could use `not in` here); the `in` operator has to return a `bool`, not a query object or anything else. (What happens if it doesn't return a `bool` depends on your Python implementation and version; it may be an error, or it may get converted to `bool`, but either way, it's not very useful…) – abarnert Sep 08 '14 at 23:49
  • I don't understand your answer. The way I understand it, those operators are used by Peewee to create an SQL command, they are not interpreted by Python. Infact if you try your expression you get a `... WHERE False`, as in my second case without the parenthesis. – stenci Sep 09 '14 at 03:42
  • They can't *not* be interpreted by Python. Peewee provides special Python objects that override operators such as `<<` to construct a SQL query. In other words, there is nothing in Peewee that ever sees `Tweet.user << a_users` and does something with it, that's all done by methods on `Tweet.user`. The result is a kind of domain-specific language (DSL) that works seamlessly with Python because it *is* Python. Unfortunately, I guessed wrong about a solution to your problem. – kindall Sep 09 '14 at 04:14
  • Looking at [Query operators](http://peewee.readthedocs.org/en/latest/peewee/querying.html#query-operators) in the Peewee docs, it doesn't seem to support `not`, or `~`, or a method or function named `not_` or `unary_not`… that can't be true, can it? Well, if it is, [Adding user-defined operators](http://peewee.readthedocs.org/en/latest/peewee/querying.html#adding-user-defined-operators) shows how you can do it. – abarnert Sep 09 '14 at 19:36
  • 1
    @stenci: Also, I'd file a bug against Peewee pointing out that either it has no way to write `NOT` queries, or (if it does) that it's too hard to figure out what that way is from the docs… – abarnert Sep 09 '14 at 19:53