On Sep 24, 2013, at 12:52 AM, Mick Heywood <[email protected]> wrote: > Hi, > > I'm attempting to do some universal filtering using a custom Query class. In > this case, I'm trying to filter out all items marked as archived in two > related classes. I'm having some trouble adding the required filters to the > query at all the right levels. > > I'm using Flask 0.9, SQLAlchemy 0.8 and PostgreSQL 9.1.5 > > Let's call the two classes Parent and Child, which are inheriting from > Archivable: > > class Archivable(object): > @declared_attr > def is_archived(cls): > return Column('is_archived', types.Boolean, nullable=False, > default=False, index=True) > > class Parent(base, Archivable): > __tablename__ = 'parent' > id = Column('id', types.BigInteger, primary_key=True, nullable=False) > is_archived = > > class Child(base, Archivable): > __tablename__ = 'child' > id = Column('id', types.BigInteger, primary_key=True, nullable=False) > parent_id = Column('id', types.BigInteger, ForeignKey('parent.id'), > nullable=False) > parent = relationship('Parent', primaryjoin='Child.parent_id==Parent.id', > backref='children') > > Somewhere in my code I am calling: > parent = db.session.query(Parent).filter(Parent.id == > 1234).options(joinedload('children')).first() > > This is resulting in a query of the form: > > SELECT anon_1.*, child.* > FROM ( > SELECT parent.* > FROM parent > WHERE parent.id = 1234 > LIMIT 1) AS anon_1 > LEFT OUTER JOIN child ON child.parent_id = parent.id > > which is fine. > > When I try and use a custom query class to access the query and filter it > however, I only seem to be able to access elements of the inner subquery. > self._entities for instance only shows a single _MapperEntity > Mapper|Parent|parent, self.whereclause is a BooleanClauseList of parent.id = > 1234. If I try and inject my filters at this stage using the following:
this is by design. joinedload() is only used for the purposes of loading related items and collections against entities which the Query is loading, so it is applied such that it is always external to the SELECT statement that the Query emits. joinedload() can only load exactly what the original relationship() refers to. If you'd like to join to another table with special criteria and then establish that as a collection load, for simple cases you use contains_eager() for that: http://docs.sqlalchemy.org/en/rel_0_8/orm/loading.html?highlight=contains_eager#contains-eager You can also consider actually using a custom relationship() for loading collections that require special filtering. Basic example is at: http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#specifying-alternate-join-conditions and there is also an example of injecting bound parameters into the primaryjoin (uses lazy load and/or joinedload too), this might be related to what you're trying to do: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/GlobalFilter
signature.asc
Description: Message signed with OpenPGP using GPGMail
