I am building an api which can return children of resources if the user requests it. For example, user has messages. I want the query to be able to limit the number of message objects that are returned.
I found a useful tip about limiting the number of objects in child collections here <http://stackoverflow.com/questions/9148316/how-to-limit-offset-sqlalchemy-orm-relations-result?noredirect=1&lq=1>. Basically, it indicates the following flow: class User(...): # ... messages = relationship('Messages', order_by='desc(Messages.date)', lazy='dynamic') user = User.query.one() users.messages.limit(10) My use case involves returning sometimes large numbers of users. If I were to follow the advice in that link and used .limit() then I would need to iterate over the entire collection of users calling .limit() on each one. This is much less efficient then, say, using LIMITin the original sql expression which created the collection. My question is whether it is possible using declarative to efficiently(N+0) load a large collection of objects while limiting the number of children in their child collections using sqlalchemy? To be clear, the below is what I am trying to *avoid*. users = User.query.all() messages = {}for user in users: messages[user.id] = user.messages.limit(10).all() I want to do something more like: users = User.query.option(User.messages.limit(10)).all() SO post here: http://stackoverflow.com/questions/43727268/limit-child-collections-in-initial-query-sqlalchemy/43727705?noredirect=1#comment74499641_43727705 -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.