CODEWITHSUNDEEP
pythonsqlitepeewee
stenci
stenci

Reputation: 8481

Operator NOT IN with Peewee

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

Upvotes: 16

Views: 7973

Answers (3)

coleifer
coleifer

Reputation: 26245

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))

Upvotes: 34

MarSoft
MarSoft

Reputation: 3913

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.

Upvotes: 4

kindall
kindall

Reputation: 184201

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.

Upvotes: 1

Related Questions