Include total count in query results with limit and offset

[Derek Lambert]

Is it possible to return a query's results as a dictionary containing the total count and a limited portion of the results?

query = session.query(Item)

results = {

    'total_count': query.count(),

    'data':        query.offset(0).limit(50).all(),

}

assert session.query(magic_here).more_magic().offset(0).limit(50).all() == results

I feel like I accidentally hit on something like this a while back, but can't recall how or find any similar examples.

Thanks,

Derek

[Jonathan Vanasco]

On Monday, April 1, 2019 at 12:10:52 PM UTC-4, Derek Lambert wrote:
> Is it possible to return a query's results as a dictionary containing the total count and a limited portion of the results?

You'd be a lot better off trying to accomplish this with a simple helper function than trying to change what query/all actually returns.

def pretty_results(query):
    return {
       'total_count': query.count(),
       'data': query.offset(0).limit(50).all(),
       }

[Derek Lambert]

Thanks, that does make more sense.

I ended up writing a little wrapper that allows me to create the query in my service including the offset/limit and continue to add additional filters, etc. when it's run.

class PagedQuery:

    def __init__(self, query: orm.Query):

        self.logger: logging.Logger = logging.getLogger(self.__module__)

        self.query: orm.Query = query

        self._limit: int  = None

        self._offset: int = None

    def __getattr__(self, item: Text):

        try:

            return self.__getattribute__(item)

        except AttributeError:

            def call(*args, **kwargs):

                self.query = getattr(self.query, item)(*args, **kwargs)

                return self

            return call

    def limit(self, limit: int):

        self._limit = limit

        return self

    def offset(self, offset: int):

        self._offset = offset

        return self

    def all(self):

        count = self.query.count()

        self.logger.debug(f"Paging results, count={count}, offset={self._offset}, limit={self._limit}")

        data = self.query \

            .limit(self._limit) \

            .offset(self._offset) \

            .all()

        return {

            'total_count': count,

            'data':        data

        }

[Jonathan Vanasco]

On Wednesday, April 3, 2019 at 1:40:16 PM UTC-4, Derek Lambert wrote:

Thanks, that does make more sense.

I ended up writing a little wrapper that allows me to create the query in my service including the offset/limit and continue to add additional filters, etc. when it's run.

I actually do something similar. I often write queries with a class that exposes a `paginated` and `count` method, and a private '_core' to construct the base query for both.  

from my experience, two things to watch out for:

* be sure to support order_by, especially when dealing with tests
* if your project grows complex, you may need to use completely different queries for `total_count` and `data`.  with simple queries and small datasets you don't... but i've written a handful of things where the `count` only needs to address one table but the `data` is built off a multi-table join and various relationship loading techniques.