annotate() exception if there are same fields name OR in Q

[Falldog]

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

[Josh Smeaton]

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:

Shipment.objects
    .annotate(commodity_count=Count('commodity_set'))
    .filter(name__isnull=False)

# different!
Shipment.objects
    .filter(name__isnull=False)
    .annotate(commodity_count=Count('commodity_set'))

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:

Shipment.objects
    .filter(Q(name__isnull=False) | Q(subshipment__name__isnull=False)) # WHERE CLAUSE
    .annotate(commodity_count=Count('commodity_set'))  # count excludes null names
    .filter(commodity_count=0)  # inserts HAVING CLAUSE

Closing this as invalid because you can't combine aggregate filters with regular filters, but please reopen if I've misinterpreted your query.

[Falldog]

Thanks for your reply,
I just realize it is different put filter() before/after annotate()

I think I need to restructure my code
thanks