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?
Typically when you query the database, the data get loaded at once; however, lazy parameter allows you to alternate the way they get loaded. lazy = 'select' (or True)
all() method. The Query object, when asked to return full entities, will deduplicate entries based on primary key, meaning if the same primary key value would appear in the results more than once, only one object of that primary key would be present.
If you have data for which business objects are not needed, use Core. If you view your data as business objects, use ORM. If you are building a quick prototype, use ORM. If you have a combination of needs that really could leverage both business objects and other data unrelated to the problem domain, use both!
Aliases allow any table or subquery to be referenced by a unique name. In case of a table, this allows the same table to be named in the FROM clause multiple times. It provides a parent name for the columns represented by the statement, allowing them to be referenced relative to this name.
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With