#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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.43da515a96b72733de1a3a370f1da039%40djangoproject.com.
