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???

Answer 1

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