DjangoForeignKey 与 null=True、内连接和左外连接

发布于 2024-08-22 00:36:39 字数 961 浏览 5 评论 0原文

假设我有两个 Django 模型“个人”和“公司”，如下所示： -

class Company(models.Model):
    name = models.CharField()

class Person(models.Model):
    last_name = models.CharField(blank=True)
    first_name = models.CharField()
    company = models.ForeignKey(Company, null=True, blank=True)

个人可能属于也可能不属于公司。

我正在使用MySQL。我想要所有不属于任何公司的人员，即公司为空的人员。

如果我这样做 Person.objects.filter(company__isnull=True) 我得到的 SQL 本质上是： -

SELECT * FROM PersonTable LEFT OUTER JOIN AgencyTable ON (PersonTable.company_id = AgencyTable.id) WHERE AgencyTable.id IS NULL

如何实现以下 SQL： -

SELECT * FROM PersonTable INNER JOIN AgencyTable ON (PersonTable.company_id = AgencyTable.id) WHERE AgencyTable.id IS NULL

根据我从 Django 用户邮件列表中收集到的信息，这曾经是 QuerySet 重构之前的行为。

编辑——现在我看到了我的问题的亵渎！

我想说的是我只是想做

SELECT * FROM PersonTable WHERE PersonTable.company_id IS NULL

原文

Let's say I have two Django models Person and Company as follows: -

class Company(models.Model):
    name = models.CharField()

class Person(models.Model):
    last_name = models.CharField(blank=True)
    first_name = models.CharField()
    company = models.ForeignKey(Company, null=True, blank=True)

A Person may or may not belong to a Company.

I am using MySQL. I want all Persons that do not belong to any Company, that is, Persons where company is null.

If I do Person.objects.filter(company__isnull=True) I get an SQL which is essentially: -

SELECT * FROM PersonTable LEFT OUTER JOIN AgencyTable ON (PersonTable.company_id = AgencyTable.id) WHERE AgencyTable.id IS NULL

How do I go about achieving the following SQL: -

SELECT * FROM PersonTable INNER JOIN AgencyTable ON (PersonTable.company_id = AgencyTable.id) WHERE AgencyTable.id IS NULL

From what I gather from reading up the Django Users mailing list, this used to be the behavior before QuerySet Refactor.

EDIT -- Now I see the blasphemy of my question!

What I want to say is I simply want to do

SELECT * FROM PersonTable WHERE PersonTable.company_id IS NULL

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最舍不得你 2024-08-29 00:36:39

好吧，这个问题已经很老了，很快补丁就会出现在 Django 中。但就目前而言，答案就在 http://code.djangoproject.com/ticket/10790：

解决方法：代替
Person.objects.filter(company=None)
使用
Person.objects.exclude(company__isnull=False)

回复收藏 0 原文

梦行七里 2024-08-29 00:36:39

它应该像这样简单：

Person.objects.filter(company_id__isnull=True)

注意 company_id 的使用，这是由foreignkey

Edit

创建的默认整数字段抱歉，自 0.9.5 以来我没有积极使用 django 。要么我正在考虑 1.0 之前的行为，要么我混淆了 sqlalchemy 和 Django ORM。无论哪种情况，正如评论所述，上述内容似乎都不起作用。

看起来在当前 django 中获取所需查询的唯一方法是使用 .extra 查询参数，该参数附带一整套警告。

Person.objects.extra(where=['company_id IS NULL'])

请注意，这可能无法移植到所有数据库，并且它可能无法与 filter() 结合使用，以及许多可能的问题。我建议不要在整个代码中使用它，而是将其移动到 Person 上的类方法，例如

 @classmethod
 def list_unaffiliated_people(cls):
    return cls.objects.extra(where=['company_id IS NULL'])

：有慢点吗？）

It should be as simple as:

Person.objects.filter(company_id__isnull=True)

Note the use of company_id which is the default integer field created by the ForeignKey

Edit

Sorry, I haven't actively used django since 0.9.5. Either I'm thinking of pre-1.0 behavior, or I'm muddling up sqlalchemy and Django ORM. In either case, as the comments stated, the above appears to not work.

It looks like the only way to get the query you want in current django is to use the .extra query parameter, which comes with a whole list of caveats.

Person.objects.extra(where=['company_id IS NULL'])

Note that this may not be portable to all DB's, and it may not work combined with filter(), and any number of possible issues. I would recommend not using this throughout your code, and instead moving it to a classmethod on Person like:

 @classmethod
 def list_unaffiliated_people(cls):
    return cls.objects.extra(where=['company_id IS NULL'])

Alternately, just use the proper ORM query syntax and suck up the possible performance hit (have you actually benchmarked the more complicated query to see that it's any slower?)

回复收藏 0 原文