On Sep 26, 2008, at 12:40 PM, Steven wrote:
>
> 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.
well, you're in luck since SQLAlchemy is oriented towards those who
think in terms of SQL.
> ===============================================
> 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
To get this query directly:
sess.query(func.count('*').label('cnt'),
User).filter(User.id==BlogPost.authorid).group_by(User.name,
User.id).order_by(User.name.asc())
the FROM clause is generated from what it sees in filter() and
order_by(), so user and blogpost will be there.
> 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)
so what's happening here is that the docs are pushing more of a
"proper" pattern of relating a table to an aggregate on a related
table, which I believe also performs better. I can't find the link I
usually reference here, but the idea is that instead of grouping on
all the columns of the parent table, you push only exactly what needs
to be grouped into a subquery and then join to that. The previous
method is still entirely fine (although not according to this article
i cant find).
> 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.
well you'd just change "outerjoin" to "join" so that you'd only get
User objects which join to a BlogPost.
> ===============================================
> 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
So again you can do this more or less directly, where "tagblogpost" is
a Table object, not a mapped association object:
sess.query(func.count("*").label('cnt'),
Tag).filter(Tag.id==tagblogpost.c.tagid).group_by(Tag.name,
Tag.id).order_by(Tag.name.asc())
and the "subquery" approach is again the same idea (here illustrated
using an implicit inner join):
subq = sess.query(tagblogpost.c.tagid,
func.count('*').label('cnt')).group_by(tagblogpost.c.tagid).subquery()
sess.query(Tag,
subq.c.cnt).filter(Tag.id==subq.c.tagid).order_by(Tag.name)
I think you get the idea.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
