Mercurial > libervia-pubsub
diff src/pgsql_storage.py @ 375:9a787881b824
implemented Order-By ProtoXEP (MAM + PubSub)
author | Goffi <goffi@goffi.org> |
---|---|
date | Sun, 06 Jan 2019 17:29:50 +0100 |
parents | 40e5edd7ea11 |
children | 22832c1d2827 |
line wrap: on
line diff
--- a/src/pgsql_storage.py Thu Jan 03 20:31:03 2019 +0100 +++ b/src/pgsql_storage.py Sun Jan 06 17:29:50 2019 +0100 @@ -834,6 +834,29 @@ nodeType = 'leaf' + def getOrderBy(self, ext_data, direction='DESC'): + """Return ORDER BY clause corresponding to Order By key in ext_data + + @param ext_data (dict): extra data as used in getItems + @param direction (unicode): ORDER BY direction (ASC or DESC) + @return (unicode): ORDER BY clause to use + """ + keys = ext_data.get('order_by') + if not keys: + return u'ORDER BY items.updated ' + direction + cols_statmnt = [] + for key in keys: + if key == 'creation': + column = 'items.item_id' # could work with items.created too + elif key == 'modification': + column = 'items.updated' + else: + log.msg(u"WARNING: Unknown order by key: {key}".format(key=key)) + column = 'items.updated' + cols_statmnt.append(column + u' ' + direction) + + return u"ORDER BY " + u",".join([col for col in cols_statmnt]) + def storeItems(self, item_data, publisher): return self.dbpool.runInteraction(self._storeItems, item_data, publisher) @@ -1031,7 +1054,7 @@ query.extend(query_filters) - return "ORDER BY items.updated DESC" + return self.getOrderBy(ext_data) def _getItems(self, cursor, authorized_groups, unrestricted, maxItems, ext_data, ids_only): self._checkNodeExists(cursor) @@ -1075,7 +1098,8 @@ # if we have maxItems (i.e. a limit), we need to reverse order # in a first query to get the right items query.insert(0,"SELECT * from (") - query.append("ORDER BY updated ASC LIMIT %s) as x") + query.append(self.getOrderBy(ext_data, direction='ASC')) + query.append("LIMIT %s) as x") args.append(maxItems) elif rsm.after: query.append("AND item_id<(SELECT item_id FROM items WHERE item=%s LIMIT 1)")