INNER JOIN in django orm

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


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(
  # …

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.

Leave a Reply