How to query as GROUP BY in django

Question

I query a model   Members objects all     And it returns   Eric  Salesman  X-Shop Freddie  Manager  X2-Shop Teddy  Salesman  X2-Shop Sean  Manager  X2-Shop   What I want is to know the best Django way to fire a group by query to my database  like   Members objects all   group by  designation     Which doesn t work  of course  I know we can do some tricks on django db models query py  but I am just curious to know how to do it without patching

User · Answer

Django does not support free group by queries  I learned it in the very bad way  ORM is not designed to support stuff like what you want to do  without using custom SQL  You are limited to    RAW sql  i e  MyModel objects raw    cr execute sentences  and a hand-made parsing of the result    annotate    the group by sentences are performed in the child model for  annotate    in examples like aggregating lines count Count  lines        Over a queryset qs you can call qs query group by     field1    field2        but it is risky if you don t know what query are you editing and have no guarantee that it will work and not break internals of the QuerySet object  Besides  it is an internal  undocumented  API you should not access directly without risking the code not being anymore compatible with future Django versions

User · Answer

If you mean to do aggregation you can use the aggregation features of the ORM   from django db models import Count Members objects values  designation   annotate dcount Count  designation      This results in a query similar to  SELECT designation  COUNT designation  AS dcount FROM members GROUP BY designation   and the output would be of the form     designation    Salesman    dcount   2       designation    Manager    dcount   2

User · Answer

If I m not mistaking you can use  whatever-query-set group by   field

User · Answer

The document says that you can use values to group the queryset      class Travel models Model       interest   models ForeignKey Interest      user   models ForeignKey User      time   models DateTimeField auto now add True     Find the travel and group by the interest    gt  gt  gt  Travel objects values  interest   annotate Count  user     lt QuerySet    interest   5   user  count   2     interest   6   user  count   1   gt    the interest id 5  had been visited for 2 times     and the interest id 6  had only been visited for 1 time    gt  gt  gt  Travel objects values  interest   annotate Count  user   distinct True     lt QuerySet    interest   5   user  count   1     interest   6   user  count   1   gt    the interest id 5  had been visited by only one person  but this person had     visited the interest for 2 times   You can find all the books and group them by name using this code   Book objects values  name   annotate Count  id    order by     ensure you add the order by     You can watch some cheet sheet here

User · Answer

You can also use the regroup template tag to group by attributes  From the docs   cities           name    Mumbai    population    19 000 000    country    India          name    Calcutta    population    15 000 000    country    India          name    New York    population    20 000 000    country    USA          name    Chicago    population    7 000 000    country    USA          name    Tokyo    population    33 000 000    country    Japan               regroup cities by country as country list      lt ul gt         for country in country list             lt li gt    country grouper                 lt ul gt                 for city in country list                     lt li gt    city name        city population    lt  li gt                 endfor                 lt  ul gt           lt  li gt         endfor     lt  ul gt    Looks like this    India   Mumbai  19 000 000 Calcutta  15 000 000   USA   New York  20 000 000 Chicago  7 000 000  Japan   Tokyo  33 000 000    It also works on QuerySets I believe   source  https   docs djangoproject com en 2 1 ref templates builtins  regroup  edit  note the regroup tag does not work as you would expect it to if your list of dictionaries is not key-sorted  It works iteratively  So sort your list  or query set  by the key of the grouper before passing it to the regroup tag

User · Answer

from django db models import Sum Members objects annotate total Sum designation     first you need to import Sum then

User · Answer

An easy solution  but not the proper way is to use raw SQL   results   Members objects raw  SELECT   FROM myapp members GROUP BY designation     Another solution is to use the group by property   query   Members objects all   query query group by     designation   results   QuerySet query query  model Members    You can now iterate over the results variable to retrieve your results  Note that group by is not documented and may be changed in future version of Django   And    why do you want to use group by  If you don t use aggregation  you can use order by to achieve an alike result

User · Answer

You need to do custom SQL as exemplified in this snippet   Custom SQL via subquery  Or in a custom manager as shown in the online Django docs   Adding extra Manager methods

User · Answer

The following module allows you to group Django models and still work with a QuerySet in the result  https   github com kako-nawao django-group-by For example  from django group by import GroupByMixin  class BookQuerySet QuerySet  GroupByMixin       pass  class Book Model       title   TextField          author   ForeignKey User           shop   ForeignKey Shop           price   DecimalField        class GroupedBookListView PaginationMixin  ListView       template name    book books html      model   Book     paginate by   100      def get queryset self           return Book objects group by  title    author   annotate              shop count Count  shop    price avg Avg  price    order by               name    author   distinct        def get context data self    kwargs           return super   get context data total count self get queryset   count      kwargs    book books html   lt ul gt     for book in object list         lt li gt           lt h2 gt    book title    lt  td gt           lt p gt    book author last name        book author first name    lt  p gt           lt p gt    book shop count    lt  p gt           lt p gt    book price avg    lt  p gt       lt  li gt     endfor     lt  ul gt   The difference to the annotate aggregate basic Django queries is the use of the attributes of a related field  e g  book author last name  If you need the PKs of the instances that have been grouped together  add the following annotation   annotate pks ArrayAgg  id     NOTE  ArrayAgg is a Postgres specific function  available from Django 1 9 onwards  https   docs djangoproject com en 1 10 ref contrib postgres aggregates  arrayagg

[python] How to query as GROUP BY in django?

Examples related to python

Examples related to django

Examples related to django-models