translate the SQL query to django model

Question

I have a SQL query :

   Select * From Order a Where date = (Select Max(b.date) From Order b Where a.company = b.company)

How to translate it into Django way using the Django ORM?

Model:

class Order(models.Model):
    id            = models.UUIDField(primary_key=True, default=uuid.uuid4)
    company       = models.ForeignKey('company', on_delete =models.CASCADE, null=False)
    text          = models.TextField(null=True, blank=True)                      
    date          = models.DateTimeField(null=True)

check this ans:https://stackoverflow.com/questions/21723512/django-filter-a-queryset-by-maxdate-year — chirag soni, Jun 13 '19 at 03:48

score 1 · Accepted Answer · answered Jun 13 '19 at 11:32

1

Are you looking to get each companies Order object with the maximum date? You could try

Order.objects.all().order_by('-date').distinct('company')

This should order by the queryset date descending and return the first distinct companies

answered Jun 13 '19 at 11:32

Mikey Lockwood

1,258
1
9
22

score 0 · Answer 2 · answered Jun 13 '19 at 06:42

One way you can do is run the query using raw:

order.objects.raw("Select * From Order a Where date = (Select Max(b.date) From Order b Where a.company = b.company)")

please check these docs below: https://docs.djangoproject.com/en/2.2/topics/db/sql/#mapping-query-fields-to-model-fields

translate the SQL query to django model

2 Answers2