title: Limit child collections in initial query sqlalchemy
body: <p>I am building an api which can return children of resources if the user requests it. For example, <code>user</code> has <code>messages</code>. I want the query to be able to limit the number of <code>message</code> objects that are returned.</p>
  
  <p>I found a useful tip aboutl imiting the number of objects in child collections <a href="https://stackoverflow.com/questions/9148316/how-to-limit-offset-sqlalchemy-orm-relations-result?noredirect=1&amp;lq=1">here</a>. Basically, it indicates the following flow:</p>
  
  <pre><code>class User(...):
      # ...
      messages = relationship('Messages', order_by='desc(Messages.date)', lazy='dynamic')
  
  user = User.query.one()
  users.messages.limit(10)
  </code></pre>
  
  <p>My use case involves returning sometimes large numbers of users. </p>
  
  <p>If I were to follow the advice in that link and used <code>.limit()</code> then I would need to iterate over the entire collection of users calling <code>.limit()</code> on each one. This is much less efficient then, say, using <code>LIMIT</code> in the original sql expression which created the collection. </p>
  
  <p>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?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>To be clear, the below is what I am trying to <em>avoid</em>.</p>
  
  <pre><code>users = User.query.all()
  messages = {}
  for user in users:
      messages[user.id] = user.messages.limit(10).all()
  </code></pre>
  
  <p>I want to do something more like:</p>
  
  <pre><code>users = User.query.option(User.messages.limit(10)).all()
  </code></pre>