TransWikia.com

Django: annotate() + distinct(fields) is not implemented

Stack Overflow Asked by popcorn on December 6, 2020

I have following models:

class Village(models.Model):
    packages_required = models.IntegerField()
    .
    .

class PackageSent(models.Model):
    village = models.ForeignKey('myapp.Village')
    package_count = models.IntegerField()
    sending_user = models.ForeignKey('myapp.Users')
    sending_account_user = models.ForeignKey('myapp.Users')
    .
    .

And I need to select all Villages along with their PackageSent models not just specific values I can group by. I built the following query:

ps_query = PackageSent.objects.filter(
                                         filter_logic
                                     ).annotate(
                                         total_account_sent=Sum("package_count"), 
                                         sending_users=ArrayAgg("sender_user_id")
                                     ).distinct(
                                        "sending_account_user_id"
                                     )

Village.objects.filter(
        filter_logic
    ).order_by(
        order_by_logic
    ).annotate(
        packages_missing=F("packages_required") - Sum("packagesent__package_count"),
        users_involved=Count("packagesent__sending_account_user", distinct=True)
    ).prefetch_related(
        models.Prefetch(
            "packagesent_set", 
             queryset=ps_query, to_attr="packages_sent"
        )
    )

However the PackageSent query raises annotate() + distinct(fields) is not implemented error.

The query I need for PackageSent model in MySQL syntax:

 SELECT 
    *,
    SUM(package_count) AS `total_account_sent`,
    GROUP_CONCAT(sending_user) AS `sending_users`
 FROM
    `myapp_packagesent` ps
 LEFT JOIN 
    `myapp_village` v ON v.id = ps.village_id
 GROUP BY 
    `sending_account_user`, 
    `village_id`
 ORDER BY
    `total_account_sent` DESC

I would like to do this in a template:

{% for village in object_list %}
    .
    .
    .
    {% for package_sent in village.packages_sent %}
        .
        .
        .
    {% endfor %}
{% endfor %}

How can I achieve the results I want?

Technologies used:
Django 3.0
PostgreSQL

One Answer

You should use .values, to group by what you want, and the you will not have to use distinct:

ps_query = PackageSent.objects.filter(
                                     filter_logic
                                 ).values(
                                     "sending_account_user_id", "village_id"
                                 ).annotate(
                                     total_account_sent=Sum("package_count"), 
                                     sending_users=ArrayAgg("sender_user_id")
                                 )

Answered by A K on December 6, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP