sqlalchemy pagination

Question

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.

//      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 )
} )

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.

//  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>4')
totalRows = rows.count ()
users = rows.limit( 50 ).offset( 0 ).all()

The reason for this failure in the 2nd example is because totalRows = rows.count () creates a second query to count the first results: SELECT count(1) AS count_1 FROM user but when having is inserted into the query it changes to SELECT count(1) AS count_1 FROM user having booksCount>4 which obvioulsy rises an error because booksCount was never selected in this 2nd query.

So how do I go about extracting the total rows on a select with a having clause applied?

Thanks.

Answer 1

This is actually an sql syntax error. having clause is used only when you have group by clause included as well, and only filtering the aggregate results. What you need is a simple where clause achieved with :

rows = User.query.filter(User.booksCount > 4)

as a side note: please do your code by the python standards, e.g. User.books_count