# HG changeset patch
# User Goffi <goffi@goffi.org>
# Date 1627934203 -7200
# Node ID 7f1394bb96db31ca06d0f25486fa63607a145816
# Parent  1a179ad10125a64c177e104075c908f330ebaff5
psql: fix ordering by using windowed function `row_number`

diff -r 1a179ad10125 -r 7f1394bb96db sat_pubsub/pgsql_storage.py
--- a/sat_pubsub/pgsql_storage.py	Fri Jul 02 15:39:57 2021 +0200
+++ b/sat_pubsub/pgsql_storage.py	Mon Aug 02 21:56:43 2021 +0200
@@ -468,7 +468,7 @@
                                     AND node IN %s
                                     AND nodes.access_model in %s
                                     AND items.access_model in %s
-                                    ORDER BY node_id DESC, items.updated DESC""",
+                                    ORDER BY node_id DESC, items.updated DESC, items.item_id DESC""",
                                  (tuple([e.userhost() for e in entities]),
                                   nodes,
                                   node_accesses,
@@ -890,7 +890,7 @@
         """
         keys = ext_data.get('order_by')
         if not keys:
-            return 'ORDER BY updated ' + direction
+            return f"ORDER BY updated {direction}, item_id {direction}"
         cols_statmnt = []
         for key in keys:
             if key == 'creation':
@@ -900,8 +900,10 @@
             else:
                 log.msg("WARNING: Unknown order by key: {key}".format(key=key))
                 column = 'updated'
-            cols_statmnt.append(column + ' ' + direction)
+            cols_statmnt.append(f"{column} {direction}")
 
+        if len(cols_statmnt) == 1 and column != "item_id":
+            cols_statmnt.append(f"item_id {direction}")
         return "ORDER BY " + ",".join([col for col in cols_statmnt])
 
     @defer.inlineCallbacks
@@ -1087,7 +1089,7 @@
         _getItems
         """
         # SOURCES
-        query.append("FROM nodes INNER JOIN items USING (node_id)")
+        query.append("FROM items")
 
         if unrestricted:
             query_filters = ["WHERE node_id=%s"]
@@ -1155,10 +1157,6 @@
             query = ["SELECT data::text,items.access_model,item_id,created,updated"]
 
         query_order = self._appendSourcesAndFilters(query, args, authorized_groups, unrestricted, ext_data)
-        if query_order.startswith("ORDER BY updated"):
-            ref_field = "updated"
-        else:
-            ref_field = "item_id"
 
         if 'rsm' in ext_data:
             rsm = ext_data['rsm']
@@ -1180,9 +1178,11 @@
                 args.append(item_id)
             elif rsm.before is not None:
                 if rsm.before != '':
+                    query.insert(0,"SELECT * from (")
+                    query[1] += f",row_number() OVER ({query_order}) as row_number"
+                    query.append(f"{query_order}) as x")
                     query.append(
-                        f"AND {ref_field}<(SELECT {ref_field} FROM items WHERE "
-                        "node_id=%s AND item=%s LIMIT 1)"
+                        f"WHERE row_number<(SELECT row_number FROM (SELECT row_number() OVER ({query_order}) as row_number,item FROM items WHERE node_id=%s) as row_num_sub WHERE item=%s)"
                     )
                     args.extend((self.nodeDbId, rsm.before))
                 # we need to reverse order in a first query to get the right
@@ -1200,9 +1200,11 @@
                 else:
                     args.append(maxItems)
             elif rsm.after:
+                query.insert(0,"SELECT * from (")
+                query[1] += f",row_number() OVER ({query_order}) as row_number"
+                query.append(f"{query_order}) as x")
                 query.append(
-                    f"AND {ref_field}>(SELECT {ref_field} FROM items WHERE node_id=%s "
-                    "AND item=%s LIMIT 1)"
+                    f"WHERE row_number>(SELECT row_number FROM (SELECT row_number() OVER ({query_order}) as row_number,item FROM items WHERE node_id=%s) as row_num_sub WHERE item=%s)"
                 )
                 args.extend((self.nodeDbId, rsm.after))
         else: