Django django-filter django-tables2 limit query results

Question

I am trying to limit the number of rows displayed in a table filtered using django-filter and build by django-tables2. I did not find anything here or in the docs (I don't want to use pagination). I know I can slice the queryset but I also want to have the table sortable and can't figure out how to do both.

This is my views.py:

def filtered_table(request):
    f = itemFilter(request.GET, queryset=ItemModel.objects.all())
    has_filter = any(field in request.GET for field in set(f.get_fields()))
    table = None
    if has_filter:
        if not request.GET.get('sort'):
            table = ItemTable(f.qs, order_by='-timestamp')
        else:
            table = ItemTable(f.qs, order_by=request.GET.get('sort'))

    return render(request, 'itemlist/filteredlist.html', {
            'itemtable': table,
            'filter': f,
        })

I tried to slice the queryset before passing it to the table:

table = ItemTable(f.qs.order_by('-timestamp')[:20])
table = ItemTable(f.qs.order_by(request.GET.get('sort'))[:20])

Resulting in:

AssertionError: Cannot reorder a query once a slice has been taken.

Because django-tables2 calls .order_by() again. Is there a way to configure django-tables2 or manipulate the queryset to limit the displayed rows?

Update: I tried as suggested, which does not work with my database:

This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

With a slight change this works for me:

f_qs = ItemModel.objects.filter(id__in=list(f_qs_ids))

I think this will now do two queries on the database but that is not a problem for me.

Thank you for answering and pointing me in the right direction. Much appriciated!

Answer 1

This is a bit of a round about way to get there, but you could use your original QuerySet (f.qs), and then take a slice of the obj ids, and then re-filter the original QuerySet with those ids.

# get the 20 ids for the objects you want
f_qs_ids = f.qs.order_by(request.GET.get('sort')).values_list("id", flat=True)[:20]

# create a new queryset by also filtering on the set of 20 ids
f_qs = f.qs.filter(id__in=f_qs_ids)

# pass a legitimate queryset to the table
table = PassTable(f_qs)