Ok, two queries:
rsvp = session.query(Project.project,
func.count(Reservation.project_id)).join(Reservation.project).group_by(Project.project,
Project.id).order_by(Project.project).all()
h = session.query(Project.project,
func.count(Host.id)).join(Project.hosts).group_by(Project.project).all()
The first one counts reservations per project, the second one hosts per
project.
Is it possible to do this in one query? Joins are no help as they
produce outlandish numbers:
>>> h = session.query(Project.project, func.count(Host.id),
func.count(Reservation.project_id)).join(Project.hosts).join(Project.reservations).group_by(Project.project)
>>> print h
SELECT project.project AS project_project, count(hosts.id) AS count_1,
count(reservation.project_id) AS count_2
FROM project JOIN hosts ON project.id = hosts.project_id JOIN
reservation ON project.id = reservation.project_id GROUP BY project.project
>>> h.all()
[(u'DMS_OTIS', 54L, 54L), (u'CLOUDBURST', 8L, 8L), (u'CIS', 12L, 12L),
(u'TESTPROJ', 4L, 4L), (u'ICAD', 118L, 118L), (u'DI', 8250L, 8250L),
(u'ITPA', 2544L, 2544L), (u'LMT', 886030L, 886030L), (u'RXA', 160L, 160L)]
This is probably bc hosts.id is counted multiple times (per reservation)?
The obvious answer would be to use subquery for Project, but how to do it?
--
Regards,
mk
--
Premature optimization is the root of all fun.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
