Referencing a non primary foreign key in Django ORM

September 21, 2023

How can I select entries in one table based on criteria in another table in Django’s ORM if the foreign key is not referencing a primary key but another column with unique constraint?
The corresponding SQL command would be:

SELECT TableMoon.name
FROM TableMoon JOIN TablePlanet
ON TableMoon.planet = TablePlanet.name
WHERE classification = 'inner_planet';

The expected answer would be: Moon, Phobos, Deimos

I tried to rewrite the SQL command from above in Django ORM with either ‘planet’ or ‘planet_id’ but I get an empty QuerySet everytime:

Tablemoon.objects.filter(planet__classification='inner_planet').values('name')
#Out: <QuerySet []>

Tablemoon.objects.filter(planet_id__classification='inner_planet').values('name')
#Out: <QuerySet []>

Background
I have a Django app that is connected to a legacy sqlite database. I am not allowed to modify the database scheme. The database looks like this:

TablePlanet  
------------------------------------  
planet_id |  name   | classification  
------------------------------------  
1         | Mercury | inner_planet  
2         | Venus   | inner_planet  
3         | Earth   | inner_planet  
4         | Mars    | inner_planet  
5         | Jupiter | outer_planet  
6         | Saturn  | outer_planet  
7         | Uranus  | outer_planet  
8         | Neptune | outer_planet  

TableMoon  
------------------------  
moon_id | planet  | name
------------------------  
1       | Earth   | Moon  
2       | Mars    | Phobos  
3       | Mars    | Deimos  
4       | Jupiter | Io  
5       | Jupiter | Europa  
6       | Jupiter | Ganymede

The column TablePlanet.name has a unique constraint. The column TableMoon.planet has a foreign key constraint that references TablePlanet.name.

This is the SQL code for the tables:

CREATE TABLE tablePlanet (
  planet_id INTEGER PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  classification TEXT
);

CREATE TABLE tableMoon (
  moon_id INTEGER PRIMARY KEY,
  planet TEXT,
  name TEXT,
  FOREIGN KEY (planet) REFERENCES tablePlanet(name)
);

My models.py file looks like this

from django.db import models


class Tableplanet(models.Model):
    planet_id = models.AutoField(primary_key=True, null=False)
    name = models.TextField(unique=True)
    classification = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'tablePlanet'


class Tablemoon(models.Model):
    moon_id = models.AutoField(primary_key=True, null=False)
    planet = models.ForeignKey('Tableplanet', models.DO_NOTHING,
                               db_column='planet', blank=True, null=True)
    name = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'tableMoon'

The first thing that puzzles me is Django’s naming of the planet-column which is ‘planet’ in one case and ‘planet_id’ in the other:

Tablemoon._meta.get_fields()
#Out: (<django.db.models.fields.AutoField: moon_id>,
#Out:  <django.db.models.fields.related.ForeignKey: planet>,
#Out:  <django.db.models.fields.TextField: name>)

Tablemoon.objects.all().values()[0]
#Out: {'moon_id': 1, 'planet_id': 'Earth', 'name': 'Moon'}

I am grateful for any advice on this!

>Solution :

Your ForeignKey needs to specify the correct to_field=… ^[Django-doc], since it does not reference the primary key of the Tableplanet:

class Tablemoon(models.Model):
    moon_id = models.AutoField(primary_key=True, null=False)
    planet = models.ForeignKey(
        'Tableplanet',
        models.DO_NOTHING,
        db_column='planet',
        blank=True,
        null=True,
        to_field='name',
    )
    name = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'tableMoon'

Note: Models normally have no Table prefix. A model is not a table, it is stored in a relational database as a table, but even then it has extra logic like validators, managers, etc.