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.

Answer 1

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