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?

you are using values. hence you don't need select_related here. you can have more fields in values to get what you want. — shivankgtm
– shivankgtm, Commented Feb 18, 2022 at 7:01
Hi @MohamedElKalioby, I have added more details along with the Demo Models — Abinash Biswal
– Abinash Biswal, Commented Feb 18, 2022 at 7:10
why? when you use values, it already take care of select_related — shivankgtm
– shivankgtm, Commented Feb 18, 2022 at 7:11

Tadej Krevh · Accepted Answer · 2022-02-18 11:03:06Z

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' )

Collectives™ on Stack Overflow

How to join 2 tables in django ORM

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related