Hi, I have two classes, Item and ItemId where one Item can have multiple ItemIds accessible from its ref_ids relation.

I can do:

Item.query().filter(not_(Item.ref_ids.any(ref_id = "OP-10-47000")))

if I want all the items except the ones with an ItemId with ref_id set to OP-10-47000 and I can do

Item.query().filter(not_(Item.ref_ids.any(ref_id = "OP-10-47000")))\ .join("ref_ids", aliased=True).filter_by(ref_id="OP-10")

and I will get all the Items with an ItemId of "OP-10" except the ones with "OP-10-47000". This is great.

However, if I flip the order and I do:

Item.query().join("ref_ids", aliased=True).filter_by(ref_id="OP-10")\ .filter(not_(Item.ref_ids.any(ref_id = "OP-10-47000")))

I get the following error:

<class 'sqlalchemy.exceptions.InvalidRequestError'>: Select statement 'SELECT 1 FROM items, item_ids AS item_ids_1 WHERE items.id = item_ids_1.item_id AND item_ids_1.ref_id = :item_ids_ref_id_1' is overcorrelated; returned no 'from' clauses

I had the same error with the first query before I aliased it so I assume that it's an aliasing problem. How can I alias the ref_ids.any() clause?