I want to be able to count how many times each value (including nulls) is present. I think that exactly what count is for. -:)
Upon a closer look the problem is not limited to foreign keys. When django generates count clauses in `count(<name>)` form here's what happens (in MySQL at least): SELECT foo, count(foo) FROM test group by foo; +-------+------------+ | foo | count(foo) | +-------+------------+ | NULL | 0 | | one | 1 | | three | 3 | | two | 2 | +-------+------------+ This is what MySQL outputs. Django simply takes these rows and outputs them in a list of dictionaries to the user. The problem here is not that nulls are not counted but that nulls are included in the output. Looking at these results one may conclude that there are no null values, right? Well actually wrong. Using `count(*)` form produces quite different results: SELECT foo, count(*) FROM test group by foo; +-------+----------+ | foo | count(*) | +-------+----------+ | NULL | 1 | | one | 1 | | three | 3 | | two | 2 | +-------+----------+ Now we see the whole picture and the sum of all individual counts is equal to MyModel.objects.count() This inclines to believe (lack of experience with other back ends prevents me from asserting this) that current behaviour is a bug and that django should generate `count(*)` clauses instead of `count(<name>)`. On Wed, Jan 26, 2011 at 9:28 AM, Stephen Burrows <stephen.r.burr...@gmail.com> wrote: > Perhaps I'm missing something, but if you count all the defined > foreign keys AND all the null values, won't you just end up with a > count of the parent model? Or are you saying that you explicitly want > to count how many values are null *instead of* defined? > > On Jan 25, 2:39 pm, Sergiy Kuzmenko <s.kuzme...@gmail.com> wrote: >> Hi there! >> >> Annotating a nullable foreign field with Count seems to always return >> the count of null values as zero (at least in MySQL environment). A >> quick look into this problem reveals that the corresponding SQL clause >> is generated as `count(<field_name>)` [1]. This causes to exclude null >> values from annotation in MySQL [2]. I believe the same applies to >> PostgreSQL [3] and likely to other backends. >> >> In my mind, current behaviour is bit of a bug (it is definitely quirky >> in MySQL). But it is possible that not counting null values was >> intentional. In this case there should be at least a way for user to >> specify that null values must also be counted. Perhaps something like: >> >> Count(field_name, count_nulls=True) >> >> [1]http://code.djangoproject.com/browser/django/trunk/django/db/models/s... >> [2]http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#functi... >> [3]http://www.postgresql.org/docs/9.0/static/sql-expressions.html >> >> Thanks >> Sergiy > > -- > You received this message because you are subscribed to the Google Groups > "Django developers" group. > To post to this group, send email to django-developers@googlegroups.com. > To unsubscribe from this group, send email to > django-developers+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/django-developers?hl=en. > > -- You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to django-developers@googlegroups.com. To unsubscribe from this group, send email to django-developers+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.
