annotate() questions

[Carsten Fuchs]

Dear Django fellows,

at https://docs.djangoproject.com/en/1.8/topics/db/aggregation/#joins-and-aggregates the
first example is:

>>> from django.db.models import Max, Min
>>> Store.objects.annotate(min_price=Min('books__price'), max_price=Max('books__price'))

which will annotate each Store object in the QuerySet with the minimum and maximum
prices that its books have.

What I was wondering is:


1) Is there a way to annotate each Store object with the actual Book objects related
to the minimum and maximum prices?

That is, if `s` is a Store object from the above QuerySet and we can access

s.min_price
s.max_price

would it also be possible to have

s.min_book # The Book object whose price is minimal
s.max_book # The Book object whose price is maximal

?


2) Can this annotation be filtered? For example, if for each Store we wanted to learn
the min and max prices of books published in 2014, can this be done?


I'd be very grateful for any hints! :-)

Best regards,
Carsten

[Carsten Fuchs]

Hello,

Am 08.04.2015 um 20:07 schrieb Carsten Fuchs:
> 1) Is there a way to annotate each Store object with the actual Book objects related
> to the minimum and maximum prices?
>

> 2) Can this annotation be filtered? For example, if for each Store we wanted to learn
> the min and max prices of books published in 2014, can this be done?

Anyone please?

Best regards,
Carsten

[Ramiro Morales]

On Wed, Apr 8, 2015 at 3:07 PM, Carsten Fuchs <carste...@cafu.de> wrote:

Dear Django fellows,

at https://docs.djangoproject.com/en/1.8/topics/db/aggregation/#joins-and-aggregates the first example is:

>>> from django.db.models import Max, Min
>>> Store.objects.annotate(min_price=Min('books__price'), max_price=Max('books__price'))

which will annotate each Store object in the QuerySet with the minimum and maximum prices that its books have.

[...]

  2) Can this annotation be filtered? For example, if for each Store we wanted to learn the min and max prices of books published in 2014, can this be done?

 

I can help with this one. In fact you cant combine annotations with filters. See

https://docs.djangoproject.com/en/1.8/topics/db/aggregation/#aggregations-and-other-queryset-clauses

"...When used with an annotate() clause, a filter has the effect of constraining the objects for which an annotation is calculated. For example..."

e.g.::

    Store.objects.filter(books__pubdate__year=2014).annotate(min_2014_price=Min('books__price'), max_2014_price=Max('books__price'))

HTH

--

Ramiro Morales
@ramiromorales

[Carsten Fuchs]

Hi Ramiro,

Am 2015-04-16 um 19:30 schrieb Ramiro Morales:
> https://docs.djangoproject.com/en/1.8/topics/db/aggregation/#aggregations-and-other-queryset-clauses
>
> "...When used with an annotate() clause, a filter has the effect of
> constraining the objects for which an annotation is calculated. For
> example..."
>
> e.g.::
>
> Store.objects.filter(books__pubdate__year=2014).annotate(min_2014_price=Min('books__price'),
> max_2014_price=Max('books__price'))
>

That's a very good info, many thanks for pointing me there!

Initially I thought that this would only limit/filter the Store objects
that are annotated ("a filter has the effect of constraining the objects
for which an annotation is calculated"), but the subsequent section
https://docs.djangoproject.com/en/1.8/topics/db/aggregation/#order-of-annotate-and-filter-clauses
clearly (in hindsight) explains that it, in this order, effectively
filters the book objects that are used for the annotation as well.

Now if only I knew how to obtain the actual book objects related to the
min/max prices...

Many thanks and best regards,
Carsten

[Carsten Fuchs]

Am 17.04.2015 um 21:14 schrieb Carsten Fuchs:
> Now if only I knew how to obtain the actual book objects related to the min/max prices...

Just for future reference, the best answer that I've found so far is:
http://blog.roseman.org.uk/2010/08/14/getting-related-item-aggregate/

Best regards,
Carsten