CODEWITHSUNDEEP
pythonpaginationsqlalchemyflask-sqlalchemy
carl
carl

Reputation: 4426

empty paginate object sqlalchemy

I am using sqlalchemy to retrieve a list of objects using the paginate function 

results = models.Object.query.filter(models.Object.id.in_(list_of_ids)).paginate(page, 10, False)

Sometimes list_of_ids can be empty. I noticed that queries where this list is empty are very slow. There is also a warning telling me that querying empty lists can be slow. However, to keep my functionality simple I need an empty paginate object. Is there a way to get that without running a query?

Upvotes: 1

Views: 1320

Answers (1)

mhawke
mhawke

Reputation: 87084

You could probably mess around and instantiate your own instance of an empty Pagination object in order to avoid a query, but it might just be easier to perform a simple, fast, query that returns no rows:

>>> empty_paginator = models.Object.query.filter_by(id=None).paginate()
>>> empty_paginator.items
[]
>>> empty_paginator.has_next
False

The query should be very fast as it is on the table's primary key and will therefore use an index. Just make sure that you select something that doesn't exist, e.g. an id of None. Then use it like this:

if list_of_ids:
    results = models.Object.query.filter(models.Object.id.in_(list_of_ids)).paginate(page, 10, False)
else:
    results = models.Object.query.filter_by(id=-1).paginate()

Another way would be to subclass a Pagination object, overriding the methods that require a valid query object in order to avoid making a query:

class EmptyPaginator(flask.ext.sqlalchemy.Pagination):
    def __init__(self):
        super(EmptyPaginator, self).__init__(None, 1, 1, 0, [])

    def next(self, error_out=False):
        self.page += 1
        return self

    def prev(self, error_out=False):
        self.page -= 1
        return self

Use it like this:

if list_of_ids:
    results = models.Object.query.filter(models.Object.id.in_(list_of_ids)).paginate(page, 10, False)
else:
    results = EmptyPaginator()

or this (more succinct but less readable):

results = models.Object.query.filter(models.Object.id.in_(list_of_ids)).paginate(page, 10, False) if list_of_ids else EmptyPaginator()

Upvotes: 1

Related Questions