CODEWITHSUNDEEP
pythonmysqldjangodjango-models
Lewis
Lewis

Reputation: 2798

Django: How to retrieve a Queryset of the latest, uniquely named records with a MySQL database

I require a query that can search my model for the latest, unique records and return a queryset of the results.

I need distinct on setup_name and latest on updated

models.py

class VehicleSetupModel(models.Model):

    inertia = models.IntegerField()
    tyre_pressure = models.IntegerField()


class StandardVehicleSetupModel(VehicleSetupModel):

    setup_name = models.CharField(max_length=20)
    updated = models.DateTimeField(auto_now=True)
    vehicle = models.ForeignKey(VehicleModel, on_delete=models.CASCADE)

What I've tried

I tried the following:

StandardVehicleSetupModel.objects.all().order_by('updated').distinct('setup_name')

but MySQL does not support DISTINCT ON querys.

Table Layout (DD-MM-YYYY)

     setup_name     |     updated      |      vehicle     
----------------------------------------------------------
        TEH              10-10-2020              1 
        TEH              11-10-2020              1
        TEL              10-10-2020              1
        TEL              08-10-2020              1
        TEL              01-10-2020              1
        TEP              01-10-2020              1

Expected Result (DD-MM-YYYY)

     setup_name     |     updated      |      vehicle     
----------------------------------------------------------
        TEH              11-10-2020              1
        TEL              10-10-2020              1
        TEP              01-10-2020              1

Upvotes: 1

Views: 68

Answers (1)

Huy Chau
Huy Chau

Reputation: 2240

Yes, MySQL doesn't support distinct() with arguments. We can use another way:

from django.db.models import Max


results = (
    StandardVehicleSetupModel.objects
      .values('setup_name')
      .annotate(max_id=Max('id'))
      .values_list('max_id', 'setup_name')
      .order_by('max_id')
)

Not tested yet, but it should work ^^

Upvotes: 3

Related Questions