Dynamic column_property with SQLAlchemy

Question

I've got some SA models and need some trick :

class Entry(Base):
    __tablename__ = 'entry'
    id = Column(Integer, primary_key=True)
    title = Column(Unicode(255))
    author_id = Column(Integer, ForeignKey('user.id'))
    date = Column(DateTime)
    content = Column(Text)
    author = relationship('User', backref='entries')

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    username = Column(Unicode(255))
    ...

As you can see this is very classical, users write entries... I need to render some statistics about them (like show their entries per week/month...)

For counting entries i added a column_property to the user model like so:

class User(Base):
    ...
    entries_count = column_property(select([func.count(Entry.id)]).\
                                            where(Entry.author_id==id))

That let me showing how many entries has been writen by users. But to make some statistics given a date range, i will need to adapt dynamically the entries_count to add the dates criterias.

So the question is : how would you do to manage the date criterias?? is column_property the best solution for that kind of need??

Thanks by advance.

Answer 1

Adding property is a good way to get some database state related to the object. But with external criterion parameter the count won't be just a state, but a function. Representing such data as object property won't be good. So query for additional data directly (counting antries newer than start_date in all examples below):

session.query(User, func.count(Entry.id))\
    .outerjoin((Entry, (Entry.author_id==User.id) & (Entry.date>start_date)))\
    .group_by(User.id)

Or define a helper method (not property!) in User class to simplify usage:

class User(Base):
    # ...
    @classmethod
    def entries_count(cls, cond):
        return select([func.count(Entry.id)])\
                .where((Entry.author_id==cls.id) & cond)\
                .as_scalar()

session.query(User, User.entries_count(Entry.date>start_date))