Duplicate results in Q query involving many to one relation to a model

Question

I have a Car model and a Passenger model with a ForeignKey to a Car object. Both Car and Passenger models have a name field.

I want to get all the cars that contain 'banana' in either the car or any passenger name, so I tried:

from django.db.models import Q
...
name = 'banana'
cars = Car.objects.filter(Q(name__icontains=name) | Q(passenger__name__icontains=name))

Curiously, when I have a Car named 'banana' and three passengers in it called anything (matching or not 'banana'), it returns three times the matching Car.

The questions are:

• Why?

• How to return only once the car? I think that .distinct() would work, but maybe there is a better way.

I am using Django 1.5.

Answer 1

Others have answered how to get what you want, but you also asked why, so here goes:

Your query will look something like this:

SELECT * FROM cars c
LEFT JOIN passengers p ON p.car_id = c.id
WHERE c.name LIKE '%banana%' OR p.name LIKE '%banana%' 

Basically, you're joining cars to passengers, and there are three passengers in a car where the car.name is 'banana'. So that returns three rows.