Django distinct group by query on two fields

Question

I have a model which have 2 fields.

class MyModel:
   tcode = Charfield
   created_on = Date field
   #some more fields

now this model can have multiple rows with same tcode, and each row can have different day or same.

e.g.

tcode1, 1/2/2001
tcode2, 1/2/2001
tcode2, 2/2/2001
....etc.

I want to filter query on this model such that tcode and date field combination should be unique. how can I get all those objects.

i was trying to do this

MyModel.objects.all().order_by('tcode').distinct('tcode', 'created_on')

Now you may ask that in case if there are two rows with same data in two fields which one row I want! it doesn't matter to me, any row would work fine.

Answer 1

I don't think there's one single query that could do this, because there's no mechanism from database to pick random one from duplicates. However, if you only care about those two fields, you could do:

MyModel.objects.order_by('tcode').values('tcode', 'created_on').distinct()

This won't give you complete MyModel objects, but a list of dictionaries that contain all the existing combinations of tcode and created_on.

Answer 2

Today I faced the same problem. In Django admin in get_queryset method I needed to make the distinct operation in multiple fields. I solved this problem this way:

from django.db.models import TextField
from django.db.models.functions import Concat
Mymodel.objects.filter(...).annotate(distinct_name=Concat('tcode', 'created_on', output_field=TextField())).order_by('distinct_name').distinct('distinct_name')

This decision helps to return QuerySet with ID of the record