Hello all,
I'm writing a SA-based library to work with an existing MSSQL database
project. There is one query in particular I haven't been able to figure out
how to express in SA: I have a many to many relationship between schools
and coordinators and I want to find the schools that have more than one
coordinator.
>From my models.py:
class School(Base):
__tablename__ = 'School'
id = Column('Id', Integer, primary_key=True)
school_name = Column('SchoolName', String)
school_coordinators = Table('SchoolCoordinator', Base.metadata,
Column('SchoolId', Integer, ForeignKey('School.Id')),
Column('CoordinatorId', Integer, ForeignKey('Coordinator.Id'))
)
class Coordinator(Base):
__tablename__ = 'Coordinator'
id = Column('Id', Integer, primary_key=True)
email_address = ('EmailAddress', String)
first_name = Column('FirstName', String)
last_name = Column('LastName', String)
schools = relationship('School',
secondary=school_coordinators, backref='coordinators')
And I want to achieve SQL that basically looks like this (IE, show me the
schools with more than one coordinator):
SELECT s.Id, COUNT(c.Id)
FROM School s
INNER JOIN SchoolCoordinator sc ON s.Id = sc.SchoolId
INNER JOIN Coordinator c ON sc.CoordinatorId = c.Id
GROUP BY s.Id
HAVING COUNT(c.Id) > 1
The HAVING example in the query object documentation is for a one-to-many
relationship and seems pretty straightforward. None of the incantations
I've tried have worked for a m2m relationship however. Any advice is much
appreciated.
Thanks,
Matthew
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
