[Answered ]-INNER JOIN in django orm

by

1👍

✅

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.

👤willeM_ Van Onsem

Leave a comment