Django ORM中的全外连接

h9vpoimq  于 2023-05-08  发布在  Go
关注(0)|答案(2)|浏览(157)

我有一组这样的模型:

class Expense(models.Model):
    debitors = models.ManyToManyField(Person, through='Debitor')
    total_amount = models.DecimalField(max_digits=5, decimal_places=2)

class Debitor(models.Model):
    class Meta:
        unique_together = [['person', 'expense']]

    person = models.ForeignKey(Person)
    expense = models.ForeignKey(Expense)
    amount = models.IntegerField()

class Person(models.Model):
    name = models.CharField(max_length=25)

也就是说,我在Expense s和Person s * 到 * Debitor s之间有一个多对多的关系。
对于给定的Expense,我想查询所有Debitor s/Person s,但包括不存在Debitor记录的Person s。在这种情况下,我想要一个具有特定person_id的行,但是具有DebitorExpensenull。换句话说,ExpenseDebitorPerson之间的FULL OUTER JOIN。
我还没有找到如何用Django的ORM来实现这一点。我已经找到了一个原始查询,它可以做我想要的事情(这有点复杂,因为SQLite本机不支持FULL OUTER JOIN):

SELECT debitor_id as id, amount, src_person.id as person, expense_id as expense
FROM src_person
LEFT JOIN (SELECT src_debitor.id as debitor_id, src_debitor.amount as amount, src_expense.id as expense_id, src_debitor.person_id as person_id
           FROM src_debitor
           INNER JOIN src_expense ON src_debitor.expense_id = src_expense.id and src_expense.id = x) as sol
           ON src_person.id = sol.person_id;

例如,对于expense_id=6,返回:
| 身份证|数量|居民|费用|
| --------------|--------------|--------------|--------------|
| 四个|1| 1|六|
| 五|1|二|六|
| 零|零|三|零|
| 零|零|四个|零|
| 零|零|五|零|
| 零|零|六|零|
但是,当使用Debitor.objects.raw()执行此查询时,我得到以下错误:

ValueError: Cannot assign "1": "Debitor.person" must be a "Person" instance.

我假设当原始查询包含与其他模型的关系时,不可能使用Debitor.objects.raw()将原始查询Map到模型示例。否则我不知道我做错了什么…
有没有其他更好的方法来做到这一点?我仍然希望在Django的ORM中有一些方法可以做到这一点,但到目前为止我还没有找到。

wecizke3

wecizke31#

我想要一个API端点,对于给定的Expense,它会生成一个所有Persons的列表,其中包含Debitor列(例如:金额),如果该费用存在Debitor记录。
我认为你可以使用:

from django.db.models import F

Person.objects.annotate(
    amount=F('debitor__amount')
).filter(debitor__expense__pk=expense_pk)

这将检索与Expense相关的Person,其主键为 expense_pk。来自这个查询集的Person s将有一个额外的属性.amount,它是debitor的数量。

yhived7q

yhived7q2#

使用一个sqlite数据库和你提出的模型,我可以通过使用django.db.connection的原始查询和修改你的查询来得到你想要的,但仍然不使用Django ORM:

from django.db import connection

def my_custom_sql():
    with connection.cursor() as cursor:
        cursor.execute("""
        SELECT debitor.debitor_id, person.id as person_id,
               debitor.amount, expense.expense 
        FROM many2many_person person
        LEFT JOIN (
          select debitor.id as debitor_id, debitor.amount as amount,
                 debitor.person_id as person_id, debitor.expense_id
          from many2many_debitor debitor
          where debitor.expense_id=1 -- your x here
        ) debitor on debitor.person_id=person.id
        LEFT JOIN (
          select expense.id, expense.total_amount as expense
          from many2many_expense expense
          where id=1                 -- your x here
        ) expense on expense.id=debitor.expense_id
        """)
        rows = cursor.fetchall()

    print(rows)

    return rows

在我的例子中,这将返回(我有5个Person,只有一个带有Expense):

[
  (1, 1, 10, 100),
  (None, 2, None, None),
  (None, 3, None, None),
  (None, 4, None, None),
  (None, 5, None, None)
]

这意味着所有的Person对象都在那里,但只有那些具有expense_id=1的对象才填充了其余的数据。在这种情况下,您必须将many2many_更改为src_
我尝试过Django OuterRefSubquery,但完全失败。

多次修改:修改@WillemVanOnsem的答案,我想我得到了:

expense_id = 1
    persons = Person.objects.annotate(
        debitor_id=Case(
            When(debitor__expense_id=expense_id, then=F('debitor__id')),
            default=Value(None),
        ),
        amount=Case(
            When(debitor__expense_id=expense_id, then=F('debitor__amount')),
            default=Value(None),
        ),
        expense_id=Case(
            When(debitor__expense_id=expense_id, then=F('debitor__expense_id')),
            default=Value(None),
        )
    )

这给了我与修改后的查询相同的输出。

相关问题