SqlAlchemy - apply filter before join statement

Question

I try to add some contribution on 3c7's cool project, and I want to apply a filter on a join query(sqlalchemy).

Simple statement: multiple rules(table) can have multiple tags(table) - I want to filter out some rules based on some tags.

[Rules][2] table   (rule_id, etc)
[Tags][2] Table    (tag_id, etc)
tags_rules(Junction table) (rule_id,tag_id) -- no declaration

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

Sql alchemy declaration:

class Rule(Base):
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    name = Column(String(255), index=True)
    meta = relationship("Meta", back_populates="rule", cascade="all, delete, delete-orphan")
    strings = relationship("String", back_populates="rule", cascade="all, delete, delete-orphan")
    condition = Column(Text)
    imports = Column(Integer)
    tags = relationship("Tag", back_populates="rules", secondary=tags_rules)
    ruleset_id = Column(Integer, ForeignKey("ruleset.id"))
    ruleset = relationship("Ruleset", back_populates="rules")

class Tag(Base):
    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    name = Column(String(255), index=True)
    rules = relationship("Rule", back_populates="tags", secondary=tags_rules)

I tried with sub queries but the most feasible seem to apply a filter on the tags table before the join.

Current implementation:

rules = rules.select_from(Tag).join(Rule.tags).filter(~Tag.name.in_(tags))

Any idea is greatly appreciated.

SqlAlchemy - apply filter before join statement

0 Answers