如何在Django中实现内连接查询？附实战验证

背景说明 #

现有三张数据库表：

battlefield_weakresistimmune;
 id | weakResistanceImmune | multiplier | pokemon_id | typing_id
----+----------------------+------------+------------+-----------

battlefield_basepokemontest;
 id |     name      | base_hp | base_atk | base_def | base_spatk | base_spdef | base_speed |  ability1  | ability2  | hidden_ability | pokemon_id | type1_id | type2_id | type3_id
----+---------------+---------+----------+----------+------------+------------+------------+------------+-----------+----------------+------------+----------+----------+----------
  1 | Bulbasaur     |      45 |       49 |       49 |         65 |         65 |         45 | Overgrowth | n/a       | Chlorophyll    |          1 |       12 |        4 |
  2 | Ivysaur       |      60 |       62 |       63 |         80 |         80 |         60 | Overgrowth | n/a       | Chlorophyll    |          2 |       12 |        4 |


battlefield_pokemontypestest;
 id |   name
----+----------
  1 | Normal
  2 | Fighting
  3 | Flying
  4 | Poison

目标是获取宝可梦名称、克制关系与倍数、属性名称，已用SQL实现内连接：

SELECT poke.name, con.weak_resist_immune, type.name, con.multiplier FROM battlefield_basepokemontest poke INNER JOIN battlefield_weakresistimmune con ON poke.id = con.pokemon_id INNER JOIN battlefield_pokemontypestest type ON type.id = con.typing_id;

查询结果：

name    | weak_resist_immune |  name  | multiplier
-----------+--------------------+--------+------------
 Bulbasaur | Weak               | Flying |       2.00

Django 模型代码 #

class PokemonTypesTest(models.Model):
    name = models.CharField(max_length=25, unique=True)

    def __str__(self):
        return self.name 

class BasePokemonTest(models.Model):
    name = models.CharField(max_length=50, unique=True)
    base_hp = models.IntegerField()
    base_atk = models.IntegerField()
    base_def = models.IntegerField()
    base_spatk = models.IntegerField()
    base_spdef = models.IntegerField()
    base_speed = models.IntegerField()
    type1 = models.ForeignKey(PokemonTypesTest, null=True, on_delete=models.SET_NULL, related_name='pokemontype1')
    type2 =  models.ForeignKey(PokemonTypesTest, null=True, on_delete=models.SET_NULL, related_name='pokemontype2')
    type3 =  models.ForeignKey(PokemonTypesTest, null=True, on_delete=models.SET_NULL, related_name='pokemontype3')
    ability1 = models.CharField(max_length=25, default='n/a') # 后续需将这些能力字段移至多对多表
    ability2 = models.CharField(max_length=25, default='n/a')
    hidden_ability = models.CharField(max_length=40, default='n/a')
    pokemon_id = models.IntegerField(default=0)

    weakness_resistance_immune = models.ManyToManyField(PokemonTypesTest, through='WeakResistImmune')

class WeakResistImmune(models.Model):
    # typing字段标记克制类型（1代表弱点）
    pokemon = models.ForeignKey(BasePokemonTest, on_delete=models.CASCADE)
    weak_resist_immune = models.CharField(max_length=25)
    typing = models.ForeignKey(PokemonTypesTest, on_delete=models.CASCADE)
    multiplier = models.DecimalField(max_digits=5, decimal_places=2)

尝试的实现与疑问 #

用以下代码实现查询，得到了预期结果：

dataSet = WeakResistImmune.objects.select_related('pokemon','typing').all()

输出结果：

>>> for i in dataSet:
...     print(str(i.pokemon.name) + ' is ' + str(i.weak_resist_immune) + ' to ' + i.typing.name + ' by a multiplier of ' + str(i.multiplier))
...
Bulbasaur is Weak to Flying by a multiplier of 2.00
Bulbasaur is Weak to Fire by a multiplier of 2.00
Bulbasaur is Weak to Psychic by a multiplier of 2.00
Bulbasaur is Weak to Ice by a multiplier of 2.00
Bulbasaur is Resist to Fighting by a multiplier of 0.50
Bulbasaur is Resist to Water by a multiplier of 0.50
Bulbasaur is Resist to Grass by a multiplier of 0.25
Bulbasaur is Resist to Electric by a multiplier of 0.50
Bulbasaur is Resist to Fairy by a multiplier of 0.50

通过调试工具看到仅发送1条内连接SQL：

SELECT ••• FROM "battlefield_weakresistimmune" INNER JOIN "battlefield_basepokemontest" ON ("battlefield_weakresistimmune"."pokemon_id" = "battlefield_basepokemontest"."id") INNER JOIN "battlefield_pokemontypestest" ON ("battlefield_weakresistimmune"."typing_id" = "battlefield_pokemontypestest"."id") LIMIT 21

疑问：

1. 链式过滤是Django实现连接的正确方式吗？
2. 当前用select_related的实现是否正确？是否应该用prefetch_related处理BasePokemonTest中的多对多字段weakness_resistance_immune？

你的实现是完全正确的，针对疑问的具体说明如下：

关于链式过滤与连接实现 #

链式过滤（如filter()链式调用）主要用于添加查询条件，而你当前的需求是关联多表获取数据。从WeakResistImmune出发通过外键关联BasePokemonTest和PokemonTypesTest，用select_related是精准且高效的选择，和你手写的SQL逻辑完全匹配。

select_related vs prefetch_related #

• select_related适用于一对一/多对一的外键关联，它会生成一条包含JOIN的SQL语句，一次性拉取所有关联数据，避免N+1查询问题。你的场景中，WeakResistImmune与pokemon、typing都是多对一关系，用select_related完全匹配，调试工具显示的单条JOIN SQL也证明了这一点，性能最优。
• prefetch_related适用于多对多/多对一反向的关联，它会先查主表数据，再批量查关联表数据，最后在Python层面做关联拼接。你当前的查询是从WeakResistImmune（中间表）出发，不需要用到BasePokemonTest的多对多字段weakness_resistance_immune，所以完全不需要用prefetch_related。如果是从BasePokemonTest出发，要获取它所有的克制关系，那才需要用prefetch_related('weakness_resistance_immune')来批量拉取关联数据。

额外优化建议 #

如果只需要特定字段（比如宝可梦名称、克制关系、属性名、倍数），可以用values()进一步减少数据传输：

dataSet = WeakResistImmune.objects.select_related('pokemon','typing').values(
    'pokemon__name', 'weak_resist_immune', 'typing__name', 'multiplier'
)

这样查询结果会是字典列表，直接拿到所需字段，无需再访问模型实例属性。

内容的提问来源于stack exchange，提问作者jarjarbinks99