Thank you! Awesome reply and it really helped me make sense of what
was happening and why I was running in to the limitations I was. For
now, I'll just stick using select and working with the ResultProxy as
it works fine.
query = sa.select([model.Type.name, sa.func.count('*')],
from_obj[model.type_tbl]).group_by(model.Type.name)
On Feb 25, 12:52 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Feb 25, 2008, at 12:14 PM, Wayne Witzel wrote:
>
>
>
>
>
> > I am having trouble what is best practice when using SQLalchemy to
> > issue a select with a count(*) and a group by.
>
> > Example SQL: select type, count(*) as type_count from type_tbl group
> > by type
>
> > This gives me the following result set:
>
> > +-------+-----------------+
> > | type | type_count |
> > +-------+-----------------+
> > | test1 | 5 |
> > | test2 | 3 |
> > +-------+-----------------+
>
> > Does i need to do some setup in my mapper to facilitate this cleanly?
> > Or is this something that can be done right from the .query() object?
> > Been trying different things I've found searching on here, like
> > incorporating .distinct() with .count() but can't seem to get the
> > result I am looking for.
>
> > Not, right now, I am just using a select to get an array of the value,
> > but I assume there must be a way to do this at a high level in the
> > orm?
>
> query.count() is currently not taking things like group_by and such
> into account and is hardwired to return exactly one scalar result.
> We have some tickets open where we're going to decide what to do with
> this method so that it makes sense with group_by(), which would be to
> change its old assumption that it returns a scalar result. But we
> havent decided upon that yet.
>
> You can add aggregates like count() to a query using add_column().
> However, it generally only makes sense when you are counting the rows
> in a related table or subselect, since query() always returns object
> instances at the very least, with optional additional columns added.
> This is one reason why the group_by() method itself on Query is not in
> such great shape; it was added in response to user requests some time
> ago, but its not really clear at the moment what real use cases exist
> for it.
>
> As an example, if you're selecting an aggregate function on some or
> all of the columns, each row returned is not going to contain a fully
> composed primary key, unless you were grouping by all of those primary
> key columns, in which case the aggregate functions are useless since
> they are applied to distinct rows. Since Query is always going to put
> the primary key columns in the SELECT statement, theres not much
> option here to change that. Without a fully composed primary key, we
> can't generate an object instance in any case.
>
> A way that all of this would make sense would be if we added to Query
> the capability to select only individual columns and not any object
> instances, such as query.columns(MyClass.type, func.count('*')).
> However, this is already doable using normal select() constructs,
> which return higher-performing ResultProxy objects (Query generally
> returns lists of object instances or tuples), so we are hesitant to
> add a redundant way of doing the same thing. This is still something
> we're thinking about, though, as Query becomes way more high level in
> its operation than we ever originally anticipated (i.e. so that higher
> level methods like join() etc. can be used).
>
> The equivalent to query.columns(MyClass.type, func.count('*')) is
> select([MyClass.type, func.count('*')]) . So it currently makes more
> sense to make usage of select() constructs when looking for individual
> columns.
>
> This has been a hot issue these days so I might consider adding
> query.columns(...), which returns an already bound select() construct
> containining the query's where/group by/having/order by criterion as
> well as the the given columns, and binds it to the current engine/
> connection. This would look like:
>
> result =
> sess.query(MyClass).group_by(MyClass.type).columns(MyClass.type,
> func.count('*')).execute().fetchall() .
>
> but the bumpiness there is the transition to ResultProxy, and the
> execute() step. more flexible but more ugly.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
