Given the following model is would seem that the Generic Relations' GenericRelatedObjectManager? generates invalid SQL, as the doctest in the following snippet proves:
from django.db import models
from django.contrib.contenttypes.generic import GenericForeignKey, GenericRelation
from django.contrib.contenttypes.models import ContentType
#==============================================================================
class Alert(models.Model):
"""
Alerts for all kinds of widgets
# create a blue widget and add an alert
>>> blue = BlueWidget.objects.create(name='Blue 1')
>>> alert = Alert(name='alert 1')
>>> alert.object = blue
>>> alert.save()
# create a red widget and add an alert
>>> red = RedWidget.objects.create(name='Red 1')
>>> alert = Alert(name='alert 2')
>>> alert.object = red
>>> alert.save()
# create a red widget with no alert
>>> red = RedWidget.objects.create(name='Red 2')
# create a blue widget and add an alert
>>> blue = BlueWidget.objects.create(name='Blue 2')
>>> alert = Alert(name='alert 3')
>>> alert.object = blue
>>> alert.save()
# get a list of red widgets with alerts - there should be only 1
>>> reds = RedWidget.objects.filter(alerts__name__contains='alert').distinct()
>>> reds
[<RedWidget: RedWidget Red 1>]
# this currently yields: [<RedWidget: RedWidget Red 1>, <RedWidget: RedWidget Red 2>]
"""
created = models.DateTimeField(auto_now_add=True)
name = models.CharField(maxlength=128)
content_type = models.ForeignKey(ContentType)
object_id = models.PositiveIntegerField()
object = GenericForeignKey('content_type', 'object_id')
#--------------------------------------------------------------------------
def __str__(self):
return '%s alert for %s widget' % (self.name, self.object)
#==============================================================================
class RedWidget(models.Model):
"""
The red kind of widget
"""
created = models.DateTimeField(auto_now_add=True)
name = models.CharField(maxlength=128)
alerts = GenericRelation(Alert)
#--------------------------------------------------------------------------
def __str__(self):
return 'RedWidget %s' % (self.name)
#==============================================================================
class BlueWidget(models.Model):
"""
The blue kind of widget
"""
created = models.DateTimeField(auto_now_add=True)
name = models.CharField(maxlength=128)
alerts = GenericRelation(Alert)
#--------------------------------------------------------------------------
def __str__(self):
return 'BlueWidget %s' % (self.name)
Following the doctest above, the generated SQL after the last statement in the doctest:
reds = RedWidget.objects.filter(alerts__name__contains='alert').distinct()
is this:
SELECT DISTINCT `testapp_redwidget`.`id`,`testapp_redwidget`.`created`,`testapp_redwidget`.`name`
FROM `testapp_redwidget`
LEFT OUTER JOIN `testapp_alert` AS `m2m_testapp_redwidget__alerts` ON `testapp_redwidget`.`id` = `m2m_testapp_redwidget__alerts`.`object_id`
INNER JOIN `testapp_alert` AS `testapp_redwidget__alerts` ON `m2m_testapp_redwidget__alerts`.`id` = `testapp_redwidget__alerts`.`id`
WHERE (`testapp_redwidget__alerts`.`name` LIKE '%alert%')
That is - although it is linking to the right table (redwidget) it is not filtering on content type in the alert table. As a result it picks up the id of the second blue widget (2), which matches the id of the second, alert-less red widget (2) and links it in the query to produce the buggy result set.
One would expect this SQL to rather look like this:
SELECT DISTINCT `testapp_redwidget`.`id`,`testapp_redwidget`.`created`,`testapp_redwidget`.`name`
FROM `testapp_redwidget`
INNER JOIN `testapp_alert` AS `testapp_redwidget__alerts` ON `testapp_redwidget`.`id` = `testapp_redwidget__alerts`.`object_id`
INNER JOIN `django_content_type` AS `testapp_redwidget__django_content_type` ON `testapp_redwidget__alerts`.`content_type_id` = `testapp_redwidget__django_content_type`.`id`
WHERE (`testapp_redwidget__alerts`.`name` LIKE '%alert%')
AND (`testapp_redwidget__django_content_type`.`app_label` = 'testapp')
AND (`testapp_redwidget__django_content_type`.`model` = 'redwidget')
.. or, even better (and because I don't quite understand the reason for the LEFT OUTER join):
SELECT DISTINCT `testapp_redwidget`.`id`,`testapp_redwidget`.`created`,`testapp_redwidget`.`name`
FROM `testapp_redwidget`
INNER JOIN `testapp_alert` AS `testapp_redwidget__alerts` ON `testapp_redwidget`.`id` = `testapp_redwidget__alerts`.`object_id`
WHERE (`testapp_redwidget__alerts`.`name` LIKE '%alert%')
AND (`testapp_redwidget__alerts`.`content_type_id` = 9)
The only current workaround for this is to explicitly filter on content type id - this works:
>>> ctype = ContentType.objects.get_for_model(RedWidget)
>>> reds = RedWidget.objects.filter(alerts__name__contains='alert', alerts__content_type=ctype).distinct()
>>> reds
[<RedWidget: RedWidget Red 1>]
