On Thu, Oct 18, 2018 at 12:31 PM Daniel Cardin <[email protected]> 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]. > 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.
