On Wed, Oct 17, 2018 at 11:57 AM Daniel Cardin <[email protected]> wrote: > > I would like add a global filter to all queries emitted against a particular > table. In particular, its important that it doesn't require changes to > existing queries; and it would be nice if there was an escape hatch. > > I'm aware of the following 2 recipes that googling this returns: > https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/PreFilteredQuery > https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter > > GlobalFilter seems to be the way to implement the escape hatch, e.g. > `.options(OmitGlobalFilter())`, or somesuch > > PreFilteredQuery at first seems to be exactly what I want for applying the > filter without changing existing queries, but only appears to work when it is > directly queried against, and not for relationships.
the "select" loader strategy should be applying the criteria for PreFilteredQuery at least, but for other kinds of "lazy" it probably won't. You'd need to write an event handler that intercepts all Query objects unconditionally and carefully tests them to detect the various kinds of loading to apply the criteria you want, this would be using the before_compile event: https://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=before_compile#sqlalchemy.orm.events.QueryEvents.before_compile. The "prefilteredquery" example should really be retired in favor of before_compile in any case. The only loader strategy this wouldn't work with would be "joined", since that loader is not creating a new query, only augmenting an existing one, and that is driven strictly off the relationship loader pattern. > > class Parent(Base): > __tablename__ = 'parent' > id = Column(types.Integer, primary_key=True) > children = relationship('Children', lazy='selectin') # or lazy=joined, > or whatever > > class Child(Base): > __tablename__ = 'child' > id = Column(types.Integer, primary_key=True) > parent_id = Column(ForeignKey('parent.id'), nullable=False) > > class OmitCertainIds(Query): > # ... the beginning portion of PreFilterQuery > def _apply_filter(self): > mzero = self._mapper_zero() > if mzero is None or mzero.class_ != Children: > return self > return self.enable_assertions(False).filter(Parent.id.in_([1, 2])) > > # ... > > # This works great! > db.query(Child).all() > > # This does not apply that filter > db.query(Parent).all()[0].children > > Is there a way to globally filter queries which return Child rows (such as > though that relationship, where its not an explicit `.query` call)? Or does > this require adjusting all such relationships, or other means by which > sqlalcemy may emit queries without a direct query. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
