On Feb 25, 2008, at 7:45 AM, Gaetan de Menten wrote:
>
> Hello all,
>
> I'm puzzled as I cannot seem to do a pretty simple query: I have a
> many to many relationship (say from Vendor to Item) and want to get a
> list of all vendors which have no item.
>
> The obvious:
> Vendor.query.filter(Vendor.items == []).all()
> does not work (it produce a query without any where clause).
I dont think we support comparing to the empty list just yet but thats
not hard to add. Try instead saying:
filter(~Vendor.items.any())
> Also, Vendor.items.count doesn't exist. It would be nice if we could
> express things like:
> Vendor.query.filter(Vendor.items.count() == 0).all()
>
>
> Maybe I'm just not awake yet, but can someone enlighten me how to do
> that?
well for the count == 0 youd be best off doing the any()
again....otherwise you can embed a subquery
itemcount =
select
([func
.count
(items
.c
.id
)]).select_from
(vendors
.join
(itemvendors
)).where(itemvendors.c.vendor_id==Vendor.id).label('itemcount')
Vendor.query.filter(itemcount==0)
we could likely add attribute.count() here though without too much
difficulty.
> On a related note, I've seen the following block in the documentation
> (in the Relation "Operators" section):
>
> # locate an address
> sql>>> address = session.query(Address).\
> ... filter(Address.email_address=='[EMAIL PROTECTED]').one()
>
> ['[EMAIL PROTECTED]']
>
> # use the address in a filter_by expression
> sql>>> session.query(User).filter_by(addresses=address).all()
>
> Is it a simple mistake in the docs or is it really valid? In the later
> case, shouldn't [collection attribute == single instance] be an
> invalid case since the new "contains", "has" and "any" operators
> appeared?
its currently a valid case. the idea of has()/any()/contains(), which
allow us much more specific usage, was introduced much later than the
filter_by(attr=<someobject>) idea. We could take it out, but I'd be
more eager to remove things like "apply_max()" / "apply_min()"
first ;) . (no seriously, we can deprecate this).
> Or is filter_by(x=y) not always equal to CurrentJoinPoint.x
> == y ?
thats the funny thing about has() and any() which is that they
generate the SQL in exactly the same way. that they both raise
exceptions for collection/non-collection mismatch is an artificial
assertion we've added.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
