I'm bamboozled by Django aggregation:
https://docs.djangoproject.com/en/1.11/topics/db/aggregation/
I'd like to add a little to the example they present to clarify my
problem (keeping it in familiar territory with regards to the
documentation):
from django.db import models
class Author(models.Model):
name = models.CharField(max_length=100)
age = models.IntegerField() country= models.ForeignKey('Country')
class Country(models.Model):
name = models.CharField(max_length=100)
class Publisher(models.Model):
name = models.CharField(max_length=300)
num_awards = models.IntegerField()
class Book(models.Model):
name = models.CharField(max_length=300)
pages = models.IntegerField()
price = models.DecimalField(max_digits=10, decimal_places=2)
rating = models.FloatField()
authors = models.ManyToManyField(Author)
publisher = models.ForeignKey(Publisher)
pubdate = models.DateField()
class Store(models.Model):
name = models.CharField(max_length=300)
books = models.ManyToManyField(Book)
registered_users = models.PositiveIntegerField()
I'd like to list the name and age of the (one) author in each country
that published the last book before a given date.
Ostensibly the only option I have is:
LatestAuthors =
Author.objects.filter(book_set__pubdate__lte=SOMEDATE).values('country').annotate(Max('book_set__pubdate'))
That seems to work generating a query that authors are selected Grouped
By country and selecting the Maximum book pubdate ... but alas the
queryset is not a set of author objects it is a set of dictionaries that
only contain "country", I have no access to the authors name or age!
It seems the values() method is overloaded, and used to both select the
values that are in the SELECT and the values that are in the GROUP BY
clause of the generated query.
I would like select all the fields of Author but group by only Country.
So that I could for example list the name and age of the most recently
published author in each country.
I've read the docs at length and the evidence seems to suggest such an
overloading alas, and that I may need to write SQL!
But I would like to think my ignorance is great enough that a better
solution exists, to select * on Author while group by Author.country.
If I leave the values clause out, I get multiple Authors listed per
country (it is not grouped by country), but I get a set of authors back.
If I add a second values() clause:
LatestAuthors =
Author.objects.filter(book_set__pubdate__lte=SOMEDATE).values('country').annotate(Max('book_set__pubdate')).values()
Things get bizarre ... as in I see all the Author fields in the returned
dictionaries but the group by reverts to id in Author not country. Grrr.
Regards,
Bernd.
--
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/ff4d317e-558c-3ea5-ae29-933af1df7ea4%40gmail.com.
For more options, visit https://groups.google.com/d/optout.
