Returningquery results with the result number

[Eduardo]

Hi,
In order to avoid bottlenecks I am force to limit the number of returned results using LIMIT and OFFSET.
Since I am not returning all results upon a query I need to include the number of hits in the result.
somequery.count()
somequery.limit(n).offset(m).all()
The problem is that response time takes twice as long as for either the count query or the query retrieving results.
Is there any way to do this more efficiently, to make a query first, then to count results and return the result chunk defined with LIMIT and OFFSET?
What is the best practice for this?
Thanks
ED

[Conor]

If your DB supports window functions and you are using SQLAlchemy 0.7+, you can include func.count().over() in your query, which effectively includes somequery.count() as a column in each row.

So this:

somequery = session.query(SomeClass).filter(...)
count = somequery.count()
some_class_instances = somequery.limit(n).offset(m).all()

becomes:

rows = session.query(SomeClass, func.count().over().label("count")).filter(...).limit(n).offset(m).all()
if rows:
    count = rows[0][1]
    some_class_instances = [row[0] for row in rows]
else:
    # Either no rows matched or the limit+offset is out of range. We will assume the former.
    count = 0
    some_class_instances = []

-Conor

[Conor]

Follow-up: if somequery contains joinedloads, then using func.count().over() will not give you the same result as somequery.count(). You should be able to get around this by using func.count(SomeClass.id.distinct()).over() instead.

-Conor