英文:
Join and get queryset accordring to many to many field
问题
我有一个MyUser模型，其中包含与city和skills相关的ForeignKey和ManyToMany字段：
class MyUser(AbstractBaseUser):
email = models.EmailField()
skills = models.ManyToManyField('jobs.Skill')
假设这是数据库中的表数据：
{'email': 'some@email.com', 'skills': ['Python', 'Java']},
{'email': 'another@email.com', 'skills': ['JavaScript', 'C#', 'Python']}
如果运行以下代码：
MyUser.objects.all().count()
输出是2，但我想要以下数据：
MyUser.objects. ..........
回答第5个问题，数据如下：
{'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'},
请注意，我没有翻译代码部分，只提供了翻译后的文本。
英文:
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 I want:
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'},
答案1
得分: 0
您可以使用annotate()，结合Count聚合函数和values()来获得所需的输出，如下所示：
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)
显然，这不是一个好的解决方案，因为city的值硬编码为分别为第一个和第二个用户的London和Berlin。
英文:
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.
答案2
得分: 0
你可以简单地进行如下查询：
MyUser.objects.values('email', 'city', 'skills__name')
你也可以像这样重命名 skills__name 字段：
MyUser.objects.annotate(skill=F('skills__name')).values('email', 'city', 'skill')
英文:
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')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论