Groupby using Django's ORM to get a dictionary of lists from the queryset of model with foreign-CodePudding

I have two models, Business and Employee:

from django.db import models

class Business(models.Model):
    name = models.CharField(max_length=150)
    # ...


class Employee(models.Model):
    business = models.ForeignKey(
        Business,
        related_name="employees",
        on_delete=models.CASCADE,
    )
    name = models.CharField(max_length=150)
    # ...

Here's a sample data:

Business.objects.create(name="first company")
Business.objects.create(name="second company")

Employee.objects.create(business_id=1, name="Karol")
Employee.objects.create(business_id=1, name="Kathrine")
Employee.objects.create(business_id=1, name="Justin")
Employee.objects.create(business_id=2, name="Valeria")
Employee.objects.create(business_id=2, name="Krista")

And I want to get a dictionary of lists, keys being the businesses and values being the list of employees. I can do so using prefetch_related on the Business model. A query like this:

businesses = Business.objects.prefetch_related("employees")

for b in businesses:
    print(b.name, '->', list(b.employees.values_list("name", flat=True)))

Which gives me this:

first company -> ['Karol', 'Kathrine', 'Justin']
second company -> ['Valeria', 'Krista']

And this is exactly what I want and I can construct my dictionary of lists. But the problem is that I only have access to the Employee model. Basically I only have a QuerySet of all Employees and I want to achieve the same result. I figured I could use select_related, because I do need the business objects, but this query:

Employee.objects.select_related("business")

Gives me this QuerySet:

<QuerySet [<Employee: Employee object (1)>, <Employee: Employee object (2)>, <Employee: Employee object (3)>, <Employee: Employee object (4)>, <Employee: Employee object (5)>]>

And I don't know how to group by business using Django's ORM from this QuerySet. How can I do that?

CodePudding user response：

You can use ArrayAgg annotation for this purpose. Try this:

from django.contrib.postgres.aggregates import ArrayAgg

Employee.objects.annotate(business=ArrayAgg('business__name'))

CodePudding user response：

Instead of trying to group, you can use a order by and then try grouping them over a simple loop. Something like:

query_set = Employee.objects.values('name', 'business').order_by('business')

For changing the structure a simple loop like this would work

from collections import defaultdict 
employees = defaultdict(list)
for result in Employee.objects.values('name', 'business').order_by('business'):
    users[result['business']].append(result['name'])

A similar example is giving here involving two foregin keys