Peewee SQL query join where none of many match
<p>The following SQL finds all posts which haven't any associated tags named 'BadTag'.</p>

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

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

<pre><code>Post.select().where(
    ~Tag.select()
    .where(Post.id == Tag.post &amp; Tag.name=='BadTag')
    .exists()
)
</code></pre>

<p>it gets compiled to</p>

<pre><code>SELECT "t1"."id", ... FROM "post" AS t1 WHERE ? [-1]
</code></pre>

<p>Something like</p>

<pre><code>Post.select().join(Tag).where(Tag.name!='BadTag')
</code></pre>

<p>doesn't work since a Post can have many Tags.</p>

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