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 developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/django-developers?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
