5

I have list of ID from external postgresql database.

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

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

I have read a lot of documentation but cannot find any suggestions how to do that.

So, in final I would to have:

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

Cheers

UPDATE:

I found solution, it's not so good as I expected, but works well. Anyway if you know better 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)")
Estatic
  • 138
  • 1
  • 8
  • BTW. I know that postgresql supports "join values" http://stackoverflow.com/questions/866465/sql-order-by-the-in-value-list, but cannot figure out how to implement this with sqlalchemy – Estatic Apr 30 '14 at 07:17
  • I am curious about the reason for such ordering, would you mind elaborating more? – van May 03 '14 at 14:52

4 Answers4

7

If you do not necessarily need to do this in SQL, you could simply sort the returned list of objects directly in python.

Example (using python's sorted function)

results = session.query(user).filter(user.id.in_(A)).all()
results = sorted(results, key=lambda o: A.index(o.id))
flinz
  • 347
  • 4
  • 12
2

Here's the example of sorting by using SQLAlchemy case expression.

from sqlalchemy import case

from core.orm import session
from core.orm import models

query = session.query(models.Model)

ids_list = [1, 2, 4, 3, 5]

# order query by index of id in `id_list`

id_ordering = case(
    {_id: index for index, _id in enumerate(ids_list)},
    value=models.Model.id
)

# order 
query = query.order_by(id_ordering)
pyropy
  • 233
  • 2
  • 10
  • Internally, that syntax compose this SQL sentence `ORDER BY CASE Table.column WHEN index THEN _id`. Was helpful for me. – Franco Gil Feb 15 '22 at 11:47
1

You can try:

results = session.query(user).limit(20).offset(10).order_by(*A)
Frits
  • 7,341
  • 10
  • 42
  • 60
Yadi Yang
  • 59
  • 4
0

Another way would be to create another helper table which contains order positions for each user.id, join on 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 cannot judge whether this is better compared to your version, but it should at least be non-RDBMS specific.

van
  • 74,297
  • 13
  • 168
  • 171
  • Well i did think about this way, but probably it should be very slow, as you have to do one more operation in database and one more join instead of just one query. – Estatic May 03 '14 at 13:46
  • no need for an operation in the database as you do this in-memory (as code shows). Would it be slow or not - very much depends on the length of this select statement. – van May 03 '14 at 14:51