Suppose you have a complex Django QuerySet query that is somewhat costly (in other words slow). And suppose you want to return:
- The first N results
- A count of the total possible results
So your implementation might be something like this:
def get_results(queryset, fields, size):
count = queryset.count()
results = []
for record in queryset.values(*fields)[:size]
results.append(record)
return {"count": count, "results": results}
That'll work. If there are 1,234 rows in your database table that match those specific filters, what you might get back from this is:
>>> results = get_results(my_queryset, ("name", "age"), 5)
>>> results["count"]
1234
>>> len(results["results"])
5
Or, if the filters would only match 3 rows in your database table:
>>> results = get_results(my_queryset, ("name", "age"), 5)
>>> results["count"]
3
>>> len(results["results"])
3
Between your Python application and your database you'll see:
query 1: SELECT COUNT(*) FROM my_database WHERE ...
query 2: SELECT name, age FROM my_database WHERE ... LIMIT 5
The problem with this is that, in the latter case, you had to send two database queries when all you needed was one.
If you knew it would only match a tiny amount of records, you could do this:
def get_results(queryset, fields, size):
- count = queryset.count()
results = []
for record in queryset.values(*fields)[:size]:
results.append(record)
+ count = len(results)
return {"count": count, "results": results}
But that is wrong. The count would max out at whatever the size is.
The solution is to try to avoid the potentially unnecessary .count() query.
def get_results(queryset, fields, size):
count = 0
results = []
for i, record in enumerate(queryset.values(*fields)[: size + 1]):
if i == size:
count = queryset.count()
break
count = i + 1
results.append(record)
return {"count": count, "results": results}
This way, you only incur one database query when there wasn't that much to find, but if there was more than what the pagination called for, you have to incur that extra database query.
