Peewee SQL query join where none of many match

Question

The following SQL finds all posts which haven't any associated tags named 'BadTag'.

select * from post t1
where not exists
(select 1 from tag t2
   where t1.id == t2.post_id and t2.name=='BadTag');

How can I write this functionality in Peewee ORM? If I write something along the lines of

Post.select().where(
    ~Tag.select()
    .where(Post.id == Tag.post & Tag.name=='BadTag')
    .exists()
)

it gets compiled to

SELECT "t1"."id", ... FROM "post" AS t1 WHERE ? [-1]

Something like

Post.select().join(Tag).where(Tag.name!='BadTag')

doesn't work since a Post can have many Tags.

I'm new to SQL/Peewee so if this is a bad way to go about things I'd welcome pointers.

See [here](http://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exist?lq=1) for the way to do what you want with a `LEFT JOIN`, and then convert that to PeeWee. — Barmar, Mar 09 '16 at 23:55

score 3 · Accepted Answer · answered Mar 10 '16 at 20:58

3

Do not use manecosta's solution, it is inefficient.

Here is how to do a NOT EXISTS with a subquery:

(Post
 .select()
 .where(~fn.EXISTS(
      Tag.select().where(
          (Tag.post == Post.id) & (Tag.name == 'BadTag'))))

You can also do a join:

(Post
 .select(Post, fn.COUNT(Tag.id))
 .join(Tag, JOIN.LEFT_OUTER)
 .where(Tag.name == 'BadTag')
 .group_by(Post)
 .having(fn.COUNT(Tag.id) == 0))

answered Mar 10 '16 at 20:58

coleifer

Thanks, that works. Could you explain the difference between your `~fn.EXISTS(...)` and my `(...).exists()`? – Samizdis Mar 10 '16 at 22:17
1

.exists() evaluates the query and returns a boolean. fn.exists creates an EXISTS subquery. – coleifer Mar 12 '16 at 02:07

1 Answers1