Steve Steve - 5 months ago 19
SQL Question

How manage outer join, group_by and count with Django

Using Django I have the follow models:

class Player(models.Model):
name = models.CharField(max_length=64)
description = models.CharField(max_length=128)
groupPlayer = models.ForeignKey(GroupPlayer)


class GroupPlayer(models.Model):
description = models.CharField(max_length=128)
name = models.CharField(max_length=32)


Every player is linked to a group. Some group has no player. My aim is to translate the follow query in Django (which include LEFT OUTER JOIN and Group by):

select GroupPlayer.description, GroupPlayer.name, COUNT(Player.name) as gplayer
from (GroupPlayer LEFT OUTER JOIN Player ON GroupPlayer.id = Player.groupPlayer_id)
GROUP BY GroupPlayer.id


So far I have created 3 queryset, but I cannot put them together in order to get the result I wish:

queryset_player = Player.objects.values('groupPlayer_id').annotate(total=Count('groupPlayer__id'))
queryset_group = GroupPlayer.objects.select_retated(queryset_player)
queryset_group2 = GroupPlayer.objects.all().prefetch_related('player_set')


Anyone can help with that LEFT OUTER JOIN, Group by and count?

Thanks

vsd vsd
Answer
from django.db.models import Count

groups = GroupPlayer.objects.annotate(num_players=Count('player'))
groups[0].num_players

Aggragation