1

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!

Di Zou
  • 4,469
  • 13
  • 59
  • 88

1 Answers1

6

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
har0ke
  • 98
  • 1
  • 8
  • Awesome, thanks! The double-underscore for `PlayerData` worked. Too bad about my third join. As for the auto incrementing primary key, I'm not using that because the data in these tables is from a third-party source. – Di Zou Apr 26 '18 at 21:54
  • Glad i could help a little. You could still set the id manually at creation, even though it is auto-incrementing. But if you choose to rename it on purpose, I don't think there is a problem with that. I only think it would be more consistent in this particular case. – har0ke Apr 27 '18 at 06:15