CODEWITHSUNDEEP
djangodjango-modelsdjango-querysetdjango-orm
user43944
user43944

Reputation: 159

Django QuerySet, Filtering Data Based on The Latest Entry of Each Data's Column/Field equals to a particular Value

#models.py
class Orders(models.Model):
    orderid = models.IntegerField(db_column='orderID', primary_key=True) 
    createdate = models.DateField(db_column='createDate', blank=True, null=True) 
    pickupdate = models.DateField(db_column='pickupDate', blank=True, null=True)  
    returndate = models.DateField(db_column='returnDate', blank=True, null=True)  
    pickupstore = models.ForeignKey(Branch, models.DO_NOTHING, db_column='pickupStore', blank=True, null=True,related_name = 'pickupstore')  
    returnstore = models.ForeignKey(Branch, models.DO_NOTHING, db_column='returnStore', blank=True, null=True,related_name = 'returnstore') 
    rentedvehicle = models.ForeignKey('Vehicles', models.DO_NOTHING, db_column='rentedVehicle', blank=True, null=True)  
    customer = models.ForeignKey(Customer, models.DO_NOTHING, db_column='customer', blank=True, null=True)


class Vehicles(models.Model):
    vehicleid = models.IntegerField(db_column='vehicleID', primary_key=True)  
    make = models.CharField(max_length=45, blank=True, null=True)
    model = models.CharField(max_length=45, blank=True, null=True)
    series = models.CharField(max_length=45, blank=True, null=True)

Orders model have foreign key rentedvehicle which refers to Vehicles models

I have filtered the data based on the pickupstore state, e.g. The list of vehicles that are picked up from store in a particular state

Vehicles.objects.filter(orders__pickupstore__state = request.POST['state'])

I want to be able to filter the filtered data above so that I can get a list of vehicles that are picked up from store in a particular state WITH its latest entry in Orders models has returnstore__state == a particular state

So basically, I want to achieve this:

Vehicles.objects.filter(orders__pickupstore__state = request.POST['state']).filter(the latest entry returnstore state =request.POST['state'])

Upvotes: 0

Views: 78

Answers (1)

jchung
jchung

Reputation: 953

Many possible solutions. Optimal depends on your Django version (1.11 or higher?). Here's one possible solution:

from django.db.models import Max, F

Vehicles.objects.filter(orders__pickupstore__state = request.POST['state']).annotate(max_date=Max('orders__returndate')).filter(orders__return_date=F('max_date')).filter(orders__state = request.POST['state'])

What's happening here is that we're identifying the most recent return date, then filtering the orders to only include the most recent, then filtering the Vehicles on the state of that latest order.

Upvotes: 1

Related Questions