Values/Values List with model reference

Using Django Using the ORM
1

I have a model name Transaction with a group field (foreign key), product field (foreign key) and a quantity field (integer).

I want to sum the quantity of each product of each group. The Custom QuerySet method below works, but I would like to return the product instance instead of a string of it’s name to be able to in the template access other fields and properties without defining them here. Is it possible in any way?

class TransactionQuerySet(models.QuerySet):
    def group_quantity_dict(self):
        return (self
                .values('group__name', 'product__name')
                .annotate(qty=Sum('quantity')
2

Yes, remove the values clause. Allow Django to return the object instances to you.

3

But in that case I do not get the “group by” aggregation.

4

Please post the models involved.

5

A bit simplified for clarity.

class Transaction(models.Model):

    class Meta:
        ordering = ['project', 'group', 'product']
    objects = TransactionManager.from_queryset(TransactionQuerySet)()
    product = models.ForeignKey( 'Product', on_delete=models.CASCADE, related_name='transactions')
    quantity = models.IntegerField()
    group = models.ForeignKey(Group, on_delete=models.SET_NULL,
                              null=True, blank=True, related_name='transactions')

class Group(models.Model):
    name = models.CharField(max_length=200, blank=True)

class Product(models.Model):

    class Meta:
        ordering = ['type', 'name']

    name = models.CharField(max_length=200)
    description = models.CharField(max_length=400, blank=True, null=True)
    day_rate = models.DecimalField(
        max_digits=18, decimal_places=6, null=True, blank=True)
    type = models.ForeignKey(
        ProductType, on_delete=models.SET_NULL, null=True, related_name='products')
6

Ok, a “group by” aggregation in SQL doesn’t return instances of tables in the database, it’s returning a result set synthesized from the query results.

What this means is that you don’t get back model instances from the ORM. You’re going to need to make an extra query to retrieve those referenced models.

7

The result above, gets me the sum I want. If I were to do this in a Subquery and annotate this sum to each row of the query set, I would have multiple lines of each product in each group. I can not use distinct to remove the “extra” rows, unless I switch to PostgreSQL db, since they are not duplicates, they have unique id and probably different quantities. Without moving to PostgreSQL, .distinct(product, group) in PostgreSQL, is there something more generic that I could do that achieves the same thing?