How to do an inner join statement in django?

Question

A little confused on how to do join's in Django.

For example I have the following 3 tables:

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

I want to get the name of the Pokemon from basepokemontest, the condition and multiplier from weakresistimmune and the type name from pokemontypestest.

I can easily do that in SQL via:

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

I read some docs and tried to chain filters, but doesn't seem to be working.

Is chaining filters the correct method to do a join in django or am i missing something?

*** EDIT *** Hi all, sorry was a little busy yesterday. My models look like this, minus the methods:

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') #these abilities (ability1, ability2 and hidden_ability will need to be moved to a many-to-many table)
    ability2 = models.CharField(max_length=25, default='n/a') #where a pokemon id from basepokemon table is linked to a ability id
    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):
    #in the typing field, 1 is weakness
    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)

I tried the following to achieve the SQL query I did in postgresql, but with django:

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

It returns a query list where I can get the result I want:

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

Now I'm a bit confused if I should do it the way I have to achieve my result, or if I was supposed to use prefetch_related on the many-to-many field in BasePokemonTest called weakness_resistance_immune = models.ManyToManyField(PokemonTypesTest, through='WeakResistImmune')

Any thoughts on if I did it correct?

*** EDIT 2 ***

Found and set up this django debug tool, that shows me the queries. Looks like the select_related does what I want in this case while only sending 1 query!

The following is what came back.

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

Does this answer your question? [how-to-make-an-inner-join-in-django](https://stackoverflow.com/questions/48128714/how-to-make-an-inner-join-in-django) — ilyasbbu, Oct 28 '22 at 03:44
@ilyasbbu, i think it did, not sure if I went about it the right way though — jarjarbinks99, Oct 28 '22 at 13:53

How to do an inner join statement in django?

0 Answers0