I have a concrete base model, from which other models inherit (all models in this question have been trimmed for brevity):

class Order(models.Model):
    state = models.ForeignKey('OrderState')

Here are a few examples of the "child" models:

class BorrowOrder(Order):
    parts = models.ManyToManyField('Part', through='BorrowOrderPart')

class ReturnOrder(Order):
    parts = models.ManyToManyField('Part', through='ReturnOrderPart')

As you can see from these examples, each child model has a many-to-many relationship of Parts through a custom table. Those custom through-tables look something like this:

class BorrowOrderPart(models.Model):
    borrow_order = models.ForeignKey('BorrowOrder', related_name='borrowed_parts')
    part = models.ForeignKey('Part')
    qty_borrowed = models.PositiveIntegerField()

class ReturnOrderPart(models.Model):
    return_order = models.ForeignKey('ReturnOrder', related_name='returned_parts')
    part = models.ForeignKey('Part')
    qty_returned = models.PositiveIntegerField()

Note that the "quantity" field in each through table has a custom name (unfortunately): qty_borrowed or qty_returned. I'd like to be able to query the base table (so that I'm searching across all order types), and include an annotated field for each that sums these quantity fields:

# Not sure what I specify in the Sum() call here, given that the fields
# I'm interested in are different depending on the child's type.
qs = models.Order.objects.annotate(total_qty=Sum(???))

# For a single model, I would do something like:
qs = models.BorrowOrder.objects.annotate(
       total_qty=Sum('borrowed_parts__qty_borrowed'))

So I guess I have two related questions:

1. Can I annotate a child-model's data through a query on the parent model?
2. If so, can I conditionally specify the field to be annotated, given that the actual field name changes depending on the model in question?

This feels to me like a place where using When() and Case() might be helpful, but I'm not sure how I'd build the necessary logic.

CodePudding user response：

The problem is that, when you are querying the base model (in multi-table inheritance), it's hard to find out which subclass the object actually is. See How to know which is the child class of a model.

The query might be achievable in theory, with something like

SELECT
    CASE
        WHEN child1.base_ptr_id IS NOT NULL THEN ...
        WHEN child2.base_ptr_id IS NOT NULL THEN ...
    END AS ...
FROM base
    LEFT JOIN child1 ON child1.base_ptr_id = base.id
    LEFT JOIN child2 ON child2.base_ptr_id = base.id
    ...

but I don't know how to translate that in Django and I think it would be too much trouble to do it. It could be done, if not anything else using raw queries.

Another solution would be to add to the base class a field that specifies which actual subclass each object is; in that case, you'd need to make as many queries as there are subclasses and join them. I don't like this solution either. Update: After I slept on this I conclude that the most Django-like solution would be not to query the parent model in the first place; simply query the submodels and join the results. I would explore the third option below only if there were performance or other practical problems.

Another idea is to create a database view (with CREATE VIEW) based on the above SQL query and translate it into a Django model with managed = False, and query that one. Maybe this is somewhat cleaner than the other solutions, but it is a bit non-standard.