Hi,
My problem is with using a many-to-many relationship inside a query.
I am using SQLAlchemy 0.7.2 with MySQL 5.1, if that matters.
In my POS app there are "Customer"s which can be grouped in
"CustomerGroup"s. These are stored in 2 tables with a many-to-many
relationship.
I have a declarative Customer class defined as follows:
class Customer(Base):
__tablename__ = 'customers'
id = Column(Integer, primary_key=True)
name = Column(String(255), nullable=False)
...
groups = relationship("CustomerGroup",
secondary=customer_group_link, backref="customers")
and a CustomerGroup declarative class, and the relationship table
defined like that:
customer_group_link = Table('customer_group', metadata,
Column('customer_id', Integer, ForeignKey('customers.id')),
Column('group_id', Integer, ForeignKey('customergroups.id'))
)
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.
I used to do it like that:
all_customers = session.query(Customer).all()
customers = [c for c in all_customers if len(c.groups) == 0]
Which also works.
I know that there is a way to do it in a cleaner way, "a la
SQLAlchemy". I just don't know how.
Please note I'm new to SQLAlchemy and I don't know if the answer to
this is obvious, but I can't seem to find it in any other way.
Thanks in advance.
--
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.
