Keep the order of list in sql pagination

Question

I have a list with an order of insertion. I want to paginate the results using the same order. As you can see currently the output will be a different order.

following_companies_list_data = Company.query.filter(Company.id.in_(['2', '24', '1', '7', '373'])).paginate(
            page, per_page=10, error_out=False)

companies = following_companies_list_data.items

for i in companies:
    print i.id

7
24
373
2
1

related question

**Never** paginate without an `ORDER BY`. Databases are allowed to return rows in *any* order without it, and that means different page queries could come up with different orders (resulting in missed or duplicated results). — jpmc26, Feb 12 '16 at 01:28
The usual way to order by a client-side list is to build a `case when 2 then 1 when 24 then 2 ... end` expression and ORDER BY that. Not sure how you'd do something like that with your tools though. — mu is too short, Feb 12 '16 at 02:35

score 8 · Accepted Answer · edited May 23 '17 at 12:15

Solution based on this answer from related question

company_ids = ['2', '24', '1', '7', '373']
order_expressions = [(Company.id==i).desc() for i in company_ids]
query = Company.query.filter(Company.id.in_(company_ids)).order_by(*order_expressions)
following_companies_list_data = query.paginate(page, per_page=10, error_out=False)

Also you can use idx function from intarray module

from sqlalchemy import func
company_ids = ['2', '24', '1', '7', '373']
query = Company.query.filter(Company.id.in_(company_ids)).order_by(func.idx(company_ids, Company.id))
following_companies_list_data = query.paginate(page, per_page=10, error_out=False)

score 1 · Answer 2 · answered Feb 12 '16 at 01:19

I think the easiest way to do this is to paginate your list of IDs before even querying:

company_ids_page = company_ids[page * 10:(page + 1) * 10]
q = Company.query.filter(Company.id.in_(company_ids_page))

Then, reorder companies based on the order of your ids:

companies_map = {c.id: c for c in q}
companies = [companies_map[i] for i in company_ids_page]

If you need the fancy features of Pagination objects you can probably create a similar class for lists.

Keep the order of list in sql pagination

2 Answers2