Custom collections in SQL Alchemy

Question

I use Declarative flavor of SQL Alchemy with a PostgreSQL database. I have a ClaimAccount that manages Claim objects. Something like:

class Claim(MyBase):
    manager_id = Column(UUID, ForeignKey(ClaimAccount.id))
    manager = relationship(ClaimAccount, backref='claims')
    created = Column(DATE)

Now, if a is a ClaimAccount, then a.claims is a list of Claims. That's fine, but I'd like it to be sorted by created column, since I always want to iterate through .claims in order from oldest to newest. Of course I can sort it in Python, but probably it would be better (clearer, faster, DRYer, more convenient) if Postgres did that for me.

At first I thought that the answer was to set collection_class on a relationship to some PriorityQueue or SortedList or whatever, but now I think it would only affect how the claims are stuffed into Python data structures, not how they are fetched from the database. Also, the documentation says that "in most cases you don't need a custom collection implementation" (however, the cases mentioned there do not include mine, it seems).

Do you have any advice on how best to do this?

You can order queryset by any field. See related question: http://stackoverflow.com/questions/4186062/sqlalchemy-order-by-descending — Ihor Pomaranskyy, Jul 09 '15 at 09:57

score 1 · Accepted Answer · answered Jul 09 '15 at 10:00

You can specify the order_by argument to the relationship. Because the order applies to backref, you can do it like this:

manager = relationship(ClaimAccount, backref=backref('claims', order_by=Claims.created.desc()))

Custom collections in SQL Alchemy

1 Answers1