Mercurial > libervia-pubsub

comparison sat_pubsub/pgsql_storage.py @ 450:96059231d58f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
pgsql: fix RSM before and after subqueries
author Goffi <goffi@goffi.org>
date Thu, 22 Apr 2021 18:27:03 +0200
parents 074037832daf
children 0e6e176cb572
comparison
equal deleted inserted replaced
449:91da42d44ea2 450:96059231d58f
1175 # now that we have the id, we can use it 1175 # now that we have the id, we can use it
1176 query.append("AND item_id<=%s") 1176 query.append("AND item_id<=%s")
1177 args.append(item_id) 1177 args.append(item_id)
1178 elif rsm.before is not None: 1178 elif rsm.before is not None:
1179 if rsm.before != '': 1179 if rsm.before != '':
1180 query.append("AND item_id>(SELECT item_id FROM items WHERE item=%s LIMIT 1)") 1180 query.append(
1181 args.append(rsm.before) 1181 "AND item_id>(SELECT item_id FROM items WHERE node_id=%s AND "
1182 "item=%s LIMIT 1)"
1183 )
1184 args.extend((self.nodeDbId, rsm.before))
1182 if maxItems is not None: 1185 if maxItems is not None:
1183 # if we have maxItems (i.e. a limit), we need to reverse order 1186 # if we have maxItems (i.e. a limit), we need to reverse order
1184 # in a first query to get the right items 1187 # in a first query to get the right items
1185 query.insert(0,"SELECT * from (") 1188 query.insert(0,"SELECT * from (")
1186 query.append(self.getOrderBy(ext_data, direction='ASC')) 1189 query.append(self.getOrderBy(ext_data, direction='ASC'))
1187 query.append("LIMIT %s) as x") 1190 query.append("LIMIT %s) as x")
1188 args.append(maxItems) 1191 args.append(maxItems)
1189 elif rsm.after: 1192 elif rsm.after:
1190 query.append("AND item_id<(SELECT item_id FROM items WHERE item=%s LIMIT 1)") 1193 query.append(
1191 args.append(rsm.after) 1194 "AND item_id<(SELECT item_id FROM items WHERE node_id=%s AND item=%s "
1195 "LIMIT 1)"
1196 )
1197 args.extend((self.nodeDbId, rsm.after))
1192 1198
1193 query.append(query_order) 1199 query.append(query_order)
1194 1200
1195 if maxItems is not None: 1201 if maxItems is not None:
1196 query.append("LIMIT %s") 1202 query.append("LIMIT %s")