[extensions-web/bugfix/search-sort] search: use `uuid` as extra order key to make pagination persistent.

[Yuri Konotopov <ykonotopov src gnome org>]

commit 737fb028548d530872ca2d0b105d44c8a18cba17
Author: Yuri Konotopov <ykonotopov gnome org>
Date:   Sun Sep 25 20:47:29 2022 +0400

    search: use `uuid` as extra order key to make pagination persistent.
    
    This is obviously needed to make limited search results persistent and we
    never experienced pagination inconsistence with MariaDB. However with
    MySQL 8 search results are not persistent so we need to explicitly use
    unique key for ordering.
    
    Fixes: https://gitlab.gnome.org/Infrastructure/extensions-web/-/issues/209

 .../0012_extensionversion_extension_id__status_idx.py   | 17 +++++++++++++++++
 sweettooth/extensions/models.py                         |  4 ++++
 sweettooth/extensions/views.py                          | 16 +++++++++++++++-
 3 files changed, 36 insertions(+), 1 deletion(-)
---
diff --git a/sweettooth/extensions/migrations/0012_extensionversion_extension_id__status_idx.py 
b/sweettooth/extensions/migrations/0012_extensionversion_extension_id__status_idx.py
new file mode 100644
index 0000000..bcc794b
--- /dev/null
+++ b/sweettooth/extensions/migrations/0012_extensionversion_extension_id__status_idx.py
@@ -0,0 +1,17 @@
+# Generated by Django 3.2.15 on 2022-09-25 20:31
+
+from django.db import migrations, models
+
+
+class Migration(migrations.Migration):
+
+    dependencies = [
+        ('extensions', '0011_extension_allow_comments'),
+    ]
+
+    operations = [
+        migrations.AddIndex(
+            model_name='extensionversion',
+            index=models.Index(fields=['extension', 'status'], name='extension_id__status_idx'),
+        ),
+    ]
diff --git a/sweettooth/extensions/models.py b/sweettooth/extensions/models.py
index 760eb1f..9490a0a 100644
--- a/sweettooth/extensions/models.py
+++ b/sweettooth/extensions/models.py
@@ -378,6 +378,10 @@ class ExtensionVersion(models.Model):
         unique_together = ('extension', 'version'),
         get_latest_by = 'version'
 
+        indexes = (
+            models.Index(fields=('extension', 'status'), name='extension_id__status_idx'),
+        )
+
     def __str__(self):
         return "Version %d of %s" % (self.version, self.extension)
 
diff --git a/sweettooth/extensions/views.py b/sweettooth/extensions/views.py
index 4e71621..38fa12a 100644
--- a/sweettooth/extensions/views.py
+++ b/sweettooth/extensions/views.py
@@ -186,6 +186,20 @@ def ajax_query_params_query(request, versions, n_per_page):
     if versions is not None:
         version_qs = version_qs.filter(shell_versions__in=versions)
 
+    '''
+    TODO: this is produces temporary table.
+    SELECT DISTINCT
+       `extensions_extension`.`id`, `extensions_extension`.`name`, `extensions_extension`.`uuid`, 
`extensions_extension`.`slug`,
+       `extensions_extension`.`creator_id`, `extensions_extension`.`description`, 
`extensions_extension`.`url`,
+       `extensions_extension`.`created`, `extensions_extension`.`downloads`, 
`extensions_extension`.`popularity`,
+       `extensions_extension`.`allow_comments`, `extensions_extension`.`screenshot`, 
`extensions_extension`.`icon`
+    FROM `extensions_extension`
+    INNER JOIN `extensions_extensionversion` ON (`extensions_extension`.`id` = 
`extensions_extensionversion`.`extension_id`)
+    WHERE `extensions_extensionversion`.`id` IN (SELECT U0.`id` FROM `extensions_extensionversion` U0 WHERE 
U0.`status` = 3)
+    ORDER BY `extensions_extension`.`popularity` DESC
+
+    We must cache "active" ExtensionVersion state in Extension model and use it in filter
+    '''
     queryset = models.Extension.objects.distinct().filter(versions__in=version_qs)
 
     uuids = request.GET.getlist('uuid')
@@ -197,7 +211,7 @@ def ajax_query_params_query(request, versions, n_per_page):
     if sort not in ('created', 'downloads', 'popularity', 'name'):
         raise Http404()
 
-    queryset = queryset.order_by(sort)
+    queryset = queryset.order_by(sort, 'uuid')
 
     # Sort by ASC for name, DESC for everything else.
     if sort == 'name':