SQLALchemy-Utils: Use '~' operator with LTree

Question

I am trying to query Postgres materialized path view(ltree) using Flask,SQLAlchemy-Utils and Flask-SQLAlchemy. SQLAlchemy-Util Docs shows the usage of '==', '!=' operators using LTree. How can i use '~' operator?

I saw the code in sqlalchemy_utils/ltree.py:

class comparator_factory(types.Concatenable.Comparator):
    def ancestor_of(self, other):
        if isinstance(other, list):
            return self.op('@>')(expression.cast(other, ARRAY(LtreeType)))
        else:
            return self.op('@>')(other)

    def descendant_of(self, other):
        if isinstance(other, list):
            return self.op('<@')(expression.cast(other, ARRAY(LtreeType)))
        else:
            return self.op('<@')(other)

    def lquery(self, other):
        if isinstance(other, list):
            return self.op('?')(expression.cast(other, ARRAY(LQUERY)))
        else:
            return self.op('~')(other)

    def ltxtquery(self, other):
        return self.op('@')(other)

This is subclass of LtreeType.

For a simple ==, i am using :

Model.query.filter(Model.path == LTree('1.2')).all()

But using this expression throws validation error:

Model.query.filter(Model.path == LTree('~1.2')).all()

How can i format above expression in a valid SQLALchemy query?

Answer 1

I was able to solve this issue with this piece of code.

Courtesy Github-Issues: SQLAlchemy-Util Issues (253)

 from sqlalchemy.sql import expression
 from sqlalchemy_utils.types.ltree import LQUERY

 custom_lquery = '*.some.pattern'
 Model.query.filter(Model.path.lquery(expression.cast(custom_lquery, LQUERY))).all()