I'm using LQUERY to do some custom filtering in queries from my PostgreSQL -database. One of the fields is a ltree -field called path and I should be able to check which objects have a certain string in their path and filter them out.
I've tried this:
from sqlalchemy_utils.types.ltree import LQUERY
from sqlalchemy.sql import expression
custom_lquery = '!res_1.*'
stmnt = stmnt.filter(MyModel.path.lquery(expression.cast(custom_lquery, LQUERY)))
This filters out correctly objects which path starts with res_1. What I want to do, is to filter out all objects that have the res_1 in any point of their path, e.g. res_0.res_1.res_2. How could I achieve this?
This was achieved with the SQLAlchemy not_.
from sqlalchemy import not_
custom_lquery = '*.res_1.*'
stmnt = stmnt.filter(not_(MyModel.path.lquery(expression.cast(custom_lquery, LQUERY))))