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
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP