GROUP BY

[Mikkel Kromann]

I'm trying to sum the field "ownProduction", grouping it by the foreign key "market" (using the market's human readable name, __name from the Market table) and the week number (which is built-in function __year of the date field "dateContract").

However, when I try to read the results of the query in the last line in the code example below., I get the error:

"'dict' object has no attribute 'ownProduction'"

So "ownProduction" is to my surprise not a field in the query.

What am I doing wrong?

    contract_query = (Project.objects.all()
                             .filter(department=department_id)
                             .filter(datePipeline__year=year)
                             .filter(stage="WON")
                             .annotate(week=ExtractWeek('dateContract'))
                             .values('week','market__name')
                             .annotate(Sum('ownProduction'))
                         )
    for contract_row in contract_query:
        contract_sum = contract_row.ownProduction

It doesn't change anything using

                             .annotate(ownProduction = Sum('ownProduction'))

cheers + thanks, Mikkel

[Matthew Pava]

You need to include “ownProduction” in your values list prior to the annotation.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/525b78b7-ada3-4a49-818a-c02fc1bf51b0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mikkel Kromann]

Thanks. But strange - exactly same error:

'dict' object has no attribute 'contracted'

    contract_query = (Project.objects.all()
                                 .filter(department=department_id)
                                 .filter(datePipeline__year=year)
                                 .filter(stage="WON")
                                 .annotate(week=ExtractWeek('dateContract'))

                                 .annotate(contracted=Sum('ownProduction'))
                                 .values('week','market__name', 'contracted')

                                )
    for contract_row in contract_query:

        contract_sum = contract_row.contracted

I am somewhat confused by this ...

Mikkel

To post to this group, send email to djang...@googlegroups.com.

[Gagan Kalia]

Try not to use _ (underscore) character as prefix or postfix to any user defined variable or db column. Its not recommended in django.

--

You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.

To post to this group, send email to django...@googlegroups.com.

[Matthew Pava]

Ah, the error has the answer.  contract_row is a dictionary, so you need to access it as if it were a dictionary.

contract_sum = contract_row['contracted']

To post to this group, send email to django...@googlegroups.com.

Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/d2748446-4d61-44a4-b435-c77eb4957818%40googlegroups.com.

[Mikkel Kromann]

@galan:

the __ is reference to a foreign key name for market, and __year is a function extracting the year from the datefield.

Sorry for not posting the model, that would have helped avoiding this.

@matthew

Yes, that's right. That was one error down. The following code works well, except that it doesn't group by week.

    contract_query = ( Project.objects
                              .values('market__market')

                              .filter(department=department_id)
                              .filter(datePipeline__year=year)
                              .filter(stage="WON")

                              .annotate(contracted=Sum('ownProduction'))
                              .annotate(week=ExtractWeek('dateContract'))

So, we can't add week to the first values line, since week has yet to be defined. So I tried moving it to the line after annotation of week:

    contract_query = ( Project.objects
                              .filter(department=department_id)
                              .filter(datePipeline__year=year)
                              .filter(stage="WON")
                              .annotate(contracted=Sum('ownProduction'))
                              .annotate(week=ExtractWeek('dateContract'))
                              .values('market__market','week')

WIth the code above, however, the error comes back: I get a "key error" for "contracted". So is that because of week? I tried:

    contract_query = ( Project.objects
                              .filter(department=department_id)
                              .filter(datePipeline__year=year)
                              .filter(stage="WON")
                              .annotate(contracted=Sum('ownProduction'))
                              .annotate(week=ExtractWeek('dateContract'))
                              .values('market__market')

No, same "key error" for "contracted". It seems to me that having the values() statement after the annotation deletes the annotation- is that correct?

And is that intended behavior?

If so, how can I then group by the week function of a datefield?

Or is there something that I got completely wrong?

thanks + cheers, Mikkel

[Matthew Pava]

Sometimes it helps to print out the results one function at a time to get an idea what Django is returning.

 

If you want to include “contracted” in your result set, you have to include “contracted” in your values function.  Values limits the result set to the columns you specify.

 

I suggest reviewing the annotation and values docs here:

https://docs.djangoproject.com/en/2.1/topics/db/aggregation/#order-of-annotate-and-values-clauses

To post to this group, send email to django...@googlegroups.com.

Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/410dbb7f-5b6f-4347-a637-5eaf4156f388%40googlegroups.com.

[Mikkel Kromann]

Thanks for your patience Matthew.I finally got it.

I'm still learning the ropes of Django, and I got caught up in multiple errors (the order of lines in the query and picking out query values as dict elements or object elements).

The running code with GROUP BY using annotated functions looks like this:

    contract_query = ( Project.objects
                              .filter(department=department_id)
                              .filter(datePipeline__year=year)
                              .filter(stage="WON")
                              .annotate(contracted=Sum('ownProduction'))
                              .annotate(week=ExtractWeek('dateContract'))

                              .values('market__market','week','contracted')
                      )        

cheers + thanks again, Mikkel