title: SqlAlchemy - apply filter before join statement
body: <p>I try to add some contribution on <a href="https://github.com/3c7/yaramanager" rel="nofollow noreferrer">3c7</a>'s cool project, and I want to apply a filter on a join query(sqlalchemy).</p>
  <p>Simple statement: multiple rules(table) can have multiple tags(table) - I want to filter out some rules based on some tags.</p>
  <pre><code>[Rules][2] table   (rule_id, etc)
  [Tags][2] Table    (tag_id, etc)
  tags_rules(Junction table) (rule_id,tag_id) -- no declaration
  </code></pre>
  <p>Issue: Applying a filter after join of course that will remove only the rules that have only one tag(the one that I specify). If a rule has multiple tags, one record from the join result will be removed, but the rule will still appear in there are any other tags associated with that rule</p>
  <p>Sql alchemy declaration:</p>
  <pre><code>class Rule(Base):
      id = Column(Integer, primary_key=True, index=True, autoincrement=True)
      name = Column(String(255), index=True)
      meta = relationship(&quot;Meta&quot;, back_populates=&quot;rule&quot;, cascade=&quot;all, delete, delete-orphan&quot;)
      strings = relationship(&quot;String&quot;, back_populates=&quot;rule&quot;, cascade=&quot;all, delete, delete-orphan&quot;)
      condition = Column(Text)
      imports = Column(Integer)
      tags = relationship(&quot;Tag&quot;, back_populates=&quot;rules&quot;, secondary=tags_rules)
      ruleset_id = Column(Integer, ForeignKey(&quot;ruleset.id&quot;))
      ruleset = relationship(&quot;Ruleset&quot;, back_populates=&quot;rules&quot;)
  
  class Tag(Base):
      id = Column(Integer, primary_key=True, autoincrement=True, index=True)
      name = Column(String(255), index=True)
      rules = relationship(&quot;Rule&quot;, back_populates=&quot;tags&quot;, secondary=tags_rules)
  </code></pre>
  <p>I tried with sub queries but the most feasible seem to apply a filter on the tags table before the join.</p>
  <p>Current implementation:</p>
  <pre><code>rules = rules.select_from(Tag).join(Rule.tags).filter(~Tag.name.in_(tags))
  </code></pre>
  <p>Any idea is greatly appreciated.</p>