title: sqlalchemy pagination
body: <p>I'm building a REST app with flask and sqlalchemy and I came across an issue. I want to query all users with their number of books. Each user has many books so my query should return the number of books each user has in the resultset.</p>
  
  <pre><code>//      Models
  class User( object ):
      __tablename__ = 'user'
  
  class Book( object ):
      __tablename__ = 'book'
  
  //      Metadata
  users_table = Table( 'user', metadata,
      Column( 'id', Integer, primary_key = True ),
      Column( 'username', String( 50 ), unique = True )
  )
  
  books_table = Table( 'book', metadata,
      Column( 'id', Integer, primary_key = True ),
      Column( 'title', String( 50 ) ),
      Column( 'user_id', Integer, ForeignKey( 'user.id' ) )
  )
  
  //      Mappers
  mapper( User, users_table, properties = {
      'booksCount': column_property( 
          select( 
              [func.count( books_table.c.id )],
              books_table.c.user_id == users_table.c.id
          ).label( 'booksCount' )
      ),
      'books' : relationship( Book )
  } )
  
  mapper( Book, books_table, properties = {
      'user': relationship( User )
  } )
  </code></pre>
  
  <p>If I want to query all users it's working fine and brings back the results with associated 'booksCount' as it should but if I want to go deeper and let's say query only users with a 'booksCount' greater than 4 it's getting complicated since I also need to know the total results count before applying limit/offset in order for my pagination to work.</p>
  
  <pre><code>//  this works
  rows = User.query
  totalRows = rows.count ()
  users = rows.limit( 50 ).offset( 0 ).all()
  
  for user in users:
      ...
  
  //  this throws an error
  rows = User.query.having('booksCount&gt;4')
  totalRows = rows.count ()
  users = rows.limit( 50 ).offset( 0 ).all()
  </code></pre>
  
  <p>The reason for this failure in the 2nd example is because <code>totalRows = rows.count ()</code> creates a second query to count the first results: <code>SELECT count(1) AS count_1 FROM user</code> but when <code>having</code> is inserted into the query it changes to <code>SELECT count(1) AS count_1 FROM user having booksCount&gt;4</code> which obvioulsy rises an error because booksCount was never selected in this 2nd query.</p>
  
  <p>So how do I go about extracting the total rows on a select with a <code>having</code> clause applied?</p>
  
  <p>Thanks.</p>