How to join 2 tables in django ORM

Question

I have 2 Tables. TableA and TableB.

id is the primary key in TableB and item_id is the foreign key in TableA.

My task is to find all the items from TableB where the id should match the item_ids.

I am able to get all the items with the below queries. But I'd like to understand how to apply joins on the both tables using select_related to get the same result.

item_ids = list(TableA.objects.filter(is_active=True).\
                               distinct().\
                               values_list('item_id', flat=True))

items = TableB.objects.filter(Q(id__in=item_ids) & \
                              Q(display_name__isnull=False) & \
                              Q(name__isnull=False)).\
                       values("name", "display_name")

model -->> TableA

class TableA(models.Model):
    item = models.ForeignKey(TableB, on_delete=models.CASCADE
    is_active = models.BooleanField(default=False)
    class Meta:
        unique_together = ('item',)

class TableB(models.Model):
    name = models.SlugField(max_length=50, null=True, blank=True)
    display_name = models.CharField(max_length=50)
    is_active = models.BooleanField(default=False)

I'd like to translate below query into ORM

select DISTINCT TableB.name, TableB.display_name
from TableB
inner join TableA on TableA.item_id = TableB.id
where TableA.is_active=true AND
      TableB.display_name is not NULL AND
      TableB.name is not NULL

Could someone help me with the query?

Answer 1

You can use a related_name attribute in your item foreign key definition. This way, you don't have to guess what the reverse relation is called from TableB.

item = models.ForeignKey(TableB, related_name='tablea', on_delete=models.CASCADE)

Then you can find your distinct values in a single query like this:

from django.db.models import Q

items = TableB.objects.filter(
  Q(tablea__is_active=True) &
  Q(display_name__isnull=False) &
  Q(name__isnull=False)
).values(
  'name', 'display_name'
).distinct(
  'name', 'display_name'
).order_by(
  'name', 'display_name'
)