Ordering and pagination in SQL-alchemy using non-sql ranking
Unless there is a good solution, I'm going to hack together my own paginate object:
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)]
I think the only way to do ordering is to create a list of all results and sort it in python according to some lambda function:
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
I think that the ordering is more important, because without it the database level pagination is completely useless. Having noted it, my answer does not cover pagination aspect at all, but I assume that even the answer provided by @mgoldwasser can be used for this.
This is what I came up with in order to be able to select some objects and preserve the order of them as per initial filter list. The code is self explanatory:
# inputpost_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 | 2q_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 JOINss = (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 sorterq = (session.query(Post) .join(s, Post.id == s.c.key) # INNER JOIN will filter implicitly .order_by(s.c.sort_order) # apply sort order )
It works on both postgresql
and sqlite
.