CODEWITHSUNDEEP
pythondjangopython-3.xdjango-modelsdjango-queryset
Di Zou
Di Zou

Reputation: 4609

How would I do these multiple joins as a Django queryset?

I have this query that joins multiple tables together:

select 
    p.player_id
    , d.player_data_1
    , l.year
    , l.league
    , s.stat_1
    , l.stat_1_league_average
from 
    stats s
inner join players p on p.player_id = s.player_id
left join player_data d on d.other_player_id = p.other_player_id
left join league_averages as l on l.year = s.year and l.league = s.year
where 
    p.player_id = 123

My models look like this:

class Stats(models.Model):
    player_id = models.ForeignKey(Player)
    stat_1 = models.IntegerField()
    year = models.IntegerField()
    league = models.IntegerField()


class Player(models.Model):
    player_id = models.IntegerField(primary_key=True)
    other_player_id = models.ForeignKey(PlayerData)

class PlayerData(models.Model):
    other_player_id = models.IntegerField(primary_key=True)
    player_data_1 = models.TextField()

class LeagueAverages(models.Model):
    year = models.IntegerField()
    league = models.IntegerField()
    stat_1_league_average = models.DecimalField()

I can do something like this:

Stats.objects.filter(player_id=123).select_related('player')

to do the first join. For the second join, I tried: 

Stats.objects.filter(player_id=123).select_related('player').select_related('player_data')

but I got this error:

django.core.exceptions.FieldError: Invalid field name(s) given in select_related: 'player_data'. Choices are: player

How would I do the third join considering that year and league aren't foreign keys in any of the tables? Thanks!

Upvotes: 1

Views: 13232

Answers (1)

har0ke
har0ke

Reputation: 98

select_related(*fields) Returns a QuerySet that will “follow” foreign-key relationships, [...]

According to the django documentation select_related follows foreign-key relationships. player_data is neighter a foreign key, nor even an field of Stats. If you'd want to INNER join PlayerData and Player you could follow its foreign-keys. In your case use the double-underscore to get to PlayerData:

Stats.objects.all()
    .select_related('player_id')
    .select_related('player_id__other_player_id')

As for joining LeagueAverages: There is not a way to join models without an appropriate foreign key, but to use raw sql. Have a look at a related question: Django JOIN query without foreign key. By using .raw(), your LEFT join (which by the way is also not that easy without using raw: Django Custom Left Outer Join) could also be taken care of.

Quick notes about your models:

  1. Each model by default has an automatically incrementing primary key that can be accessed via .id or .pk. So there is no need to add for example player_id
  2. A models.ForeignKey field references an object not it's id. Therefore it's more intuitive to rename for example player_id to player. If you name your field player django allows you automatically to access it's id via player_id

Upvotes: 6

Related Questions