How to filter out with lquery?

Question

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?

Answer 1

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