CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemy
FranGoitia
FranGoitia

Reputation: 1993

SQLAlchemy - Get query results in same order as IN clause

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]

Upvotes: 3

Views: 2242

Answers (3)

Ilja Everilä
Ilja Everilä

Reputation: 52929

One SQL solution in PostgreSQL is to use WITH ORDINALITY and a JOIN:

With SQLAlchemy 1.4.0b2 and above

from sqlalchemy.dialects.postgresql import array
from sqlalchemy import select, func

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

teams_names = func.unnest(array(teams_names)).\
    table_valued('name', with_ordinality='ord')

stmt = select(Team).\
    join(teams_names, teams_names.c.name == Team.name).\
    order_by(teams_names.c.ord)

Session.scalars(stmt).all()

With older versions

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()

Upvotes: 1

Adán Escobar
Adán Escobar

Reputation: 4593

I'm using postgres and I emulate this with sqlalchemy:

postgres equivalent:

SELECT *
FROM teams
WHERE name IN ('Liverpool', 'Arsenal', 'Utd', 'Liverpool')
ORDER BY CASE name
    WHEN 'Liverpool' THEN 1
    WHEN 'Arsenal' THEN 2
    WHEN 'Utd' THEN 3
    WHEN 'Liverpool' THEN 4
    ELSE 5
END;

python script:

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

#Create a case statement to maintain the order
order = case(
    {name: index for index, name in enumerate(teams_names)},
    value=Team.name
)

list_teams = db.session\
     .query(Team)\
     .filter(Team.name.in_(teams_names))\
     .order_by(order)\
     .all()

Upvotes: 0

univerio
univerio

Reputation: 20518

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.

Upvotes: 6

Related Questions