1

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 ?

Community
  • 1
  • 1
TJB
  • 3,706
  • 9
  • 51
  • 102

1 Answers1

1

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.

NS0
  • 6,016
  • 1
  • 15
  • 14
  • When I get a distinct list of 'model_no' values, it does in fact narrow the list down, from 2869 to 639, but .values_list( 'id', flat=True ) seems to reset the list count back to all instances, 2869. It would be nice if .values gave you back a list of values, with the id of the corresponding instance. – TJB Apr 21 '17 at 19:35
  • Sweet ! Unfortunately for me, I have more filtering in my view function based on query string parameters, and am now getting a 'too many SQL variables' error, but I believe your answer might be the most effective way to do this query on a SQLite database. – TJB Apr 21 '17 at 21:04