Django中的左连接查询具有针对同一表的多个连接

Not sure how to accomplish this in Django.

不知道如何在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:

想要查询一个玩家的所有信息，包括他们对他们的任何挑战。这个SQL工作原理:

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:

如果玩家3挑战了玩家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?

不知道如何在Django ORM ....有什么办法吗?

3 个解决方案

#1

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!

实际上，您应该稍微改变一下您的模型，因为使用Match作为中间表，从LadderPlayer到其本身存在多对多的关系。查看django关于这个主题的文档。然后您应该能够使用django的orm进行查询!还可以看看对称/不对称的多对多关系!

#2

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:

我做了更多的挖掘，在Django 1.2中，这可以通过查询管理器的“raw()”方法实现。这是我上面查询的代码:

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....

看起来像我需要....

#3

@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.

@lazerscience绝对是正确的。您应该调整模型，因为您正在建立一个事实上的多对多关系;这样做将允许您利用管理界面的更多特性等等。

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

此外，无论如何，没有必要去raw()，因为这完全可以通过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.

获取所有有关该球员的感兴趣的信息。注意，您需要从django.db中获得。模型导入Q来使用它。

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:

如果您希望得到与示例查询完全相同的信息，我认为最好将查询拆分为单独的查询，以便获得challenger & challengee列表——例如:

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).

将获得感兴趣的玩家的两个相关查询集(w/ 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.

无论如何，您应该通过Django ORM执行查询，而不是在本例中使用raw()。

#1

#2