SQLAlchemy dynamic & customized Collection Class

Question

I have the following SQLAlchemy model :

from datetime import date

from app import Base
from sqlalchemy import Column, Integer, String, ForeignKey, Date
from sqlalchemy.orm import relationship

class Article(Base):

    __tablename__ = 'article'

    id = Column(Integer, primary_key=True)
    title = Column(String)

    posts = relationship('Post', back_populates='article')


class Post(Base):

    __tablename__ = 'post'

    id = Column(Integer, primary_key=True)
    article_id = Column(Integer, ForeignKey('article.id'))
    date = Column(Date)

    article = relationship('Article', back_populates='posts')

What I would like is having a fast and flexible way to get the posts of an article article between a date start_date and a date end_date.

I know that I could do:

`session.query(Post).filter(Post.article == article, Post.date >= start_date, Post.date < end_date).all()`

but I find it too long and not 'Object-Oriented' enough.

After some research, I saw that I could use the lazy argument of relationship:

posts = relationship('Post', back_populates='article', lazy='dynamic')

After that, the "posts" attribute isn't a list any longer, but a Query Object, which allows us to do:

article.posts.filter(Post.date >= start_date, Post.date < end_date).all()

But what I would like to do would be:

article.posts[start_date:end_date]

I think I have to use the collection_class attribute, for instance :

class DatedList(list):
    def __getitem__(self, item):
        if isinstance(item, slice):
            # What to do here ?
        else:
            super(DatedList, self).__getitem__(item)

posts = relationship('Post', back_populates='article', lazy='dynamic', collection_class=DatedList)

But I don't know what to write inside the __getitem__ function, because we don't have access to the query given by the relationship Object !

Does someone have an idea which could help me?

Answer 1

I found the solution by posting on the SQLAlchemy Google Group.

As said by Mike Baker, the collection_class doesn't work with the lazy argument set at dynamic, so I had to use the query_class argument:

from sqlalchemy.orm import Query


class PostClassQuery(Query):

    def __getitem__(self, item):

        if isinstance(item, slice) and (isinstance(item.start, date) or isinstance(item.stop, date)):
            query = self
            if isinstance(item.start, date):
                query = query.filter(Post.date >= item.start)
            if isinstance(item.stop, date):
                query = query.filter(Post.date < item.stop)
            return query.all()
        else:
            super(PostClassQuery, self).__getitem__(item)

and after in the Article entity:

posts = relationship('Post', back_populates='article', lazy='dynamic', query_class=PostClassQuery)

If you want, you can even return a Query instead of the result, by removing .all() in return.query.all(), which would allow you to do something like article.posts[date(2015, 1, 1):date(2015, 6, 1)][10:20].