how to limit/offset sqlalchemy orm relation's result?

Question

in case i have a user Model and article Model, user and article are one-to-many relation. so i can access article like this

user = session.query(User).filter(id=1).one()
print user.articles

but this will list user's all articles, what if i want to limit articles to 10 ? in rails there is an all() method which can have limit / offset in it. in sqlalchemy there also is an all() method, but take no params, how to achieve this?

Edit:

it seems user.articles[10:20] is valid, but the sql didn't use 10 / 20 in queries. so in fact it will load all matched data, and filter in python?

Solution

The solution is to use a dynamic relationship as described in the collection configuration techniques section of the SQLAlchemy documentation.

By specifying the relationship as

class User(...):
    # ...
    articles = relationship('Articles', order_by='desc(Articles.date)', lazy='dynamic')

you can then write user.articles.limit(10) which will generate and execute a query to fetch the last ten articles by the user. Or you can use the [x:y] syntax if you prefer which will automatically generate a LIMIT clause.

Performance should be reasonable unless you want to query the past ten articles for 100 or so users (in which instance at least 101 queries will be sent to the server).

Comments

[Lucas Andrade]

Saved my day, I was trying to run order desc inside of a subquery just for the nested relationships, it's sooo hard (no examples around the internet)