Join and get queryset accordring to many to many field

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

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

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

suppose this my table data in database

{'email': 'some@email.com',  'skills': ['Python', 'Java']},
{'email': 'another@email.com',  'skills': ['JavaScript', 'C#', 'Python']}

>>> MyUser.objects.all().count()

output is 2 but iwant

MyUser.objects. ..........

answer to 5 my like following data

{'email': 'some@email.com', 'city': 'London', 'skills': 'Python'},
{'email': 'some@email.com', 'city': 'London', 'skills': 'Java'},
{'email': 'another@email.com', 'city': 'Berlin', 'skills': 'JavaScript'},
{'email': 'another@email.com', 'city': 'Berlin', 'skills': 'C#'},
{'email': 'another@email.com', 'city': 'Berlin', 'skills': 'Python'},

You can use annoate(), with Count aggregation and values() to get the desired output so:

from django.db.models import Count

all_users = MyUser.objects.annotate(num_skills=Count('skills')).values('email', 'num_skills')

data = []
for single_user in all_users:
    email = single_user['email']
    num_skills = single_user['num_skills']
    skills = MyUser.objects.get(email=email).skills.all()
    for skill in skills:
        data.append({'email': email, 'city': 'London', 'skills': skill.name})

print(data)

Obviously, not a good solution as city value is hardcoded to London and Berlin for the first and second user respectively.

You can simply query like this:

MyUser.objects.values('email', 'city', 'skills__name')

You can also rename the skills__name field like this:

MyUser.objects.annotate(skill=F('skills__name')).values('email', 'city', 'skill')
Back to Top