Query manipulation when using joinedload

[Mick Heywood]

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:

class NoArchivesQuery(Query):

  def __iter__(self):

    return Query.__iter__(self._add_archive_filter())

  def from_self(self, *ent):

    return Query.from_self(self._add_archive_filter(), *ent)

  def _add_archive_filter(self):

    entities = self._entities

    for entity in entities:

      if entity.entity_zero and hasattr(entity.entity_zero, 'class_'):

        tables_involved_in_the_query.add(entity.entity_zero.class_.__table__)

    filter_crits = []

    for table in tables_involved_in_the_query:

    if hasattr(table.c, "is_archived"):

      filter_crits.append(or_(table.c.is_archived == None, table.c.is_archived == False))

    if filter_crits:

      return self.enable_assertions(False).filter(*filter_crits)

 I can get as far as 

SELECT anon_1.*, child.*

FROM (

  SELECT parent.*

  FROM parent

  WHERE parent.id = 1234 AND (parent.is_archived IS NULL OR parent.is_archived = false)

  LIMIT 1) AS anon_1

LEFT OUTER JOIN child ON child.parent_id = parent.id

But this does not filter the children, and so I get all archived children back.  What I would like to get back is more along the lines of:

SELECT anon_1.*, child.*

FROM (

  SELECT parent.*

  FROM parent

  WHERE parent.id = 1234 AND (parent.is_archived IS NULL OR parent.is_archived = false)

  LIMIT 1) AS anon_1

LEFT OUTER JOIN child ON child.parent_id = parent.id AND (child.is_archived IS NULL OR child.is_archived = false)

Is that sort of manipulation of a joinedload possible at this level?  Do I need to look somewhere else in the query processing pipeline?  Or is it really not feasible?

Thanks,

Mick

[Michael Bayer]

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