sqlite: Peewee SQL query join where none of many match

mercredi 9 mars 2016

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_id & Tag.name=='Field-Notes')
    .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.

sqlite