Django filter based in joined table

Question

I have two tables:

class Client(models.Model): name = models.TextField() lastname = models.TextField() class Meta: managed = False db_table = 'client' class Clientreport(models.Model): id_client_home = models.ForeignKey('Client', models.DO_NOTHING, db_column='id_client_home', related_name='home_id_client_home') id_client_reported = models.ForeignKey('Client', models.DO_NOTHING, db_column='id_client_reported', related_name='client_id_client_home') class Meta: managed = False db_table = 'clientreport'

And I'm trying to build a query similar to this:

SELECT cr.*, cl.id, cl.name, cl.lastname FROM Clientreport cr INNER JOIN Client cl ON cr.id_client_reported = cl.id WHERE (LOWER(cl.name) LIKE LOWER('%jo%') OR LOWER(cl.lastname) LIKE LOWER('%jo%') )

I tried using: SQL queries

But, now I'm trying to do it using django. How I can access to a joined model using django???

Gavin Clark · Accepted Answer · 2017-09-26 17:15:37Z

You can query across joins using standard Django Queryset filters, using __ to go across the relationship like this:

Clientreport.objects.filter(client_id_client_home__name='jo')

client_id_client_home being the related_name from your Clientreport model. More info here in the documentation on queries using related objects.

To reproduce the LIKE LOWER('%jo%') you can use __icontains:

Clientreport.objects.filter(client_id_client_home__name__icontains='jo')

Awesome! thanks! I didn't know about the __ to go across the relationships. Thanks @Gavin Clark

Collectives™ on Stack Overflow

Django filter based in joined table

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related