I'd like to order_by Levenshtein distance on a (small) set of rows returned by an endpoint used for searching. My setup:
- Postgres Engine 11.3
My initial approach with
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 = ( db.query(Model) .options(joinedload(Model.child2s), joinedload(Model.children)) .filter(Model.children.has(id=12345)) .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?