#31163: use filters after when case, the table is not reused. -------------------------------------+------------------------------------- Reporter: Ashe | Owner: nobody Type: | Status: new Uncategorized | Component: Database | Version: 2.2 layer (models, ORM) | Severity: Normal | Keywords: join Triage Stage: | Has patch: 0 Unreviewed | Needs documentation: 0 | Needs tests: 0 Patch needs improvement: 0 | Easy pickings: 0 UI/UX: 0 | -------------------------------------+------------------------------------- == Model {{{#!python class Device(models.Model): pass
class DeviceItem(models.Model): device = models.ForeignKey( Device, on_delete=models.PROTECT, related_name='item_set') class DeviceItemLocationHistory(models.Model): device_item = models.ForeignKey( DeviceItem, on_delete=models.PROTECT, related_name='devicerequestitem_set') location = models.ForeignKey(Location, on_delete=models.PROTECT) prev = models.OneToOneField( 'self', on_delete=models.PROTECT, blank=True, null=True) next = models.OneToOneField( 'self', on_delete=models.PROTECT, blank=True, null=True) changed_on = models.DateTimeField(auto_now_add=True) class DeviceMoveRequest(models.Model): device_item = models.ForeignKey( DeviceItem, on_delete=models.PROTECT, related_name='location_history_set') location_history = models.OneToOneField( 'devices.DeviceItemLocationHistory', verbose_name='위치', on_delete=models.PROTECT, blank=True, null=True, ) request_type = models.CharField(max_length=50) }}} == Query {{{#!python queryset = Device.objects.annotate( diligence_site_partition_id=Case( When( item_set__location_history_set__devicerequestitem__request_type=[ 'purchaserequest', 'storerequest', ], then=F('item_set__location_history_set__location_id'), ), When( item_set__location_history_set__devicerequestitem__request_type='releaserequest', then=F('item_set__location_history_set__prev__location_id') ), output_field=IntegerField() ) ).values( 'diligence_site_partition_id', 'item_set__device', ).filter( item_set__location_history_set__changed_on__year='2020', ) }}} == SQL === I expected {{{#!sql SELECT `devices_deviceitem`.`device_id`, CASE WHEN `logistics_devicemoverequest`.`request_type` IN ('purchaserequest', 'storerequest') THEN `devices_deviceitemlocationhistory`.`location_id` WHEN `logistics_devicemoverequest`.`request_type` = 'releaserequest' THEN T8.`location_id` ELSE NULL END AS `diligence_site_partition_id`, FROM `devices_device` LEFT OUTER JOIN `devices_deviceitem` ON (`devices_device`.`id` = `devices_deviceitem`.`device_id`) LEFT OUTER JOIN `devices_deviceitemlocationhistory` ON (`devices_deviceitem`.`id` = `devices_deviceitemlocationhistory`.`device_item_id`) LEFT OUTER JOIN `logistics_devicemoverequest` ON (`devices_deviceitemlocationhistory`.`id` = `logistics_devicemoverequest`.`location_history_id`) LEFT OUTER JOIN `devices_deviceitemlocationhistory` T8 ON (`devices_deviceitemlocationhistory`.`prev_id` = T8.`id`) WHERE (`devices_deviceitemlocationhistory`.`changed_on` BETWEEN '2019 - 12 - 31 15:00:00' AND '2020-12-31 14:59:59.999999') GROUP BY `devices_deviceitem`.`device_id`, CASE WHEN `logistics_devicemoverequest`.`request_type` IN ('purchaserequest', 'storerequest') THEN `devices_deviceitemlocationhistory`.`location_id` WHEN `logistics_devicemoverequest`.`request_type` = 'releaserequest' THEN T8.`location_id` ELSE NULL END ORDER BY NULL; }}} === but not reuse table Unexpected join will occur. {{{#!sql SELECT `devices_deviceitem`.`device_id`, CASE WHEN `logistics_devicemoverequest`.`request_type` IN ('purchaserequest', 'storerequest') THEN `devices_deviceitemlocationhistory`.`location_id` WHEN `logistics_devicemoverequest`.`request_type` = 'releaserequest' THEN T8.`location_id` ELSE NULL END AS `diligence_site_partition_id`, FROM `devices_device` LEFT OUTER JOIN `devices_deviceitem` ON (`devices_device`.`id` = `devices_deviceitem`.`device_id`) LEFT OUTER JOIN `devices_deviceitemlocationhistory` ON (`devices_deviceitem`.`id` = `devices_deviceitemlocationhistory`.`device_item_id`) LEFT OUTER JOIN `logistics_devicemoverequest` ON (`devices_deviceitemlocationhistory`.`id` = `logistics_devicemoverequest`.`location_history_id`) LEFT OUTER JOIN `devices_deviceitemlocationhistory` T8 ON (`devices_deviceitemlocationhistory`.`prev_id` = T8.`id`) INNER JOIN `devices_deviceitem` T11 ON (`devices_device`.`id` = T11.`device_id`) INNER JOIN `devices_deviceitemlocationhistory` T12 ON (T11.`id` = T12.`device_item_id`) WHERE (`T12`.`changed_on` BETWEEN '2019 - 12 - 31 15:00:00' AND '2020-12-31 14:59:59.999999') GROUP BY `devices_deviceitem`.`device_id`, CASE WHEN `logistics_devicemoverequest`.`request_type` IN ('purchaserequest', 'storerequest') THEN `devices_deviceitemlocationhistory`.`location_id` WHEN `logistics_devicemoverequest`.`request_type` = 'releaserequest' THEN T8.`location_id` ELSE NULL END ORDER BY NULL; }}} -- Ticket URL: <https://code.djangoproject.com/ticket/31163> Django <https://code.djangoproject.com/> The Web framework for perfectionists with deadlines. -- You received this message because you are subscribed to the Google Groups "Django updates" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-updates+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/049.43da515a96b72733de1a3a370f1da039%40djangoproject.com.