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
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
