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




commit 83e33b478e775609efa3bbe65b34728ef27b2300
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

 sweettooth/extensions/views.py | 16 +++++++++++++++-
 1 file changed, 15 insertions(+), 1 deletion(-)
---
diff --git a/sweettooth/extensions/views.py b/sweettooth/extensions/views.py
index 4e71621..be228b2 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 implies full table scan.
+    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':


[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]