Django从两个表中检索数据

Question

我有以下型号：

class Purchases(models.Model):
    p_id = models.IntegerField(primary_key=True, default=0)
    date = models.DateField(default=datetime.now)

    def __str__(self):
        return self.date.strftime('%Y-%m-%d')

    class Meta:
        verbose_name_plural = "Purchases"


class Purchased_Items(models.Model):
    p_id = models.ForeignKey(Purchases, on_delete=models.CASCADE)
    item = models.CharField(max_length=80)
    size = models.IntegerField(max_length=2)
    quantity = models.IntegerField(max_length=3)
    price = models.IntegerField(max_length=4)
    total = models.IntegerField(default=0)

    def __str__(self):
        return self.item

    class Meta:
        verbose_name_plural = "Purchased Items"

我正在尝试使用内部联接检索：

pur = Purchased_Items.objects.all().select_related()

但我没有得到要求的结果。基本上我需要以下sql：

select * from finance_purchases as fp
     inner join finance_purchased_items pi 
           ON (fp.p_id = pi.p_id_id);

另一个问题是，虽然Purchased\u Items中的外键是p\u id，但是sql表中的列中的外键是p\u id！为什么？背后的逻辑是什么？你知道吗

谢谢

Answer 1

回复：选择相关

Django ORM允许您直接使用简单的属性引用从Purhcased_Items对象访问Purchases对象。你知道吗

示例：

item = Purchased_Items.objects.get(pk=123)  # This is the `Purchase_Items` instance
purchase = item.p_id  # This is the `Purchases` instance

这意味着ORM中默认允许“join”操作。使用select_related的原因是，当您执行这个外键引用时，Django每次都会触发一个离散查询。如果你有这样的代码：

items = Purchased_Items.objects.filter()[:100]  # This fires 1 sql query
for i in items:
    print i.purchase  # This line fires 1 SQL query

然后总共触发101个SQL查询。这也称为N+1select查询问题1

如果将行更改为：

items = Purchased_Items.objects.filter().select_related('p_id')[:100]  # This fires just 1 sql query for the entire operation
for i in items:
    print i.purchase  # No query fired, data is already selected in Join

您可以通过在django中打印查询来查看差异

print Purchased_Items.objects.filter().query
print Purchased_Items.objects.filter().select_related('p_id').query

回复：p\u id\u id

Django ORM自动为ForeignKey关系创建带有_id后缀的字段名。其思想是对象可以作为item.p直接访问，数据库字段键设置为p_id。这有助于检索purchase对象