CODEWITHSUNDEEP
django
Ev.
Ev.

Reputation: 1567

How to filter latest objects using nested query in Django

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))

Upvotes: 0

Views: 564

Answers (2)

Bernhard Vallant
Bernhard Vallant

Reputation: 50776

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.

Upvotes: 1

Moses Koledoye
Moses Koledoye

Reputation: 78546

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')

Upvotes: 1

Related Questions