CODEWITHSUNDEEP
pythonsortingsqlalchemy
Veky
Veky

Reputation: 2745

Custom collections in SQL Alchemy

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?

Upvotes: 1

Views: 197

Answers (1)

plaes
plaes

Reputation: 32716

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

See also the Ordering List extension.

Upvotes: 1

Related Questions