Queryset only get one (any) record for each foreign key

Question

Say I have the following models:

class Manufacturer(Model):
    name = models.CharField(max_length=255)
    origin = models.CharField(max_length=255)

class Car(Model):
    model = models.CharField(max_length=255)
    manf = models.ForeignKey(Manufacturer, related_name="cars")

And then I want to run the following query:

usa_manfs = Manufacturer.objects.filter(origin='USA')
usa_manf_cars = Car.objects.filter(manf__in=usa_manfs)

However I would only like to have 1 Car per Manufacturer in the QuerySet.

I know I could go the other route and do something like usa_manfs.cars[0] however wouldn't this mean I need to do a query for each Manufacturer to get all related cars?

To clarify, I don't need any control over which instance of Car is retrieved in relation to each Manufacturer. I simply need to end up with a list of Cars wherein the Manufacturer is unique.

SOLUTION FOR NOW

After much deliberation I've decided to go for a Python solution and prevent duplicates in the loop.

The data I'm working with is likely to have relatively few duplicates (in which my example possibly doesn't hold up) on the ForeignKey field and since I'm not able to get a solution working that keeps it within Django ORM, I'll simply track the ForeignKey and exclude repeats in a loop. Since I'm looping over them anyway, I don't think I'm creating much more work overall.

e.g.:

cars = list(Car.objects.all())
final_list = []
used_before = []

for car in cars:
    if not car.manf_id in used_before:
        used_before.append(car.manf_id)
        doStuff(car)
        final_list.append(car)

If a better solution comes along I'll accept that instead.

Why not just get all the `Cars` ? One Car can only have 1 Manufacturer right? — Henrik Andersson, Apr 23 '13 at 07:43
I don't want duplicate Manufacturers, which all Cars cannot guarantee. — DanH, Apr 23 '13 at 07:57

Burhan Khalid · Answer 1 · 2013-04-23T10:05:49.057

1

I simply need to end up with a list of Cars wherein the Manufacturer is unique.

How about using distinct:

cars = Car.objects.order_by('manf').distinct('manf')

Since the above is only supported on PostgreSQL, you have to work around it:

m = Manufacturer.objects.all()

cars = []
for i in m:
    if Car.objects.filter(manf=i).exists():
       cars.append(Car.objects.filter(manf=i).order_by('?')[1])

This will give you one random car for each manufacturer.

edited Apr 23 '13 at 10:05

answered Apr 23 '13 at 07:40

Burhan Khalid

169,990
18
245
284

This does sounds like exactly what I want, however trying it I get: `NotImplementedError: DISTINCT ON fields is not supported by this database backend`. I assume either MySQL, or my version does not support it. – DanH Apr 23 '13 at 08:03
1

Its only supported on PostgreSQL unfortunately. – Burhan Khalid Apr 23 '13 at 10:01

Akshar Raaj · Answer 2 · 2013-04-23T07:50:10.350

0

I am not sure how we can do it with orm. Can tell how we can achieve it through direct sql:

from django.db import connection
cursor = connection.cursor()

cursor.execute("select c.model from myapp_car c, myapp_manufacturer m where m.origin='USA' and m.id=c.manf_id group by m.id")

edited Apr 23 '13 at 07:50

answered Apr 23 '13 at 07:35

Akshar Raaj

14,231
7
51
45

Queryset only get one (any) record for each foreign key

2 Answers2