Hi,
I ran in this today. Say i have these two models in my app called atest
class Person(models.Model):
name = models.CharField(max_length=255)
age = models.IntegerField(null=True)
class Car(models.Model):
owner = models.ForeignKey('Person', related_name='cars')
brand = models.CharField(max_length=255)
Suppose that some persons have more than one car, some have one and others
none.
First case, i want to get a list of persons with the number of cars they
have that includes 0
*Person.objects.all().annotate(Count('cars')).order_by('-cars__count')
*
it creates a query like:
*SELECT atest_person.id, atest_person.name, atest_person.age,
COUNT(atest_car.id) AS cars__count
FROM atest_person
LEFT OUTER JOIN atest_car ON (atest_person.id = atest_car.owner_id)
GROUP BY atest_person.id,
atest_person.name,
atest_person.age
ORDER BY cars__count DESC*
This is all fine,
But what if i want to get a list of persons with the number of "Ford" cars
they have?
You might say:
*
Person.objects.filter(cars__brand='Ford').annotate(Count('cars')).order_by('-cars__count')
*
generates a query like:
*SELECT atest_person.id, atest_person.name, atest_person.age,
COUNT(atest_car.id) AS cars__count
FROM atest_person
LEFT OUTER JOIN atest_car ON (atest_person.id = atest_car.owner_id)
WHERE atest_car.brand = 'Ford'
GROUP BY atest_person.id,
atest_person.name,
atest_person.age
ORDER BY cars__count DESC*
I get a list of persons with how many Ford cars they have IF they have one
or more, those that don't have a Ford car won't make it into the
resultset, with a 0 for cars__count like in the previous query where we
didn't care for the model. This is because of the WHERE that filters out
nulls.
But i want the result like the previous one, with a 0 if they don't have
one.
In SQL this is, put the car brand condition in the JOIN clause:
*
SELECT atest_person.name, count(atest_car.id) as "cars__count"
FROM atest_person
LEFT OUTER JOIN atest_car ON (atest_person.id = atest_car.owner_id)
AND atest_car.brand = 'Ford'
GROUP BY atest_person.name
ORDER BY cars__count desc*
So i have two options,
one to execute a raw query or something like
*
Person.objects.all().extra(
select = {
'cars__count':'SELECT count(id) FROM atest_car WHERE
atest_person.id = atest_car.owner_id AND atest_car.brand = %s'
},
select_params = ['Ford']
).order_by('-cars__count')*
that makes this query:
*SELECT
(SELECT count(id)
FROM atest_car
WHERE atest_person.id = atest_car.owner_id
AND atest_car.brand = 'Ford') AS cars__count,
atest_person.id,
atest_person.name,
atest_person.age
FROM atest_person
ORDER BY cars__count DESC *
But i would really like if one could only pass a parameter to annotate or
count to include zeroes.
Sorry for the long read, a fine example of this problem is here
http://searchoracle.techtarget.com/answer/LEFT-OUTER-JOIN-with-ON-condition-or-WHERE-condition
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
