id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
31163	Use filters after when case, the table is not reused.	Ashe	nobody	"== 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;
}}}"	Bug	closed	Database layer (models, ORM)	2.2	Normal	duplicate	join		Unreviewed	0	0	0	0	0	0