#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.
