INNER JOIN in django orm

November 25, 2021

I have two tables that only contain the same product ids and they didnt have foreigh keys. So question is about how i can filter them both in query by id.
In SQL i want to do something like this

SELECT Url FROM pricehistory p INNER JOIN product d ON p.ProductID = d.ProductID

Models look like this:

class Product(models.Model):
   ProductID = models.CharField(..)
   Price = models.FloatField(..)
   Url = models.CharField(..)
   ....

class PriceHistory(models.Model):
  ProductID = models.CharField(..)
  Price = models.FloatField(..)
  Retailer = models.CharField(..)
  Timestamp = models.DateTimeField(..)

>Solution :

You can filter with an Exists(…) subquery [Django-doc]:

from django.db.models import Exists, OuterRef

Product.objects.filter(
    Exists(PriceHistory.objects.filter(ProductID=OuterRef('ProductID')))
).values('Url')

This will not perform an INNER JOIN, but will make an WHERE EXISTS (…) subquery that will only retain the Urls of the Products which have a PriceHistory.

I would advise to turn the ProductID in a ForeignKey that refers to the ProductID of the Product, so:

class Product(models.Model):
    ProductID = models.CharField(unique=True)
    # …

class PriceHistory(models.Model):
    ProductID = models.ForeignKey(
        Product,
        to_field='ProductID',
        on_delete=models.CASCADE,
        db_column='ProductID'
    )
  # …

This will store the ProductID of the Product in the database but also guarantee referential integrity, and makes it more convenient to make queries with the Django ORM.