Django filter on combination using F

I wrote the following query using the Django ORM:

fixtures = Fixture.objects.filter(league=league).filter((Q(predictions__value__gt=0.0) & Q(predictions__team=F("home"))) & (Q(predictions__value__gt=0.0) & Q(predictions__team=F("away"))))

With this I’m trying to filter out each fixture where the prediction__value for the home is greater than 0.0 and the prediction__value of the away is greater than 0.0.

Sadly my query is returning 0 records upon using .count() whereas it should return almost every record.

When I only use one part of the query; so either the home or the away part the query does return a certain amount of records.

The models:

class Fixture(models.Model):        
    home = models.ForeignKey(Team, on_delete=models.CASCADE, related_name="home")
    away = models.ForeignKey(Team, on_delete=models.CASCADE, related_name="away")

    league = models.ForeignKey(League, on_delete=models.CASCADE, blank=True)

class Prediction(models.Model):
    fixture = models.ForeignKey(Fixture, on_delete=models.CASCADE, related_name="modelx_predictions")
    team = models.ForeignKey(Team, on_delete=models.CASCADE)
    model = models.ForeignKey(Model, on_delete=models.CASCADE, related_name="modelx_predictions")

    value = models.FloatField(default=None, null=True, blank=True)

The value of league is irrelevant here.

Answer

You should filter with two outer JOIN’s, so:

fixtures = Fixture.objects.filter(league=league).filter(
    predictions__value__gt=0.0, predictions__team=F('home')
).filter(
    predictions__value__gt=0.0, predictions__team=F('away')
)

This will thus look for Predictions in the given fixture where one prediction is from the home team and its value is greater than 0, and a predication from the away team that is greater than 0.

Advertisement

Answer