Querying for empty / non-empty collection (in many-to-many relation)

[Marcin Krol]

Hello,


rsvs = session.query(Reservation).filter(Reservation.email ==
em).filter(Reservation.newhosts == [] ).options(eagerload('newhosts')).all()

sqlalchemy.exc.InvalidRequestError: Can't compare a collection to an
object or collection; use contains() to test for membership.

Well I can always do:

rsvs = session.query(Reservation).filter(Reservation.email ==
em).options(eagerload('newhosts')).all()


for r in rsvs:
if r.newhosts != []:
print r.id, 'newhosts', r.newhosts

or

for r in rsvs:
if r.newhosts == []:
print r.id, 'newhosts', r.newhosts


But, that's ugly like. So, the question is, is it possible to query for
empty or non-empty collection?

Regards,
mk

[Michael Bayer]

use a blank any() for that. filter(Reservation.newhosts.any()) /
filter(~Reservation.newhosts.any())



>
> Regards,
> mk
>
> --
>
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>

[Conor]

If you really do need to peek inside non-empty collections like the
example above, then I think your eagerloading query works best.

Otherwise, you can use this (which generates a NOT EXISTS subquery) to
find reservations without any newhosts:
q = session.query(Reservation)
q = q.filter(Reservation.email == em)
q = q.filter(~Reservation.newhosts.any())
rsvs = q.all()

or this (explicit anti-join, assumes that Reservation.newhosts is a
relation to a Host class):
q = session.query(Reservation)
q = q.outerjoin(Reservation.newhosts)
q = q.filter(Reservation.email == em)
q = q.filter(Host.id == None)
rsvs = q.all()

or, if you need to include both empty and non-empty newhosts, with a
flag indicating which:
q = session.query(Reservation, Reservation.newhosts.any())
q = q.filter(Reservation.email == em)
rsvs_with_flag = q.all()

-Conor