在SQL-alchemy中使用非SQL排名进行排序和分页
我有一个用Python写的算法,这个算法可以根据特定用户返回数据库中行的排名顺序。这个算法会输出一个主键ID的列表(这些ID可以和post.id关联)。输出的结果大概是这样的,虽然可能会有成千上万的匹配结果:
result_rank = [1286, 1956, 6759, 3485, 2309, 3409, 9023, 912, 13098, 23489, 19023, 1239]
我想让sqlalchemy来选择这些行,并且按照列表中的顺序排列它们。这里有个问题,我还想对这个结果进行分页处理。
results = posts.query().filter(posts.id.in_(
resultIds)).order_by(??? how can I order by post.id = result_rank ???).paginate(page_num, posts_per_page)
我使用的是Postgresql作为数据库。
2 个回答
1
我觉得排序更重要,因为如果没有排序,数据库的分页功能就完全没用了。虽然我提到这一点,但我的回答并没有涉及分页的内容,不过我认为@mgoldwasser 提供的答案也可以用来处理这个问题。
这是我想到的一个方法,可以选择一些对象,并保持它们按照最初筛选列表的顺序排列。代码的意思很清楚:
# input
post_ids = [3, 4, 1]
# create helper (temporary in-query table with two columns: post_id, sort_order)
# this table looks like this:
# key | sort_order
# 3 | 0
# 4 | 1
# 1 | 2
q_subq = "\nUNION ALL\n".join(
"SELECT {} AS key, {} AS sort_order".format(_id, i)
for i, _id in enumerate(post_ids)
)
# wrap it in a `Selectable` so that we can use JOINs
s = (select([literal_column("key", Integer),
literal_column("sort_order", Integer)])
.select_from(text("({}) AS helper".format(text(q_subq))))
).alias("helper")
# actual query which is both the filter and sorter
q = (session.query(Post)
.join(s, Post.id == s.c.key) # INNER JOIN will filter implicitly
.order_by(s.c.sort_order) # apply sort order
)
这个方法在 postgresql
和 sqlite
上都能用。
1
如果没有好的解决办法,我打算自己拼凑一个分页对象:
class paginate_obj:
""" Pagination dummy object. Takes a list and paginates it similar to sqlalchemy paginate() """
def __init__(self, paginatable, page, per_page):
self.has_next = (len(paginatable)/per_page) > page
self.has_prev = bool(page - 1)
self.next = page + self.has_next
self.prev = page - self.has_prev
self.items = paginatable[(page-1)*(per_page):(page)*(per_page)]
我觉得排序的唯一方法就是先把所有结果放到一个列表里,然后用Python根据某个lambda函数来排序:
results = my_table.query().all()
results.sort(key=lamba x: distance(x.lat, x.long, user_lat, user_long)
paginated_results = paginate_obj(results, 1, 10) #returns the first page of 10 elements