Problem with django aggregation queries

Question

Hello

I have a number of elements that can be of one or more types.

class Type(models.Model):
    name = models.CharField(max_length=128, unique=True)

class Element(models.Model):
    name = models.CharField(max_length=128, unique=True)
    type = models.ManyToManyField('Type')

Let's say that I have 3 types and 3 elements:

In [3]: Type.objects.all()
Out[3]: [<Type: His Type>, <Type: My Type>, <Type: Your Type>]

In [4]: [(e,e.type.all()) for e in Element.objects.all()]
Out[4]: 
[(<Element: First Element>, [<Type: My Type>]),
(<Element: Second Element>, [<Type: Your Type>]),
(<Element: Third Element>,
  [<Type: My Type>, <Type: Your Type>, <Type: His Type>])]

I'm trying to get a queryset with the elements that are only of the type "My Type"

My idea was to get the elements of this type and check that they are only of one type.

But for some reason it thinks that "Third Element" is of only one type

In [5]: my_type=Type.objects.get(name='My Type')

In [6]: my_type.element_set.annotate(num_types=Count('type')).filter(num_types__exact=1)
Out[6]: [<Element: First Element>, <Element: Third Element>]

In [7]: [(e,e.num_types) for e in my_type.element_set.annotate(num_types=Count('type'))]
Out[7]: [(<Element: First Element>, 1), (<Element: Third Element>, 1)]

when it is of three types

In [8]: Element.objects.get(name='Third Element').type.count()
Out[8]: 3

What I'm doing wrong?

Answer 1

Given that this is a standard many-to-many, you can call 'my_type.element_set.all()', that should give you the Element instances that are of that type. You can also go the other way (using pluralized version of model name, not '_set') to get an elements types like so: 'my_element.types.all()'.

To get the count, just do 'my_type.element_set.count()'.

Answer 2

The SQL query that Django is performing behind the scenes is limiting you to only the rows that contain type_element_type.element_id = 1. So the annotated count is only applying to those, hence 1 each.

Your query [7] above becomes this SQL (as shown by this):

SELECT "type_element"."id", "type_element"."name", COUNT("type_element_type"."type_id") 
AS "num_types" FROM "type_element" LEFT OUTER JOIN "type_element_type"
ON ("type_element"."id" = "type_element_type"."element_id")
WHERE "type_element_type"."type_id" = 1 
GROUP BY "type_element"."id", "type_element"."name"

and from sqlite3:

sqlite> SELECT * FROM "type_element" LEFT OUTER JOIN "type_element_type" ON ("type_element"."id" = "type_element_type"."element_id") WHERE "type_element_type"."type_id" = 1;
1|First Element|1|1|1
3|Third Element|3|3|1

Answer 3

I ended doing

Element.objects.filter(pk__in=my_type.element_set.values_list('pk', flat=True)).annotate(num_types=Count('type')).filter(num_types=1)

That way I'm not affected by the join that happens when I start with my_type.element_set

If you are wondering about the SQL involved:

In my_type.element_set.annotate(num_types=Count('type')).filter(num_types__exact=1) it does:

SELECT `senalesweb_element`.`id`, `senalesweb_element`.`name`, COUNT(`senalesweb_element_type`.`type_id`) AS `num_types` FROM `senalesweb_element` LEFT OUTER JOIN `senalesweb_element_type` ON (`senalesweb_element`.`id` = `senalesweb_element_type`.`element_id`) WHERE (`senalesweb_element_type`.`type_id` = 1 ) GROUP BY `senalesweb_element`.`id` HAVING COUNT(`senalesweb_element_type`.`type_id`) = 1  ORDER BY NULL LIMIT 21

While in Element.objects.filter(pk__in=my_type.element_set.values_list('pk', flat=True)).annotate(num_types=Count('type')).filter(num_types=1) it does:

SELECT `senalesweb_element`.`id`, `senalesweb_element`.`name`, COUNT(`senalesweb_element_type`.`type_id`) AS `num_types` FROM `senalesweb_element` LEFT OUTER JOIN `senalesweb_element_type` ON (`senalesweb_element`.`id` = `senalesweb_element_type`.`element_id`) WHERE (`senalesweb_element`.`id` IN (SELECT U0.`id` FROM `senalesweb_element` U0 INNER JOIN `senalesweb_element_type` U1 ON (U0.`id` = U1.`element_id`) WHERE U1.`type_id` = 1 )) GROUP BY `senalesweb_element`.`id` HAVING COUNT(`senalesweb_element_type`.`type_id`) = 1  ORDER BY NULL LIMIT 21