How to order data in sqlalchemy according to the list

Question

I have a list of IDs from an external postgresql database.

A = [1,2,3,4,5,6,7,98,0] 

I would like to query the database using SQLAlchemy, but I would like to sort the data in postgresql by list.

I read a lot of documentation but cannot find any suggestions on how to do this.

So, in the finale, I would like to:

 results = session.query(user).limit(20).offset(10).order_by(A) 

Greetings

UPDATE:

I found a solution, it is not as good as I expected, but it works well. In any case, if you know the best solution, just let me know!

 ids = ','.join([str(i) for i in A]) results = session.query(user).filter(user.id.in_(A)).\ limit(20).offset(10).\ order_by(" position(CONCAT(',',users.id::text,',') in ',{0},'.format(ids)") 

Answer 1

If you don’t have to do this in SQL, you can simply sort the returned list of objects directly in python.

Example (using python sorted function)

 results = session.query(user).filter(user.id.in_(A)).all() results = sorted(results, key=lambda o: A.index(o.id)) 

Answer 2

Another way would be to create another auxiliary table that contains position items for each user.id, join it and order:

 A = [1,2,3,4,5,6,7,98,0] stmt = " UNION ALL ".join( 'SELECT {0} AS user_id, {1} AS order_id'.format(uid, row) for row, uid in enumerate(A)) ordq = text(stmt).columns(user_id=Integer, order_id=Integer).alias("t") results = session.query(user).join(ordq, user.id == ordq.c.user_id).order_by(ordq.c.order_id).all() 

I can’t judge how much better this is compared to your version, but it should be at least not specific to the RDBMS.

Answer 3

You can try:

 results = session.query(user).limit(20).offset(10).order_by(*A)