CODEWITHSUNDEEP
pythondjangodjango-orm
noname12
noname12

Reputation: 61

Django: Queryset aggregating ManyToMany values

I have MyUser model with ForeignKey and ManyToMany related fields city and skills:

accounts/models.py

class MyUser(AbstractBaseUser):
    email = models.EmailField()
    city = models.ForeignKey('jobs.City')
    skills = models.ManyToManyField('jobs.Skill')

jobs/models.py

class Skill(models.Model):
    name = models.CharField()
class City(models.Model):
    name = models.CharField()

I need to make a queryset that would return data in this format:

{'email': '[email protected]', 'city': 'London', 'skills': ['Python', 'Java']},
{'email': '[email protected]', 'city': 'Berlin', 'skills': ['JavaScript', 'C#', 'Python']},
...

MyUser.objects.values('email', 'city__name', 'skills__name') returns all data I need but ManytoMany values returned separately duplicating other entries:

{'email': '[email protected]', 'city': 'London', 'skills': 'Python'},
{'email': '[email protected]', 'city': 'London', 'skills': 'Java'},
{'email': '[email protected]', 'city': 'Berlin', 'skills': 'JavaScript'},
{'email': '[email protected]', 'city': 'Berlin', 'skills': 'C#'},
{'email': '[email protected]', 'city': 'Berlin', 'skills': 'Python'},
...

So how can I make a queryset aggregating ManyToMany values into one set?

Upvotes: 2

Views: 1202

Answers (1)

marcelob33
marcelob33

Reputation: 66

https://docs.djangoproject.com/en/3.1/ref/models/querysets/#values

Django documentation warns about this case

Warning: Because ManyToManyField attributes and reverse relations can have multiple related rows, including these can have a multiplier effect on the size of your result set. This will be especially pronounced if you include multiple such fields in your values() query, in which case all possible combinations will be returned.

I've searched for alternatives, like using a serializer but I think the only way to do it is looping through your querysets and create your own list of dictionaries with the data that you want.

I suggest to add a custom property to your model

class MyUser(models.Model):
    email = models.EmailField()
    city = models.ForeignKey(City, on_delete=models.PROTECT)
    skills = models.ManyToManyField(Skill)

    @property
    def get_skills(self):
        skills = self.skills.all()
        skills_list = []
        for skill in skills:
            skills_list.append(skill.name)

        return skills_list

Now it's easier to get a list of names for the skills attribute

users_list = []
for my_user in MyUser.objects.all():
    users_list.append({
        'email': my_user.email,
        'city': my_user.city.name,
        'skills': my_user.get_skills,
    })

Maybe it's not the solution you were looking for and maybe it's not the best way to do it, anyway I hope this answer would help you.

Upvotes: 1

Related Questions