CODEWITHSUNDEEP
djangodjango-modelsforeign-keysdjango-querysetreverse
Matthieu
Matthieu

Reputation: 16417

Django, add data from reverse Foreign key query (foreign key join with filter)

I'm writing a small web game in Django. Each game has two players. To manage the games and participants, I have something like this (very simplified):

class GamesQuerySet(models.query.QuerySet):
    def with_player(self, user):
        """Games with |user| participating"""
        return self.filter(players__player=user)


class Game(models.Model):
    """All the games"""
    name = models.CharField(max_length=100, blank=True)

    objects = GamesQuerySet.as_manager()


class PlayerInGame(models.Model):
    """Players participating in games"""
    game = models.ForeignKey('Game', related_name='players')
    player = models.ForeignKey('auth.User')

    class Meta:
        unique_together = ('game', 'player')

So I'm able to easily query for games where the current player is taking part of:

Games.objects.with_player(user)

But to show in the list of games view, I would like to also include the name of the other player.

I've tried a few things and seems like this is possible to do by adding an .extra() and some raw SQL, but I'd like to try to avoid that as much as possible.

I've tried using annotate with something like this:

    def add_other_player(self, user):
        return self.annotate(other_player=PlayerInGame.objects.all(). \
                filter(~Q(player=user)))

But without much luck. Also looked into fetch_related and select_related. What would be the right way to do this?

Upvotes: 2

Views: 1876

Answers (2)

Matthieu
Matthieu

Reputation: 16417

OK, I think I found what I was looking for. Here is what I did (in a two step process):

from django.db.models import F

game_ids = Games.objects.with_player(user).values_list('id', flat=True)
with_opponents = Games.objects.filter(id__in=game_ids) \
                    .annotate(opponent=F('players__player__username')) \
                    .filter(~Q(opponent=user.username)

This way, I get a "field" in the result with the reverse foreign key relationship and I am able to filter on it too.

Not sure if there is a good way to avoid the two steps, but that works fine for me.

Upvotes: 0

Shang Wang
Shang Wang

Reputation: 25549

fetch_related and select_related are just optimization methods, the relationship is always there, these 2 methods just do the queries in one batch.

Since you are most likely going to loop through the queryset games, you could do:

games = Games.objects.with_player(user)
for game in games:
    players = game.players.values_list('player__first_name',
                                       'player__last_name')

The disadvantage of this is that you can specify as many fields as you want to display for a player, but it doesn't give you a whole player object. I think it actually makes more sense to create a ManyToMany relationship between Game and Player, because sounds like it's what you are doing, with PlayerInGame model as through model:

class PlayerInGame(models.Model):
    """Players participating in games"""
    game = models.ForeignKey('Game', related_name='players')
    player = models.ForeignKey('auth.User')

    class Meta:
        unique_together = ('game', 'player')

class Game(models.Model):
    """All the games"""
    name = models.CharField(max_length=100, blank=True)
    players = models.ManyToManyField('auth.User', through='PlayerInGame')

    objects = GamesQuerySet.as_manager()

Then to get the players of a game you could do:

players = game.players.all()

Django doc about m2m with through.

Note: through is mostly used when you have extra attributes for the m2m relationship. But if you don't have anything extra like in your code(maybe you were simplifying but just in case it's exactly like that), you could just use the ManyToManyField and get rid of PlayerInGame model all together, django would create the intermediate database table for you anyway.

Edit:

In template you do:

{% for game in games %}
  Current game is: {{ game.name }}
  The players in the game are:
  <ul>
  {% for player in game.players.all %}
    <li>{{ player.player.first_name }} {{ player.player.last_name }}</li>
  {% endfor %}
  </ul>
{% endfor %}

Upvotes: 1

Related Questions