Am 04.06.2013, 04:57 Uhr, schrieb Rob Green robsgr...@gmail.com:
Hello,
I'm having some trouble getting my SQL query to work in SQLAlchemy.
In this example, I want to find out the number of meeting rooms and
bathrooms available to a user. The user is part of a userGroup, which is
assigned to one building.
The building has many meeting rooms and bathrooms. My sql query looks
like:
select user.id,
(select count(*) from meetingRoom where userGroup.buildingCode =
meetingRoom.buildingCode),
(select count(*) from restroom where userGroup.buildingCode =
restroom.buildingCode)
from user
join userGroup
on user.userGroupId = userGroup.userGroupId
I've tried using subqueries:
meetingRoomCount
= session.query(func.count(MeetingRoom.id)).join(UserGroup,
MeeingRoom.buildingId == UserGroup.buildingId).subquery()
bathroomCount = session.query(func.count(Bathroom.id)).join(UserGroup,
Bathroom.buildingId == UserGroup.buildingId).subquery()
session.query(User.id, meetingRoomCount, bathroomCount).first()
But this returns the total number of meeting rooms and bathrooms in the
database, not the ones that are specific to that user.
I feel like I'm missing something simple here, anyone have any ideas?
I think you might have more success if you explicitly alias your counts.
SELECT user.id, mr.total, rr.total
FROM
user,
(select count(*) AS total from meetingRoom where userGroup.buildingCode =
meetingRoom.buildingCode) AS mr,
(select count(*) AS total from restroom where userGroup.buildingCode =
restroom.buildingCode) AS rr
from user
join userGroup
on user.userGroupId = userGroup.userGroupId
Charlie
--
Charlie Clark
Managing Director
Clark Consulting Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
