Search code examples
djangodjango-1.5django-q

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


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.


Solution

  • 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.