Fair enough, I was expecting something like that to be the case anyhow. Thank you very much!
On Thursday, October 18, 2018 at 2:33:20 PM UTC-4, Mike Bayer wrote: > > On Thu, Oct 18, 2018 at 12:31 PM Daniel Cardin <[email protected] > <javascript:>> wrote: > > > > 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? > > you would have to do something crazy like make the joinedload() > against a new relationship that has the modified join condition. > maybe if there were an option for joinedload to not use aliases, which > was discussed in a different thread, that might make things easier. > But if you're dealing with collections, the "selectin" strategy, > especially the new enhancements coming in 1.3 which omit the JOIN > entirely, is probably a better eager loader in any case. > > > > > > 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 <[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] <javascript:>. > > To post to this group, send email to [email protected] > <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 [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.
