Build query to join data from two models without primary or foreign key

Using Django Using the ORM
1

I’m learning Django, so far so good, however I am stuck with the get_query(self) function.

I have two models in django no foreign in tables:

class Airport(models.Model):
   ID = models.AutoField()
   Name = models.CharField()
   City = models.CharField()
   ICAO = models.CharField()

class Airport_Frequency(models.Model):
   ICAO = models.CharField())
   ATC_Type = models.CharField()
   Frequency_MHz = models.DecimalField()

Now I want to create a listview but I want to join table fields Airport.name and Airport_Frequency.ICAO to show in frequency list - SQL statement:

SELECT DISTINCT a.ICAO, b.ICAO, b.Name FROM airport as b, airport_frequency as a WHERE a.ICAO = b.ICAO

View:

    class Airport_FrequencyListView(ListView):
       model = Airport_Frequency

How to refer in the Airport_FrequencyListViewview above to another model? Is it possibe, to do that without adding airport = models.ForeignKey(Airport, null=True) field to Airport_Frequency(models.Model) class.

Can someone give me a hint ?

2

Django does not directly support joins of models where there are no direct key relationships.

Is this relationship a many-to-one? If so, in which direction? Or is this a one-to-one?

Assuming it’s a Many-to-one, with the many-side being Airport_Frequency, you’ve got a couple of choices:

  • Create the foreign key field as you’ve described
  • Assuming there’s only one entry in Airport for each ICAO, you could add the unique constraint to the ICAO field, then define the ICAO field in the Airport_Frequency table as a foreign key to that field.
  • You can add a second query to filter Airport_Frequency for each Airport

One item I’ll note - it doesn’t make sense to reference a CBV in a model. A view takes an HTTP request and returns an HTTP response - usually either HTML or JSON. You might want to become more familiar with the sequence of events as they occur in a CBV. For that I suggest becoming more familiar with the Classy Class Based View site and the CBV diagrams pages.

1 Like
3

Thanks now it is clear that not all sql statements can be used in Django.
This is many to one airport_frequency table has many couple the same icao rowa with different frequency while airport table has only one. So solution to add foreign key to airport_frequency table is the best. Also i will add unique const to airport table.

But can you give me a tip how to call airport table in airport_frequenciesm view. Is it with “__”?