Chaining queries through related objects

Question

Given a scenario like:

from django.db import models

class Player(models.Model):
    playername = models.CharField()

class Team(models):
    teamname = models.CharField()

class Members(models):
    player = models.ForeignKey(Player)
    team = models.ForeignKey(Team)

class Division(models):
    divname = models.CharField()  

class DivisionTeam(models):
    division = models.ForeignKey(Division)
    team = models.ForeignKey(Team)

How can I list all distinct players in division id = 5? I've look through the Q and F expressions, but I'm not looking for a complex set of or's. I am wondering if there is a way to chain a number of object1_set.object2_set.all() type structures, or do I set up a nested loops to build the object (to be passed to template via context) with eventual {% for p in players %} type loop in the template. The div id is passed through as a request variable.

Uh, `Player.objects.filter(members__team__divisionteam__divsion__pk=5)`? Or something like that? Your db structure is confusing :P — NightShadeQueen, Jul 22 '15 at 17:55
I'm assuming from your use of the word 'distinct' that for some reason you have multiple players with the same name and that's the crux of the question? Is there a reason you can't you just use [.distinct()](https://docs.djangoproject.com/en/1.8/ref/models/querysets/#django.db.models.query.QuerySet.distinct) ? Otherwise, I can't really tell what you're asking here. — foobarbecue, Jul 22 '15 at 17:58
@NightShadeQueen it is inherited (but I played a part), based on existing databases, and simplified for this question. Thanks, I'll give that a shot. — AMG, Jul 22 '15 at 17:59
@foobarbecue distinct because some other things I have tried list the same player multiple times as they appear on more than one team throughout the years. I've read about distinct() but can't get to the point to use it. — AMG, Jul 22 '15 at 18:02
Ok, I see now that Members is an intermediate model between Team and Player. That actually makes sense -- you might want to store data in Member like the dates for which the player was a member of a team. But you should not have any duplicate player objects in your database, so you should not need distinct() -- you just need to filter. — foobarbecue, Jul 22 '15 at 18:06

YPCrumble · Answer 1 · 2015-07-22T19:43:06.413

You would do best to simplify your models first. I don't think you need either of the Members or DivisionTeam models:

from django.db import models

class Division(models):
    divname = models.CharField()

class Team(models):
    teamname = models.CharField()
    division = models.ForeignKey(Division)

class Player(models.Model):
    playername = models.CharField()
    team = models.ManyToManyField(Team)

Then, your query is simply:

Player.objects.filter(team__division__pk=5)

In case you DO need intermediate models per comments, you should use a through relationship:

# Uses "Salary" as an example intermediate model.
class Player(models.Model):
    playername = models.CharField()
    team = models.ManyToManyField(Team, through="Salary")

class Salary(models.Model):
    value = models.IntegerField()
    player = models.ForeignKey(Player)
    team = models.ForeignKey(Team)

I assume the OP has removed some fields in order to make this readable. Like 'date_started_playing' and 'salary' and stuff which would go on the Member model so that players can be members of multiple teams. There's plenty of potentially good reasons to have these intermediate models. — foobarbecue, Jul 22 '15 at 18:08
@foobarbecue good point - adjusted my answer to account for this. — YPCrumble, Jul 22 '15 at 19:43

score 1 · Accepted Answer · answered Jul 22 '15 at 18:08

1

You could do:

players = Player.objects.filter(members__team__divisionteam__division_id=5).distinct()

Of course, as suggested in the other answer, your models could be simplified (by using ManyToMany rather than explicitly managing it)

answered Jul 22 '15 at 18:08

karthikr

97,368
26
197
188

thank you. As a side bar, if the order is off slightly, one can expect "Relation fields do not support nested lookups" error. The structure is difficult to change, as some parts are views to another database; and in some cases there is addition date effective type information in the intermediary tables. Still getting the hang of Django, but loving it. – AMG Jul 22 '15 at 18:32
Actually, those are called `through_tables` in django - where you can store additional information in the intermediary tables. Glad you are liking django - it is awesome ! – karthikr Jul 22 '15 at 18:34
reading through the docs, many to many may still be the way to go, but I do need extra attributes on the m2m table. something like http://stackoverflow.com/questions/4443190/djangos-manytomany-relationship-with-additional-fields will work for me and simplify the query slightly. Many thanks to all. – AMG Jul 22 '15 at 18:43

Chaining queries through related objects

2 Answers2