On Apr 30, 2008, at 3:51 PM, Yannick Gingras wrote:
>
>
> 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?
OK the item_ids inside the any() is getting aliased per the
join("refids", aliased=True) so that both "items" and "item_ids" is
correlating to the parent. The fix for this would be to call
reset_joinpoint() after the filter_by() to cancel out future filter
criterion from sticking to the "refids" join.
Although, I think it may be wise here if SQLA set the "correlate"
value on the expression returned by any() to prevent these errors from
occuring at all. Below is a patch that does it. It needs a little
bit of tweaking to work with inheritance though so i might add a
ticket for this.
Index: lib/sqlalchemy/orm/properties.py
===================================================================
--- lib/sqlalchemy/orm/properties.py (revision 4592)
+++ lib/sqlalchemy/orm/properties.py (working copy)
@@ -332,21 +332,21 @@
if criterion and target_adapter:
criterion = target_adapter.traverse(criterion)
- return j, criterion, dest
+ return j, criterion, source, dest
def any(self, criterion=None, **kwargs):
if not self.prop.uselist:
raise exceptions.InvalidRequestError("'any()' not
implemented for scalar attributes. Use has().")
- j, criterion, from_obj =
self._join_and_criterion(criterion, **kwargs)
+ j, criterion, source, from_obj =
self._join_and_criterion(criterion, **kwargs)
- return sql.exists([1], j & criterion, from_obj=from_obj)
+ return sql.exists([1], j & criterion,
from_obj=from_obj).correlate(source)
def has(self, criterion=None, **kwargs):
if self.prop.uselist:
raise exceptions.InvalidRequestError("'has()' not
implemented for collections. Use any().")
- j, criterion, from_obj =
self._join_and_criterion(criterion, **kwargs)
+ j, criterion, source, from_obj =
self._join_and_criterion(criterion, **kwargs)
- return sql.exists([1], j & criterion, from_obj=from_obj)
+ return sql.exists([1], j & criterion,
from_obj=from_obj).correlate(source)
def contains(self, other):
if not self.prop.uselist:
@@ -360,8 +360,8 @@
def _negated_contains_or_equals(self, other):
criterion = sql.and_(*[x==y for (x, y) in
zip(self.prop.mapper.primary_key,
self.prop.mapper.primary_key_from_instance(other))])
- j, criterion, from_obj =
self._join_and_criterion(criterion)
- return ~sql.exists([1], j & criterion, from_obj=from_obj)
+ j, criterion, source, from_obj =
self._join_and_criterion(criterion)
+ return ~sql.exists([1], j & criterion,
from_obj=from_obj).correlate(source)
def __ne__(self, other):
if other is None:
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
