#25796 closed Bug (invalid)
annotate() exception if there are same fields name OR in Q
Reported by: | Falldog | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | annotate |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
I had try a complete annotate, got this issue
Fail on MySQL, good on SQLite
here is example models
class Shipment(models.Model): id = models.AutoField(primary_key=True) name = models.CharField(max_length=32, null=True) # => field name same as below class SubShipment(models.Model): id = models.AutoField(primary_key=True) primary_shipment = models.ForeignKey(Shipment, related_name='sub_shipment_set') name = models.CharField(max_length=32, null=True) # => field `name` same as ForeignKey(Shipment).`name` class Commodity(models.Model): id = models.AutoField(primary_key=True) shipment = models.ForeignKey(Shipment, related_name='commodity_set') description = models.CharField(max_length=128)
here is query example
only exception on Q() by OR
on the same field name condition, name
and sub_shipment_set__name
I try Q() AND
is okay
query = models.Shipment.objects.annotate(commodity_count=Count('commodity_set')) # OR all condition q = Q(commodity_count=0) q |= Q(name__isnull=False) | Q(sub_shipment_set__name__isnull=False) # query same `name` will cause exception query = query.filter(q)
got exception below
(1054, "Unknown column 'myapp_subshipment.name' in 'having clause'")
I had dump SQL query below
SELECT `myapp_shipment`.`id`, `myapp_shipment`.`name`, COUNT(`myapp_commodity`.`id`) AS `commodity_count` FROM `myapp_shipment` LEFT OUTER JOIN `myapp_commodity` ON (`myapp_shipment`.`id` = `myapp_commodity`.`shipment_id`) LEFT OUTER JOIN `myapp_subshipment` ON (`myapp_shipment`.`id` = `myapp_subshipment`.`primary_shipment_id`) GROUP BY `myapp_shipment`.`id`, `myapp_shipment`.`name`, `myapp_subshipment`.`name` HAVING (COUNT(`myapp_commodity`.`id`) = 0 OR `myapp_shipment`.`name` IS NOT NULL OR `myapp_subshipment`.`name` IS NOT NULL) ORDER BY NULL
for MySQL query command GROUP BY
on myapp_subshipment
.name
it must appear in SELECT
area
I try to put it into SELECT
area and execute query again, it's okay
Change History (3)
comment:1 by , 9 years ago
Summary: | annotate() exception if there is same field name OR in Q → annotate() exception if there are same fields name OR in Q |
---|
comment:2 by , 9 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
Version: | 1.8 → master |
comment:3 by , 9 years ago
Thanks for your reply,
I just realize it is different put filter() before/after annotate()
I think I need to restructure my code
thanks
The problem here is that you're combining two filters together that can't logically be grouped together. You've got half of a "HAVING" clause and half of a "WHERE" clause and you're trying to OR them together. This can't work.
This would work (Maybe you should split these two filter clause):
query.filter(commodity_count=0, Q(nameisnull=False) | Q(subshipment_setnameisnull=False))
# or maybe..
query.filter(commodity_count=0).filter(Q(nameisnull=False) | Q(subshipment_setnameisnull=False))
But I'm not really sure what you're trying to do with your query. Do you perhaps want to do the name is null checking *before* the Count? Adding the filtering after the annotation always filters after the result of annotating. These are different:
The first will do the count, and then will remove any with null. The second will remove any with null and *then* do a count, which is important when you're trying to filter on the result of the count.
This is *probably* the query you want:
Closing this as invalid because you can't combine aggregate filters with regular filters, but please reopen if I've misinterpreted your query.