sqlalchemy conditional multiple filters on dynamic lazy relationship

Question

I am using sqlalchemy with the following models

class Page(db.Model):
     id= ..
     posts = db.relationship('Post', lazy='dynamic')

class Post(db.Model):
   id=..
   page_id=..
   author= db.Column(db.String)
   date= db.Column(db.DateTime)

in the Page class I have a method to get the page's posts for a specific date and author, it looks like that

def author_posts(author, start_date=None, end_date=None):
    p= self.posts.filter(Post.author == author)

    if start_date:
       p.filter(Post.date >= start_date)

    if end_date:
       p.filter(Post.date <= end_date)

    return p

The problem is, even if the function is given a start and end date, it returns post filtered by author but never by the dates argument.

What's the right way to do it?

Edit: The query generated

SELECT post.id AS post_id, post.page_id AS post_page_id, post.author AS post_author ... FROM post WHERE post.author = ?

Answer 1

filter() returns a new query object, but you do not store it. Replace p with the result each time:

if start_date:
   p = p.filter(Post.date >= start_date)

if end_date:
   p = p.filter(Post.date <= end_date)

return p