SQLAlchemy - Get query results in same order as IN clause

Question

I have a list of teams names. For every team name in the list I want to get the row from the database but I want to make only one call to the database and the list of sql alchemy objects need to preserve the order of the original list. I will have duplicated names in the original list.

I need something like this, but working(the query I'm doing obviously doesn't work since it returns everything ordered by id)

teams_names = ['Liverpool', 'Arsenal', 'Utd', 'Liverpool']
Session.query(Team).filter(Team.name.in_(teams_names)).all()
teams_sa_obj = [#sa_liverpool, #sa_arsenal, #sa_utd, #sa_liverpool]

I'm with postgres. I'd be ok with postgres specific. – FranGoitia Feb 10 '16 at 14:56 — FranGoitia, Feb 10 '16 at 14:56

score 6 · Accepted Answer · answered Feb 10 '16 at 17:59

I usually do the reordering in Python. The idea is that you build a map of name to Team objects, then you look them up while iterating over the original list:

q = Session.query(Team).filter(Team.name.in_(teams_names))
team_map = {t.name: t for t in q}
teams = [team_map[n] for n in teams_names]

There are SQL-side solutions, but those are kind of awkward to do with SQLAlchemy.

score 0 · Answer 2 · answered Jul 04 '20 at 07:13

One of the awkward SQL solutions in PostgreSQL is to use WITH ORDINALITY and a JOIN:

teams_names = ['Liverpool', 'Arsenal', 'Utd', 'Liverpool']

teams_names = text("""SELECT * 
                      FROM unnest(:teams_names) WITH ORDINALITY dummy(name, ord)""").\
    bindparams(teams_names=teams_names).\
    columns(name=String, ord=Integer).\
    alias()

Session.query(Team).\
    join(teams_names, teams_names.c.name == Team.name).\
    order_by(teams_names.c.ord).\
    all()

Unfortunately SQLAlchemy does not yet support WITH ORDINALITY, so in order to avoid having to produce a custom compiler extension the clunky text() construct is required, but on the other hand a custom construct would improve the readability of the Python code greatly.

SQLAlchemy - Get query results in same order as IN clause

2 Answers2