Column_property dynamic property with SQLAlchemy

Question

I have several SA models and you need a 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 classic, users record notes ... I need to make some statistics about them (for example, show their records in a week / month ...)

To count records, I added column_property to the user model :

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

Let me show you how many posts were written by users. But for some statistics to specify a date range, I will need to dynamically adapt entries_count to add date criteria.

So the question is: how would you handle the date criteria? is column_property the best solution for this kind of need?

Thanks in advance.

Answer 1

Adding a property is a good way to get some database state associated with an object. But with the external parameter of the criterion, the score will be not just a state, but a function. Presenting data such as an object property will not be good. Thus, the query is for additional data directly (counting antries later 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 a property!) In the User class to simplify use:

 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))