TJB
TJB

Reputation: 3866

Get queryset of objects that are unique based on a field - Django

I have a large inventory of vehicles, and there are a lot of duplicates that are only unique by a stock number. I am trying to query for a list of all vehicles, but only want one vehicle for each model number, ignoring it's stock number field.

V1.stock_no = 15393

V2.stock_no = 15394

V1.model_no = 98874

V2.model_no = 98874

So when doing a query in Django, I only want one instance of each object with that model no. I read this Stack Overflow question Select DISTINCT individual columns in django?, but

q = ProductOrder.objects.values('Category').distinct()

just returns a distinct list of model numbers, and not the object instance, and

Productorder.objects.all().distinct('category')

made available since Django 1.4, only works with PostgreSQL, and not SQLite3.

Is there any way to do this with SQLite, without having some contrived for loop to get it done ?

Upvotes: 1

Views: 3137

Answers (1)

NS0
NS0

Reputation: 6126

This is one potential solution

q = ProductOrder.objects.filter(
    id__in=ProductOrder.objects.values('Category')
                               .distinct()
                               .values_list('id', flat=True))

Edit: alternatively, you can try

q1 = ProductOrder.objects.values('Category')
                               .distinct()
                               .annotate(x=Max('id'))
q2 = ProductOrder.objects.filter(
    id__in=[i["x"] for i in q1])

I realize this is not ideal however due to having to loop.

Upvotes: 1

Related Questions