Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

Support for group_by and count aggregate on two or more different table cloumns. #110

Closed
zffl opened this Issue · 1 comment

2 participants

@zffl

I have this:

class A(Model):
    c1 = CharField()

class B(Model):
    c2 = ForeignKeyField(A)

class C(Model):
    c3 = ForeignKeyField(B)
    c4 = CharField()

I want to group by A.c1 and C.c4, and count C.id
and my query like this:

C.select({A:['c1'], C:['c4']}).filter(c3__c2__c1__in=[]).group_by('t1.c4, t3.c1')

Two problems here:
1.I don't know where to put the count aggregate? I try the select dict, count() function, but failed.
2.The group clause is weird. I can only use the alias. Any other solution?

@coleifer
Owner

Yeah...its weird, i'm not in love with the api...but here's how you do it:

C.select({A: ['c1'], C: ['c4', Count('id')]}).group_by('c4').join(B).join(A).group_by('c1')
  1. First we select the columns -- you had that part right except you were just missing the Count('id') in the list of "C" columns. from peewee import Count

  2. Then while the "query context" is "C", we'll group by c4

  3. Then join from c -> b, b -> a

  4. Now the query context is "A", we'll group by c1

This yields:

SELECT t1."c4", COUNT(t1."id") AS count, t3."c1"
FROM "c" AS t1 
INNER JOIN "b" AS t2 ON t1."c3_id" = t2."id"
INNER JOIN "a" AS t3 ON t2."c2_id" = t3."id" 
GROUP BY t1."c4", t3."c1"

I'm working on a rewrite of peewee in branch unstable/2.0 -- it will improve on some of these inconsistencies.

@coleifer coleifer closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.