Steve Steve - 1 year ago 63
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,, COUNT( as gplayer
from (GroupPlayer LEFT OUTER JOIN Player ON = Player.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?


vsd vsd
Answer Source
from django.db.models import Count

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