Queryset filter with three-deep nested models (multiple one-to-many relationships)
<p>I'm trying to figure out how to filter some queries in Django with my models setup something like this:</p>

<pre><code>class Team(models.Model):
    name = models.CharField()

class TeamPosition(models.Model):
    description = models.CharField()
    team = models.ForeignKey(Team)

class Player(models.Model):
    teamposition = models.ForeignKey(TeamPosition)
    team = models.ForeignKey(Team)
    joined_date = models.DateField()
    left_date = models.DateField(blank=True, null=True)
    person = models.ForeignKey(Person)

class Person(models.Model):
    name = models.CharField()
</code></pre>

<p>I'd like to find querysets that answer these questions (moved below for clarity):</p>

<p>If I start at the TeamPosition objects it is much easier to figure out (but doesn't give me a queryset of Teams).</p>

<p>Sample Data set:</p>

<p>Object set 1:</p>

<pre><code>Team(name="Apples")
    TeamPosition(team="Apples", description="Forward")
        Player(team="Apples", teamposition="Forward", joined_date="2014-01-01", left_date=null, person="Bob")
    TeamPosition(team="Apples", description="Defense")
        Player(team="Apples", teamposition="Defense", joined_date="2014-01-01", left_date=2015-01-01, person="John")
        Player(team="Apples", teamposition="Defense", joined_date="2015-01-01", left_date=2017-01-01, person="Paul")
    TeamPosition(team="Apples", description="Goalie")
        Player(team="Apples", teamposition="Goalie", joined_date="2014-01-01", left_date=2015-01-01, person="Jane")
</code></pre>

<p>Object set 2:</p>

<pre><code>Team(name="Pears")
    TeamPosition(team="Pears", description="Forward")
        Player(team="Pears", teamposition="Forward", joined_date="2014-01-01", left_date=null, person="Carol")
    TeamPosition(team="Pears", description="Defense")
        Player(team="Pears", teamposition="Defense", joined_date="2015-01-01", left_date=2017-01-01, person="Bill")
    TeamPosition(team="Pears", description="Goalie")
        Player(team="Pears", teamposition="Goalie", joined_date="2014-01-01", left_date=null, person="Susan")
</code></pre>

<p>Object set 3:</p>

<pre><code>Team(name="Oranges")
    TeamPosition(team="Oranges", description="Forward")
    TeamPosition(team="Oranges", description="Forward")
    TeamPosition(team="Oranges", description="Goalie")
</code></pre>

<p>Object set 4:</p>

<pre><code>Team(name="Bananas")
    TeamPosition(team="Bananas", description="Forward")
        Player(team="Bananas", teamposition="Forward", joined_date="2014-01-01", left_date=null, person="Joe")
    TeamPosition(team="Bananas", description="Defense")
        Player(team="Bananas", teamposition="Defense", joined_date="2015-01-01", left_date=2017-01-01, person="Angela")
    TeamPosition(team="Bananas", description="Goalie")
        Player(team="Bananas", teamposition="Goalie", joined_date="2014-01-01", left_date="2016-09-30", person="Kelly")
</code></pre>

<p>So based on those objects, I'd expect the following results:</p>

<ul>
<li><p>Which Teams have available TeamPositions? (available meaning a TeamPosition without a current Player)</p>

<p><code>Queryset should return Object 1 (Apples) and Object 3 (Oranges)</code></p></li>
<li><p>Which Teams have all TeamPositions full with current Players? (the opposite of above) (current meaning a Player that has either no left_date or a left_date in the future)</p>

<p><code>Queryset should return Object 2 (Pears) and Object 4 (Bananas)</code></p></li>
<li><p>Which Teams will have an empty TeamPosition in 30 days?</p>

<p><code>Queryset should return Object 4 (Bananas)</code></p></li>
</ul>

<p>Hopefully that makes it more clear.</p>

<p>Note: Previously had a car example (hence first response), but it seemed unclear so created a better example</p>

<p><strike>One question before answering: Is the line <code>carmodel = models.ForeignKey(Model)</code> ok? or should it be <code>ForeignKey(CarModel)</code> instead?</strike></p>

<p>Try this query (this should give you all CarCompany objects whose CarModel's ModelYear's <code>last_availability</code> date is in the future:</p>

<pre><code>from datetime import datetime
CarCompany.objects.filter(carmodel__modelyear__last_availability__gte=datetime.now())
</code></pre>

<p>In order to check both whether <code>last_availability</code> is in the future or blank/null I would use <a href="https://docs.djangoproject.com/en/dev/topics/db/queries/#complex-lookups-with-q-objects" rel="nofollow">Q objects</a>:</p>

<pre><code>from django.db.models import Q
CarCompany.objects.filter(Q(carmodel__modelyear__last_availability__gte=datetime.now()) |
                          Q(carmodel__modelyear__last_availability__isnull=True))
</code></pre>

<p>For your second example, I can't think but in the same query (It answers your questions <code>Which Teams have open TeamPositions?</code> and <code>Which Teams will have an empty TeamPosition in 30 days</code>)</p>

<p>But I'm not sure what do you mean by <code>Which Teams have no TeamPositions without current Players?</code> If you could explain it a bit...</p>

<pre><code>Team.objects.filter(Q(teamposition__player__left_date__gte=datetime.now()) |
                    Q(teamposition__player__left_date__isnull=True) |
</code></pre>

python django database