在Django ORM中向LEFT OUTER JOIN添加非FK条件以返回不加入的行

时间：2021-01-24 11:40:19

I want to run a query of the type:

我想运行类型的查询:

SELECT company.id
FROM company
LEFT OUTER JOIN employee
    ON company.id=employee.company_id AND employee.retired=True
WHERE employee.id IS NULL

IE, I want the database to try to join each company to an employee where retired=True , and to return me the companies that fail to join.

IE,我希望数据库尝试将每个公司加入一个退休= True的员工,并将我的公司归还给失败的公司。

This will give me all companies who don't have a retired employee, including those with no employees.

这将给我所有没有退休员工的公司,包括那些没有员工的公司。

Is there any way of doing this in Django without using .raw()?

有没有办法在不使用.raw()的情况下在Django中执行此操作?

I can do the LEFT JOIN with Company.objects.filter(employee__isnull=True) but I can't add the retired join condition, so I get companies with retired employees. I can't make it a Q(), because in the WHERE clause employee.retired will be NULL. I can do:

我可以使用Company.objects.filter(employee__isnull = True)进行LEFT JOIN,但我无法添加退休的加入条件,因此我得到了退休员工的公司。我不能使它成为Q(),因为在WHERE子句中employee.retired将为NULL。我可以:

Company.objects.exclude(id__in=Employee.objects.filter(retired=True).values('company_id'))

Which is equivalent to the less efficient:

这相当于效率较低:

SELECT * FROM company WHERE id NOT IN (SELECT company_id FROM employee WHERE retired=True)

Is there a way of achieving the LEFT JOIN in Django?

有没有办法在Django中实现LEFT JOIN?

Or at least changing the second query to a WHERE NOT EXISTS (SELECT 1 FROM employee WHERE company_id=company.id AND retired=True) so we're filtering the sub-query?

或者至少将第二个查询更改为WHERE NOT EXISTS(SELECT 1 FROM employee WHERE company_id = company.id AND retired = True)所以我们要过滤子查询?

I'm using Django 1.8.5, postgres 9.4 and psycopg2 2.6.1

我正在使用Django 1.8.5,postgres 9.4和psycopg2 2.6.1

1 个解决方案

#1

0  

OK, I don't know a better way to achieve this in one shot, because there's no way to tell django to select ALL employees are not retired. If you think your query would work, just use Company.objects.raw().

好吧,我不知道一个更好的方法来实现这一点,因为没有办法告诉django选择所有员工都没有退休。如果您认为您的查询可行,请使用Company.objects.raw()。

Here's my attempt:

这是我的尝试:

no_retired_employees_company = Employee.objects.filter(is_retired=False) \
                                               .values_list('company_id', flat=True).distinct()

no_employee_company = Company.objects.filter(employee__isnull=True) \
                                     .values_list('id', flat=True).distinct()

result = list(a) + list(b)

#1

0  

OK, I don't know a better way to achieve this in one shot, because there's no way to tell django to select ALL employees are not retired. If you think your query would work, just use Company.objects.raw().

好吧,我不知道一个更好的方法来实现这一点,因为没有办法告诉django选择所有员工都没有退休。如果您认为您的查询可行,请使用Company.objects.raw()。

Here's my attempt:

这是我的尝试:

no_retired_employees_company = Employee.objects.filter(is_retired=False) \
                                               .values_list('company_id', flat=True).distinct()

no_employee_company = Company.objects.filter(employee__isnull=True) \
                                     .values_list('id', flat=True).distinct()

result = list(a) + list(b)
标签：

相关文章