Ooooo, very nice thanks! This appears to work much more reliably and requires far less code!
We do have some relationships which are using joined loading. And one which is doing Parent -> mapping-table --joinedload--> Child -----selectin--> Parent, which also appears not to work, but I could (naively) see the joinedload in the middle causing that to be the problem. Is there a way to ensure this works for joined relationships as well? On Wednesday, October 17, 2018 at 10:24:50 PM UTC-4, Mike Bayer wrote: > > On Wed, Oct 17, 2018 at 11:57 AM Daniel Cardin <d.d.c...@gmail.com > <javascript:>> 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 sqlalchemy+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
