基于联接表的Django过滤器 [英] Django filter based in joined table

问题描述

我有两个表：

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

我尝试使用：
SQL查询

但是，现在我正在尝试它使用django。
如何使用django访问联接模型？

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

推荐答案

您可以使用标准Django在联接之间进行查询Queryset过滤器，使用 __ 来遍历这种关系：

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 是您的 Clientreport 模型中的 related_name 。有关更多信息，请参见使用相关对象的查询。

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

要重现 LIKE LOWER（'％jo％'），您可以使用 __ icontains ：

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

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

这篇关于基于联接表的Django过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！