Get data in model methods from multiple models - Django

Question

I'm quite new to Django (Python too) and learning it by doing.

I'm playing with Django admin site. I've created two models and successfully registered in admin. The data is nicely displayed for the fields given in list_display. I want an another table in admin, which displays data from both the tables by processing with some logic.

To get the another table based on the previous two models data, I can create a new model with only methods as mentioned in this question.

Now the problem is that, how can I get data from other model tables and how to return, so that it can be displayed in table in admin.

Here is my models.py:

from django.db import models


class GoodsItem(models.Model):
    name = models.CharField(max_length=255)
    size = models.DecimalField(max_digits=4, decimal_places=2)
    INCHES = 'IN'
    NUMBER = 'NUM'
    GOODS_ITEM_SIZE_UNITS = (
        (INCHES, 'Inches'),
        (NUMBER, '#'),
    )
    size_unit = models.CharField(
        max_length=4,
        choices=GOODS_ITEM_SIZE_UNITS,
        default=INCHES,
    )

    def __str__(self):
        if(self.size_unit == self.NUMBER):
            return "%s #%s" % (self.name, (self.size).normalize())
        else:
            return "%s %s\"" % (self.name, (self.size).normalize())


class FinishedGoodsItem(models.Model):
    date = models.DateField()
    goods_item = models.ForeignKey(GoodsItem, on_delete=models.CASCADE, related_name="finished_name")
    weight = models.DecimalField(max_digits=6, decimal_places=3)

    def __str__(self):
        return str(self.goods_item)


class SoldGoodsItem(models.Model):
    goods_item = models.ForeignKey(GoodsItem, on_delete=models.CASCADE, related_name="sold_name")
    date = models.DateField()
    weight = models.DecimalField(max_digits=6, decimal_places=3)

    def __str__(self):
        return self.goods_item

The third table in admin should show data like:

===============================
GoodsItem   |   Stock Available
===============================

Here GoodsItem is string representation of GoodsItem model and Stock available should be calculated as:

Sum of weight in FinishedGoodsItem for particular GoodsItem till last entry(i.e. last date of entry) - Sum of weight in SoldGoodsItem for particular GoodsItem till last entry(i.e. last date of entry)

I will implement the logic, if some kind personality tell me how to query data and return by different methods in model. If in any doubt, feel free to ask.

Answer 1

I understand you want one row per GoodsItem object. You can add the "stock available" column directly to the GoodsItem admin without creating a separate model, as Daniel Roseman says.

Example code (might not be 100% correct, but you get the point):

class GoodsItem(models.Model):
    # ...
    @property
    def stock_available(self):
        stock_finished = self.finished_name.aggregate(Sum('weight'))['weight__sum']
        stock_sold = self.sold_name.aggregate(Sum('weight'))[
            'weight__sum']
        return stock_finished - stock_sold


class GoodsItemAdmin(admin.ModelAdmin):
    # ...
    list_display = ('name', 'size', 'size_unit', 'stock_available')

See the Django docs on aggregation. Keep in mind that this will perform two extra queries per item shown in the list.

---

EDIT: The solution proposed below doesn't work due to the Django bug with aggregations and multiple tables described here, as highlighted in a comment. Use this answer that uses subqueries instead.

Another option if the previous solution was too slow due to many queries:

class GoodsItemAdmin(admin.ModelAdmin):
    list_display = ('name', 'size', 'size_unit', 'stock_available')

    def get_queryset(self, request):
        qs = super(GoodsItemAdmin, self).get_queryset(request)
        qs = qs.annotate(stock_available=Sum('finished_name__weight') - Sum('sold_name__weight'))
        return qs

Note that this last option only adds the feature to the admin, but that might be enough.