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
-~----------~----~----~----~------~----~------~--~---
