Get a queryset of objects where a field is distinct-CodePudding

I have a model like the following:

class Reports(models.Model):
    reportid = models.AutoField(primary_key=True, unique=True)
    name = models.CharField(max_length=100)
    description = models.CharField(max_length=300, default='', blank=True)
    value = models.FloatField(default=0)

I need to search the model by matching a search term in the name field. Then I need to return a queryset containing objects each of which have unique names which match the search term.

See the example:

testsearchterm = 'bs'

initreports = Reports.objects.filter(name__lower__contains=testsearchterm)

print("initreports:", initreports)

Result:

initreports: <QuerySet [<Reports: FBS>, <Reports: PPBS>, <Reports: FBS>, <Reports: FBS>, <Reports: PPBS>, <Reports: FBS>, <Reports: FBS>, <Reports: PPBS>, <Reports: Random Blood Sugar (RBS)>,

Now I get distinct names

reports = Reports.objects.filter(name__lower__contains=testsearchterm).values_list('name', flat=True).distinct()
print("Unique values:", reports)

Result:

Unique values: <QuerySet ['FBS', 'PPBS', 'Random Blood Sugar (RBS)', 'Sleep study to r/o Obstructive sleep apnea', 'Serum HBsAg']>

Now I need the full queryset not just the names, so that I can serialize the results and return the other fields too.

for name in tests:
    res = Reports.objects.filter(name=name).first()
    print(res, type(res))

Results:

FBS <class 'appointments.models.Reports'>
PPBS <class 'appointments.models.Reports'>
Random Blood Sugar (RBS) <class 'appointments.models.Reports'>
Sleep study to r/o Obstructive sleep apnea <class 'appointments.models.Reports'>
Serum HBsAg <class 'appointments.models.Reports'>

I need a queryset of the above, like the following:

<QuerySet [<Reports: FBS>, <Reports: PPBS>, <Reports: RBS>, <Reports: HBsAg>]

It nees to be a proper queryset and not a list or set, so that I can use DRF modelserializer as is.

An example of the data is given as reference:

N.B. DISTINCT ON fields is not supported by this database backend(mysql)

CodePudding user response：

Try this:

unique_names = [Reports.objects.filter(name=name)[0].pk for name in Reports.objects.filter(name__lower__contains=testsearchterm).values_list('name', flat=True).distinct()]
query = Reports.objects.filter(pk__in=unique_names)

CodePudding user response：

You are filtering to return only the name from the following query set:

Reports.objects.filter(name__lower__contains=testsearchterm).values_list('name', flat=True).distinct()

If you remove the values_list call, you will get queryset instead of list of strings.

Reports.objects.filter(name__lower__contains=testsearchterm).distinct()

Reference: https://docs.djangoproject.com/en/4.0/ref/models/querysets/#distinct