order_by Levenshtein Distance in SqlAlchemy

I'd like to order_by Levenshtein distance on a (small) set of rows returned by an endpoint used for searching. My setup:

  • sqlalchemy=='1.3.19'
  • Postgres Engine 11.3

My initial approach with Model:

class Model(Base):
    id = Column(...)
    name = Column(...)

class Child(Column):
    id = Column(...)
    model_id = Column(...)
    model = relationship("Model", backref='children', ...)

class Child2(Column):
    id = Column(...)
    model_id = Column(...)
    model = relationship("Model", backref='child2s', ...)

db: Session = get_session()
q = (
    .options(joinedload(Model.child2s), joinedload(Model.children))
    .order_by(text("LEVENSHTEIN(model.name, 'SomeString')")) <<< this is what caused an error

res = q.all()

and here is the error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable) missing FROM-clause entry for table "model"

This failed because the joinedload options introduced an alias to my model such that model.name is now named anon_1_model_name. Because the order_by is one of a list of join/filter operations that I'm applying to the query based on incoming requests' parameters, the alias of the model table will not be known at runtime. Is there a good way of approaching sorting using the Postgres Levenshtein function?

Read more here: https://stackoverflow.com/questions/64949921/order-by-levenshtein-distance-in-sqlalchemy

Content Attribution

This content was originally published by Nikhil Shinday at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: