Thanks.. make more sense now ( I suppose it generally does once
someone else has solved it for you ).
Hmm... it almost looks like a little tool for beginners could be
created where you enter a sql statement and it generates the
corresponding SQLAlchemy call.
Thanks again.
On Sep 26, 10:50 am, Michael Bayer <[EMAIL PROTECTED]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
