CODEWITHSUNDEEP
sqldjangojoin
chacmool
chacmool

Reputation: 1449

Left join query in Django with multiple joins against same table

Not sure how to accomplish this in Django.

Models:

class LadderPlayer(models.Model):
  player = models.ForeignKey(User, unique=True)
  position = models.IntegerField(unique=True)

class Match(models.Model):
  date = models.DateTimeField() 
  challenger = models.ForeignKey(LadderPlayer)
  challengee = models.ForeignKey(LadderPlayer)

Would like to query to get all info about a player in one shot, including any challenges they have issued or challenges against them. This SQL works:

select lp.position,
lp.player_id,
sc1.challengee_id challenging,
sc2.challenger_id challenged_by
from ladderplayer lp left join challenge sc1 on lp.player_id = sc1.challenger_id
left join challenge sc2 on lp.player_id = sc2.challengee_id

Which returns something like this, if player 3 has challenged player 2:

position    player_id   challenging  challenged_by
----------  ----------  -----------  -------------
1           1
2           2                        3
3           3           2

No idea how to do in Django ORM....any way to do this?

Upvotes: 0

Views: 3522

Answers (3)

desfido
desfido

Reputation: 787

@lazerscience is absolutely correct. You should tweak your models, since you are setting up a de facto many-to-many relationship; doing so will allow you to leverage more features of the admin interface & so forth.

Additionally, regardless, there is no need to go to raw(), since this can be done entirely via normal usage of the Django ORM.

Something like:

class LadderPlayer(models.Model):
    player = models.ForeignKey(User, unique=True)
    position = models.IntegerField(unique=True)
    challenges = models.ManyToManyField("self", symmetrical=False, through='Match')

class Match(models.Model):
    date = models.DateTimeField() 
    challenger = models.ForeignKey(LadderPlayer)
    challengee = models.ForeignKey(LadderPlayer)

should be all you need to change in the models. You then should be able to do a query like

player_of_interest = LadderPlayer.objects.filter(pk=some_id)
matches_of_interest = \
        Match.objects.filter(Q(challenger__pk=some_id)|Q(challengee__pk=some_id))

to get all the information of interest about the player in question. Note that you'll need to have from django.db.models import Q to use that.

If you want exactly the same info you're presenting with your example query, I believe it'd be easiest to split the queries into separate ones for getting the challenger & challengee lists -- for example, something like:

challengers = LadderPlayer.objects.filter(challenges__challengee__pk=poi_id)
challenged_by = LadderPlayer.objects.filter(challenges__challenger__pk=poi_id)

will get the two relevant query sets for the player of interest (w/ a primary key of poi_id).

If there's some particular reason you don't want the de facto many-to-many relationship to become a de jure one, you can change those to something along the lines of

challenger = LadderPlayer.objects.filter(match__challengee__pk=poi_id)
challenged_by = LadderPlayer.objects.filter(match__challenger_pk=poi_id)

So the suggestion for the model change is merely to help leverage existing tools, and to make explicit a relationship which you are currently having occur implicitly.

Based on how you want use it, you might want to do something like

pl_tuple = ()
for p in LadderPlayer.objects.all():
    challengers = LadderPlayer.objects.filter(challenges__challengee__pk=p.id)
    challenged_by = LadderPlayer.objects.filter(challenges__challenger__pk=p.id)
    pl_tuple += (p.id, p.position, challengers, challenged_by)
context_dict['ladder_players'] = pl_tuple

in your view to prepare the data for your template.

Regardless, you should probably be doing your query through the Django ORM instead of using raw() in this case.

Upvotes: 1

chacmool
chacmool

Reputation: 1449

Well, I did more digging and it looks like in Django 1.2 this is doable via the "raw()" method on the Query Manager thing. So this is the code using my query above:

ladder_players = LadderPlayer.objects.raw("""select lp.id, lp.position,lp.player_id,
sc1.challengee_id challenging,
sc2.challenger_id challenged_by
from ladderplayer lp left join challenge sc1 on lp.player_id = sc1.challenger_id
left join challenge sc2 on lp.player_id = sc2.challengee_id order by position""")

And in the template, you can refer to the "calculated" join fields:

{% for p in ladder_players %}
{{p.challenging}} {{p.challenged_by}}
...

etc.

Seems to work as I needed....

Upvotes: 1

Bernhard Vallant
Bernhard Vallant

Reputation: 50786

Actually you should probably change your models a bit, since there's a many-to-many relation from LadderPlayer to itself using Match as an intermediate table. Check out django's documentation on this topic. Then you should be able to make the queries you want using django's orm! Also have a look at symmetrical/asymmetrical many-to-many relationships!

Upvotes: 2

Related Questions