文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >一个django objects.get请求的SQL查询乘法

问一个django objects.get请求的SQL查询乘法
EN

Stack Overflow用户
提问于 2021-05-27 19:49:51
回答 2查看 97关注 0票数 3

我正在研究Django，我有一个ClientPartner类作为模型：

代码语言：javascript
运行
复制
class ClientPartner(BusinessObject):
    charge_account  = models.ForeignKey("ClientAccount", on_delete=models.DO_NOTHING, null=True, blank=True, related_name="partners_as_charge")
    client_account  = models.ForeignKey("ClientAccount", on_delete=models.DO_NOTHING, null=True, blank=True, related_name="partners_as_client")
    legal_entity    = models.ForeignKey("legal_entity.LegalEntity", on_delete=models.CASCADE, related_name="partners")
    partner         = models.ForeignKey("legal_entity.LegalEntity", on_delete=models.CASCADE, related_name="client_legal_entities")
    product_account = models.ForeignKey("ClientAccount", on_delete=models.DO_NOTHING, null=True, blank=True, related_name="partners_as_product")
    vendor_account  = models.ForeignKey("ClientAccount", on_delete=models.DO_NOTHING, null=True, blank=True, related_name="partners_as_vendor")

我查询该表如下所示：

代码语言：javascript
运行
复制
lo_partnership = ClientPartner.objects.get(legal_entity=self.owner, partner=io_vendor)

我使用django标准日志，当我运行这一行时，我有下面的SQL查询：

代码语言：javascript
运行
复制
DEBUG 2021-05-27 21:38:42,237 utils 21776 22144 (0.016) SELECT "client_clientpartner"."id", "client_clientpartner"."creation_date", "client_clientpartner"."creation_user_id", "client_clientpartner"."deleted", "client_clientpartner"."deletion_date", "client_clientpartner"."deletion_user_id", "client_clientpartner"."update_date", "client_clientpartner"."update_user_id", "client_clientpartner"."charge_account_id", "client_clientpartner"."client_account_id", "client_clientpartner"."legal_entity_id", "client_clientpartner"."partner_id", "client_clientpartner"."product_account_id", "client_clientpartner"."vendor_account_id" FROM "client_clientpartner" WHERE ("client_clientpartner"."legal_entity_id" = 2 AND "client_clientpartner"."partner_id" = 935) LIMIT 21; args=(2, 935)
DEBUG 2021-05-27 21:38:42,243 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id" FROM "legal_entity_legalentity" WHERE "legal_entity_legalentity"."id" = 2 LIMIT 21; args=(2,)
DEBUG 2021-05-27 21:38:42,248 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 2 LIMIT 21; args=(2,)
DEBUG 2021-05-27 21:38:42,256 utils 21776 22144 (0.016) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 2 LIMIT 21; args=(2,)
DEBUG 2021-05-27 21:38:42,262 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id" FROM "legal_entity_legalentity" WHERE "legal_entity_legalentity"."id" = 935 LIMIT 21; args=(935,)
DEBUG 2021-05-27 21:38:42,267 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 935 LIMIT 21; args=(935,)
DEBUG 2021-05-27 21:38:42,275 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 935 LIMIT 21; args=(935,)
DEBUG 2021-05-27 21:38:42,282 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id" FROM "legal_entity_legalentity" WHERE "legal_entity_legalentity"."id" = 2 LIMIT 21; args=(2,)
DEBUG 2021-05-27 21:38:42,287 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 2 LIMIT 21; args=(2,)
DEBUG 2021-05-27 21:38:42,296 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 2 LIMIT 21; args=(2,)
DEBUG 2021-05-27 21:38:42,305 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id" FROM "legal_entity_legalentity" WHERE "legal_entity_legalentity"."id" = 935 LIMIT 21; args=(935,)
DEBUG 2021-05-27 21:38:42,312 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 935 LIMIT 21; args=(935,)
DEBUG 2021-05-27 21:38:42,320 utils 21776 22144 (0.000) SELECT "legal_entity_legalentity"."id", "legal_entity_legalentity"."creation_date", "legal_entity_legalentity"."creation_user_id", "legal_entity_legalentity"."deleted", "legal_entity_legalentity"."deletion_date", "legal_entity_legalentity"."deletion_user_id", "legal_entity_legalentity"."update_date", "legal_entity_legalentity"."update_user_id", "legal_entity_legalentity"."comment", "legal_entity_legalentity"."image_id", "legal_entity_organization"."legalentity_ptr_id", "legal_entity_organization"."accounting_method_id", "legal_entity_organization"."ape", "legal_entity_organization"."bic", "legal_entity_organization"."commercial_name", "legal_entity_organization"."country_id", "legal_entity_organization"."draft", "legal_entity_organization"."factory_code", "legal_entity_organization"."legal_form_id", "legal_entity_organization"."main_activity_id", "legal_entity_organization"."name", "legal_entity_organization"."siren", "legal_entity_organization"."tax_model_id", "legal_entity_organization"."url_login", "legal_entity_organization"."url_privacy", "legal_entity_organization"."url_website", "legal_entity_organization"."vat_number", "legal_entity_organization"."vat_regime_id" FROM "legal_entity_organization" INNER JOIN "legal_entity_legalentity" ON ("legal_entity_organization"."legalentity_ptr_id" = "legal_entity_legalentity"."id") WHERE "legal_entity_organization"."legalentity_ptr_id" = 935 LIMIT 21; args=(935,)

我不明白为什么我有13个SQL查询。我只想要第一个。其他的主要是重复的。有人能解释一下发生了什么吗？

然后我这样做(上面的日志是在运行这段代码之前生成的)：

代码语言：javascript
运行
复制
if lo_partnership.vendor_account_id is None or lo_partnership.charge_account_id is None:
    # Not configured, use default waiting account
    ev_excl_tax_account = self.waiting_account
    ev_incl_tax_account = self.waiting_account
else:
    ev_excl_tax_account = lo_partnership.charge_account.code
    # 3. Check if vendor account is an auxiliary account
    if lo_partnership.vendor_account.general_account_id is not None:
        ev_incl_tax_account = lo_partnership.vendor_account.general_account.code
        ev_auxiliary_account = lo_partnership.vendor_account.code
    else:
        ev_incl_tax_account = lo_partnership.vendor_account.code

更新

根据要求，我隔离了该函数：

代码语言：javascript
运行
复制
@action(methods=['get'], detail=False)
def testing(self, io_request, pk=None):
    """
    Method to get test objects.get outside main code
    """
    from django.db import connection # to check all queries
    connection.queries
    tmp = len(connection.queries) # before the get : 1 query
    result = ClientPartner.objects.select_related('legal_entity').get(legal_entity__id=2, partner__id=379)
    tmp2 = len(connection.queries) # after the get : 17 queries
    break_point_stoper = tmp2 # breakpoint in debug to stop

通过使用失眠症调用这个函数，我对select_related有15个查询，没有13个查询。Somes查询仍然是重复的。

python
django
EN

回答 2

Stack Overflow用户

发布于 2021-05-28 13:45:33

获得这么多查询的原因是Django使用了惰性的QuerySets。这意味着在使用SQL之前不会执行它。除了某些情况外，比如get()方法，因为该方法已经返回了一个对象，而不是QuerySet。但同样的原则也适用。

因此，取决于以后使用的内容，它可能会执行一个或多个额外的查询(这就是为什么我问您在查询之后在注释中做什么)。

试着看看它是如何工作的：

代码语言：javascript
运行
复制
from django import db

lo_partnership = ClientPartner.objects.get(legal_entity=self.owner, partner=io_vendor)
# Check the last SQL executed
print(db.connection.queries[-1:])
# You should see something similar to:
# SELECT {fields} FROM {ClientPartner table} WHERE legal_entity.id = {id} AND partner.id={another id}
# I.e. a simple SQL on a single table

# Now use that same object to access data from a related table:
print(lo_partnership.vendor_account.code)
# The previous query did not return any of this info, so Django will need to execute a new Query.
# So, again, check for the last SQL executed:
print(db.connections.queries[-1:])
# You will see Django performed a new query to fetch that value.
# SELECT {fields} FROM {ClientAccount table} where id = {the id}

为了避免这种情况，您应该以这样的方式使用select_related：

代码语言：javascript
运行
复制
lo_partnership = ClientPartner.objects.select_related('vendor_account').get(legal_entity=self.owner, partner=io_vendor)
print(db.connections.queries[-1:])
# Now you will see Django is producing an INNER JOIN to return values from the related_table.

因此，如果以后要使用对象中任何一个ForeignKeys的数据，则应该将所有这些FK字段添加到select_related方法中。否则，Django将执行多个查询。

这并不能解释为什么会多次执行完全相同的SQL。有不同的字段指向相同的模型可能。我对此不太确定，但是如果您拥有与product_account和vendor_account相同的id (并且访问两者)，那么它可能会执行两次相同的查询。

查看Django文档，了解更多关于Django查询系统工作方式的详细信息。

票数 1
EN

Stack Overflow用户

发布于 2021-05-27 21:00:44

看完那个任务后，有两个解决方案。

第一:使用

代码语言：javascript
运行
复制
ClientPartner.objects.select_related('legal_entity', 'partner')

关于queryset和获取对象列表

第二:使用legal_entity_id=self.owner_id，partner_id=io_vendor_id (用id调用，您可以用列表调用id，如下所示：

代码语言：javascript
运行
复制
ClientPartner.objects.filter(legal_entity_id__in=[owner_id_list],partner_id__in=[io_vendor_id_list])

)

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/67729016

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档