CODEWITHSUNDEEP
pythonmysqlormpeewee
eignhpants
eignhpants

Reputation: 1771

Returning a count of grouped items in peewee ORM

I am trying to query a list on a unique field, and also the count of each unique field using peewee ORM. I can get what I want easily from MySQL workbench, however I can't seem to get a similar result out of peewee. The working MySQL query looks like this:

select Title, Severity, count(*) from qmodel group by Title;

I have tried a few variations in peewee but nothing is has worked. This is about as close as I have gotten:

from application.database.models import qmodel as q 

_field_select_list = [
    q.Title,
    q.Severity,
    fn.COUNT(q.Title),
]

for record in q.select(*_field_select_list).group_by(q.Title):
   print record

This returns the count, but replaces the title field on the return with the count, no title ( example {'Severity': '3', 'Title': '25'})

I also made my field select look like this:

_field_select_list = [
    q.Title,
    q.Severity,
    fn.COUNT(SQL('*')),
]

But that just gives me a grouped list, no count. I have tried many other combinations with no luck.

Upvotes: 0

Views: 4155

Answers (2)

coleifer
coleifer

Reputation: 26215

You'll need to do something like this:

query = (QModel
         .select(QModel.title, QModel.severity, fn.COUNT(QModel.id).alias('ct'))
         .group_by(QModel.title, QModel.severity))
for obj in query:
    print obj.title, obj.severity, obj.ct

Note, in most databases you need to group by every column you select that is a non-aggregate.

Upvotes: 3

wyattis
wyattis

Reputation: 1297

Have you tried just leaving the COUNT function empty? It works fine for me on my data.

query = q.select(q.Title, q.Severity, fn.COUNT()).group_by(q.Title)

Upvotes: 0

Related Questions