Pagination not working as expected

[calinbule]

I have a Django application that is integrated with a Postgres database. In the front-end I use DataTables and, Python-side, I use django-ajax-datatable.

I am facing an issue that I don't really know how to tackle namely, when the table is sorted on certain columns, some records that appear towards the end of the current page, will also be present in the second page.

Ex:

• in the screenshots below, the table is sorted by the DR field
• in the first image, the last record is steemit.com
• however, steemit.com also appears on the second page, which it should not, given that it's the exact same object (not a duplicate)

I will provide the code for the aforementioned table:

The Python class:

class BaseAjaxDatatableView(AjaxDatatableView):
    title = None
    initial_order = [["site_name", "asc"], ]
    length_menu = [[100, 50, 20, 10, -1], [100, 50, 20, 10, 'all']]
    search_values_separator = '+'
    show_column_filters = True

    column_defs = [
        {'name': 'id', 'visible': False, },
        {
            'name': 'name',
            'title': 'NAME',
            'visible': False,
            'searchable': False,
            'orderable': False,
            'className': 'table-text table-link table_filter'
         },
        {
            'name': 'site_name',
            'title': 'LINK',
            'visible': True,
            'placeholder': True,
            'searchable': True,
            'orderable': True,
            'className': 'table-link table_filter table-text',
        },
        {'name': 'price', 'title': 'PRICE', 'visible': True, 'className': 'align-center table-text', 'searchable': False },
        {'name': 'dr', 'title': 'DR', 'visible': True, 'className': 'align-center table-text', 'searchable': False},
        {'name': 'cf', 'title': 'CF', 'visible': True, 'className': 'align-center table-text', 'searchable': False  },
        {'name': 'tf', 'title': 'TF', 'visible': True, 'className': 'align-center table-text', 'searchable': False  },
        {'name': 'da', 'title': 'DA', 'visible': True, 'className': 'align-center table-text', 'searchable': False  },
        {'name': 'ahrefs_traffic', 'title': 'TRAFFIC <span class="info-tooltip" data-bs-toggle="tooltip" data-bs-placement="top" title="AHREFS TRAFFIC">i</span> ', 'visible': True, 'className': 'align-center table-text', 'searchable': False},
        {'name': 'ahrefs_organic_keywords', 'title': 'ORGANIC KEYWORDS <span class="info-tooltip" data-bs-toggle="tooltip" data-bs-placement="top" title="AHREFS ORGANIC KEYWORDS">i</span> ', 'visible': True, 'className': 'align-center table-text', 'searchable': False},
        {'name': 'mozrank', 'title': 'MOZRANK', 'visible': True, 'className': 'align-center table-text', 'searchable': False  },
        {'name': 'semrush_traffic', 'title': 'SEMRUSH <span class="info-tooltip" data-bs-toggle="tooltip" data-bs-placement="top" title="SEMRUSH TRAFFIC">i</span> ', 'visible': True, 'className': 'align-center table-text', 'searchable': False  },
        {
            'name': 'type',
            'title': 'TYPE',
            'visible': True,
            'className': 'align-center table-text',
            'autofilter': True,
            'choices': True,
         },
        {
            'name': 'category',
            'title': 'CATEGORY',
            'foreign_field': 'category__category_name',
            'visible': True,
            'className': 'align-center table-text',
            'autofilter': True,
            'choices': True,
         },
    ]
    
class EwAjaxDatatableView(BaseAjaxDatatableView):
    model = EnglishWebsite
    title = 'EnglishWebsite'

JS Code in template:

$(document).ready(function() {

    AjaxDatatableViewUtils.initialize_table(
        $('#table'),
        "{{ api_url }}",
        {
            processing: true,
            autoWidth: true,
            full_row_select: false,
            scrollX: true,
            pagingType: "full_numbers",
            pageLength: 20,
            "columnDefs": [
               { "orderSequence": [ "desc", "asc" ], "targets": [ 4, 5, 6, 7, 8, 9 ] },
            ],
            initComplete: function () {
                $('[data-bs-toggle="tooltip"]').tooltip();
            }
        },
    );
});

The Django model:

class BaseWebsiteModel(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    name = models.CharField(max_length=255, blank=False, null=False)
    site_name = models.CharField(max_length=255, blank=True, null=True)
    price = models.FloatField(validators=[MinValueValidator(0.0)], blank=False, null=False, default=0.0)
    dr = models.IntegerField()
    cf = models.IntegerField()
    tf = models.IntegerField()
    da = models.IntegerField()
    ahrefs_traffic = models.IntegerField()
    ahrefs_organic_keywords = models.IntegerField()
    mozrank = models.FloatField()
    semrush_traffic = models.IntegerField()
    type = models.CharField(max_length=255, blank=False, null=False)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    details = models.CharField(max_length=255, blank=True, null=True)

    def __str__(self):
        return self.name

    class Meta:
        abstract = True


class Website(BaseWebsiteModel):
    class Meta:
        abstract = True


class EnglishWebsite(Website):
    category = models.ForeignKey(Category, related_name="ew_category", on_delete=models.CASCADE)

    class Meta:
        verbose_name = 'Global website'
        verbose_name_plural = 'Global websites'

I am using:

• Django 4.0.3
• django-ajax-datatable 4.4.4
• DataTables 1.11.5

Please give me a hand in investigating this issue. I want to determine if it's a bug or now and how to fix it.

Thank you in advance.

[morlandi]

I would add an appropriate default ordering to the Model to make sure that when issuing a second query to populate the second page, the underlying queryset is extracted in a stable manner

[calinbule]

Thanks for the recommendation. I will give it a try.

However, the thing is that when sorted by site_name (the default sort field in DataTables) there are no issues. If I sort by dr, or cf (numerical values) some of the records that are displayed towards the end of page N are also displayed among the first records of page N+1.

One thing I noticed is that the records that are repeated, seem to all have the same value, in the sorted field. Going back to the screenshots above, both steemit.com and anchoratemes.com (the two records that are duplicated on the second page) are part of the subset of records that have dr=84. I will go on a limb to presume that this might have something to do with the reason why the values are duplicated, even though I can't say for sure.

Do you think that providing a default ordering to the Django model will help?

On another train of thought, in light of the new information provided, maybe something else comes to mind, some other way to investigate/fix this?

[morlandi]

I recently had a similar problem in a completely different context, and I do believe it is an SQL-related issue.
When paginating, you should always provide a list of fields to sort on such that, when repeating the same query twice, you're guaranteed to receive the resulting recordset in the same order (as long as no data has been modified).
Otherwise, splitting the recordset in pages could very well produce unpredictable results.

[calinbule]

Hey. I also tried declaring default ordering model-wise but to no avail. It behaves the same.

I did, however, notice that the sub-group of fields that have the DR value 84, are not sorted by any other criteria than DR. Is there any possibility to customize sorting and also add other sorting criteria?

Do you happen to have any other recommendations? Thank you