On Dec 17, 2011, at 6:56 PM, Jad Kik wrote: > Hi, > My problem is with using a many-to-many relationship inside a query. > > > I wanted to get all the Customers who are associated to a > CustomerGroup, I did: > session.query(Customer).filter(Customer.groups.contains(group)) > > I wanted to get all the Customers who do not belong to any > CustomerGroup. I tried that: > session.query(Customer).filter(len(Customer.groups) == 0) > which threw an exception "TypeError: object of type > 'InstrumentedAttribute' has no len()". > I understand what that means, but it would be a good thing to have it > available, something similar to Customer.groups.contains(). > > I can't seem to do such a thing neither: > session.query(Customer).filter([some subquery].count() == 0) > Then what would [some subquery] be? > > I did manage to do it with normal SQL (on MySQL 5.1): > SELECT c2.id FROM customers c2 WHERE (SELECT COUNT(cg.id) FROM > customers c, customergroups cg, customer_group ccg WHERE > ccg.customer_id=c.id AND ccg.group_id=cg.id AND c.id=c2.id)=0 > It worked as I expected.
The usual form of this query is relational terms (the set of all customers) <intersection> (the set of all customer ids in customer_group with N number of group id). The second half of it uses GROUP BY to group by the customer id and HAVING to limit the rows, and you'd use join() to join the two together as in the form at http://www.sqlalchemy.org/docs/orm/tutorial.html#using-subqueries . But here you're looking for the count being zero. So a subquery against customer_group won't have any rows you'd care about. To query for an exclusion, use WHERE NOT EXISTS. In the ORM this is the expression ~Customer.groups.any(), just like the third example at http://www.sqlalchemy.org/docs/orm/internals.html#sqlalchemy.orm.properties.RelationshipProperty.Comparator.any . The join across the association table is handled here within the subquery. The correlated subquery approach you have is more amenable to being "packaged up" as a simple WHERE criterion on the Python side, but you'll note the format of the query is less well suited to the typical optimizer. MySQL's optimizer in particular is awful, so this is an example where len(SomeRelationship) might look tidy on the Python side but is actually not taking the reality of the relational database into account to an appropriate degree. You could achieve that exact form by using the style at http://www.sqlalchemy.org/docs/orm/tutorial.html#using-subqueries in conjunction with aliased(Customer) to produce "c2". I don't know if any of these ways is really "cleaner", except for the ~any() that is only appropriate for the "no groups" comparison. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
