How to filter latest objects using nested query in Django

Question

I have the following relation:

class Product(foo):
    name = models.CharField()

class Maintenance(foo):
    product = models.ForeignKey(Product, related_name="maintenances")
    start = models.DateField()
    end = models.DateField()

I would like to filter all products with the latest (only the latest) maintenance object having start and end attributes in a given date range.

Something like this:

Product.objects.filter(maintenances__last__end__gte=today.now(), maintenances__last__end__lte=today.now()+datetime.timedelta(days=30))

Moses Koledoye · Accepted Answer · 2016-09-29 14:19:12Z

1

You could filter the products on the selected range of dates for maintenances and then take the lastest maintenance using annotation on Max:

import datetime as dt

from django.db.models import Max

start_date = dt.datetime.now()
end_date = dt.datetime.now() + dt.timedelta(days=30)

products = Product.objects.filter(maintenances__start__gte=start_date, maintenances__end__lte=end_date)\
                          .annotate(most_recent_maint=Max('maintenances__id'))\
                          .prefetch_related('maintenances')

edited Sep 29, 2016 at 14:19

answered Sep 29, 2016 at 14:13

Moses Koledoye

78.8k8 gold badges139 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bernhard Vallant · Accepted Answer · 2016-10-04 10:54:55Z

1

In some cases it also might make sense to think the other way round: Select the latest Maintenance object for every product:

# filter for time range
maintenances = Maintenance.objects.filter(
    end__gte=today.now(),
    end__lte=today.now() + datetime.timedelta(days=30)
)
# get latest with distinct product id
maintenances = maintenances.order_by(
    'product_id', '-end'
).distinct('product_id')
# do a `select_related` to get all products in the same query
maintenances = maintenances.select_related('product')

Note that passing arguments to distinct() only works if you are using PostgreSQL.

edited Oct 4, 2016 at 10:54

answered Sep 30, 2016 at 7:34

Bernhard Vallant

50.9k21 gold badges126 silver badges149 bronze badges

3 Comments

Ev. Over a year ago

But how do I select the latest maintenance record for every entry I have and make sure the filter is based only on it? If I use last_ as you provided, it doesn't work. It tries to use it as an attribute.

Bernhard Vallant Over a year ago

Sorry just wrongly copied that... If I understand your question properly it should just filter on end of the Maintenance objects. Using order_by with distinct shoud just give you the latest object for every product_id.

Ev. Over a year ago

That clarifies. Thanks.

Collectives™ on Stack Overflow

How to filter latest objects using nested query in Django

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related