How to set filter to get children in certain time period by eagerload_all() at SqlAlchemy

Question

I have a table posts and it stores 3 types of post, Topic, Reply and Comment. Each one has its parent id.

# Single table inheritance
class Post(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('posts.id'))
    discriminator = Column(String(1))
    content = Column(UnicodeText)
    added_at = Column(DateTime)
    __mapper_args__ = {'polymorphic_on': discriminator}

class Topic(Post):
    replies = relation("Reply")
    __mapper_args__ = {'polymorphic_identity': 't'}

class Reply(Post):
    comments = relation("Comment")
    __mapper_args__ = {'polymorphic_identity': 'r'}

class Comment(Post):
    __mapper_args__ = {'polymorphic_identity': 'c'}

And I'm using eagerload_all() to get all the replies and comments belong to one topic:

session.query(Topic).options(eagerload_all('replies.comments')).get(topic_id)

My question is, if I want to get only replies and those replies' comments in certain time period, for example, this week, or this month. How should I use filter to achieve this?

Thank you

Answer 1

The use of eagerload_all will only query for the children of an object Topic immediately rather on first request to the Replies and/or Comments, but since you load the Topic object into the session, all its related children will be loaded as well. This gives you the first option:

Option-1: Filter in the python code instead of database:
Basically create a method on the Topic object similar to

class Topic(Post):
    ...
    def filter_replies(self, from_date, to_date):
        return [r for r in self.replies
                if  r.added_at >= from_date
                and r.added_at <= to_date]

Then you can do similar code on Replies to filter Comments or any combination of those. You get the idea.

Option-2: Filter on the database level:
In order to achieve this you need not load the Topic object, but filter directly on the Reply/Comment. Following query returns all Reply for a given Topic with a date filter:

topic_id = 1
from_date = date(2010, 9, 5)
to_date = date(2010, 9, 15)
q = session.query(Reply)
q = q.filter(Reply.parent_id == topic_id)
q = q.filter(Reply.added_at >= from_date)
q = q.filter(Reply.added_at <= to_date)
for r in q.all():
    print "Reply: ", r

The version for the Comment is just a little bit more involved as you require an alias in order to overcome the SQL statement generation issue as all your objects are mapped to the same table name:

topic_id = 1
from_date = date(2010, 9, 5)
to_date = date(2010, 9, 15)
ralias = aliased(Reply)
q = session.query(Comment)
q = q.join((ralias, Comment.parent_id == ralias.id))
q = q.filter(ralias.parent_id == topic_id)
q = q.filter(Comment.added_at >= from_date)
q = q.filter(Comment.added_at <= to_date)
for c in q:
    print "Comment: ", c

Obviously you can create a function that would combine both peaces into a more comprehensive query.
In order to achieve this week or this month type of queries you can either convert these filter into a date range as shown above or use the expression.func functionality of SA.