1

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?

lr_optim
  • 299
  • 1
  • 10
  • 30
  • 1
    Invert the filter. I do not do ORMs, but the logic would be to have SQLAlchemy NOT the result of searching for `'*.res_1.*'` – Mike Organek Jun 17 '22 at 10:36
  • @MikeOrganek That is what I try to do with the `!`.. – lr_optim Jun 17 '22 at 11:28
  • 1
    I understand that, but I could not get the negation to work inside of the lquery. It looks like that is not possible: https://www.postgresql.org/docs/current/ltree.html#:~:text=and%20you%20can%20put%20!%20(NOT)%20at%20the%20start%20of%20a%20non%2Dstar%20group%20to%20match%20any%20label%20that%20doesn%27t%20match%20any%20of%20the%20alternatives. It has to be followed by a `non-star group`. – Mike Organek Jun 17 '22 at 13:32
  • SQLAlchemy's shorthand for `not` is `~`, not `!`. See [conjunctions](https://docs.sqlalchemy.org/en/14/core/tutorial.html#conjunctions). – snakecharmerb Jun 20 '22 at 06:57

1 Answers1

2

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))))
lr_optim
  • 299
  • 1
  • 10
  • 30
  • 1
    Can also use the `~` operator which is doing exactly the same, but a bit less verbose: `stmnt.filter(~MyModel.path.lquery(expression.cast(custom_lquery, LQUERY)))` – van Jun 22 '22 at 01:54