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
tirsdag den 14. august 2018 kl. 20.40.16 UTC+2 skrev 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
>
>
>
>
>
> *From:* django...@googlegroups.com <javascript:> [mailto:
> django...@googlegroups.com <javascript:>] *On Behalf Of *Mikkel Kromann
> *Sent:* Tuesday, August 14, 2018 1:27 PM
> *To:* Django users
> *Subject:* Re: GROUP BY
>
>
>
>
>
> @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
>
>
>
>
> tirsdag den 14. august 2018 kl. 15.28.01 UTC+2 skrev 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']
>
>
>
> *From:* django...@googlegroups.com [mailto:django...@googlegroups.com] *On
> Behalf Of *Mikkel Kromann
> *Sent:* Monday, August 13, 2018 11:51 PM
> *To:* Django users
> *Subject:* Re: GROUP BY
>
>
>
>
>
> 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
>
>
>
>
>
> mandag den 13. august 2018 kl. 22.24.48 UTC+2 skrev Matthew Pava:
>
> You need to include “ownProduction” in your values list prior to the
> annotation.
>
>
>
>
>
> *From:* django...@googlegroups.com [mailto:django...@googlegroups.com] *On
> Behalf Of *Mikkel Kromann
> *Sent:* Monday, August 13, 2018 3:22 PM
> *To:* Django users
> *Subject:* GROUP BY
>
>
>
> 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
>
> --
> 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 djang...@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
>
> <https://groups.google.com/d/msgid/django-users/525b78b7-ada3-4a49-818a-c02fc1bf51b0%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
> --
> 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 djang...@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
>
> <https://groups.google.com/d/msgid/django-users/d2748446-4d61-44a4-b435-c77eb4957818%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
> --
> 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 <javascript:>.
> To post to this group, send email to djang...@googlegroups.com
> <javascript:>.
> 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
>
> <https://groups.google.com/d/msgid/django-users/410dbb7f-5b6f-4347-a637-5eaf4156f388%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@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/b0e6c0bc-ebca-4759-90ff-22f9fdd60758%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
