I'm new to SQLAlchemy and still getting used to it. I'm trying to
learn how to use the higer level apis.. the mapped objects. I still
think in SQL and then have to work "backwards" to figure out how to
best get SQLAlchemy to do something similar.
===============================================
Version1: "one-to-many" group by with count query (2 tables involved:
user, blogpost). Desired result is list of users with # of blogposts
for each.
=In SQL:
select count(*) as cnt, user.name, user.id as name from user, blogpost
where user.id = blogpost.authorid group by user.name, user.id order by
user.name asc
=Seems like in SQLAlchemy it is something like this? (I copied this
from documentation for 0.5rc1)
stmt = session.query(blogpost.authorid,
func.count('*').label('post_count')).group_by(blogpost.user_id).subquery()
session.query(User, stmt.c.post_count).outerjoin((stmt,
User.id==stmt.c.user_id)).order_by(User.id)
Although these are doing something slightly different... the
SQLAlchemy version is doing outer join and returning users with no
blog posts... don't really need it to be doing that.
What are some ways of achieving this in SQLAlchemy?
===============================================
Version2: "many-to-many" (3 tables involved: tag, tagblogpost and
blogpost). Desired result: list of tags with number of blog posts
with that tag
=In SQL we only really have to look at 2 or the 3 tables: tag and
tagblogpost:
select count(*) as cnt, tag.name, tag.id as name from tag, tagblogpost
where tag.id = tagblogpost.tagid group by tag.name, tag.id order by
tag.name asc
=In SQLAlchemy.. not sure yet what the query looks like since a pure
"many-to-many" does not have a mapped class to represent the
tagblogpost table. Should I add an explicit "Association Object"
pattern... or maybe somehow involve all 3 tables? seems like neither
should be necessary.
Steven
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
