Django复杂查询多个模型

ltskdhd1 于 2023-06-07 发布在 Go

关注(0)|答案(1)|浏览(105)

我有这些模型，需要在下面进行查询

class Quote(models.Model):
    company = models.ForeignKey(
        Company,
        on_delete=models.CASCADE,
    ) 
    status = models.ForeignKey(
        StatusType,
        on_delete=models.CASCADE,
    )

class QuoteStatusHistory(StatusHistoryBase):
    quote = models.ForeignKey(Quote, on_delete=models.CASCADE)
    create_date = models.DateTimeField(auto_now_add=True)

class StatusType(models.Model):
    type_class = models.CharField(max_length=10, choices=COMMON.CLASS_STATUS_TYPE)
    type_code = models.CharField(max_length=50)
    type_name = models.CharField(max_length=100)

Here is the query:

SELECT qt.id
    ,(SELECT qst.create_date
    FROM quote_status_history qst
    JOIN status_type st ON (type_class = 'QUOTE' AND type_code = 'AVAILABLE' AND qst.status_type_id = st.id)
    WHERE qst.quote_id = qt.id) send_date 
FROM quote qt
WHERE qt.company_id = 90;

目前，我只是做一个query_set来获取特定公司的所有Quotes，然后循环所有的company.quotes记录，以便从QuoteStatusHistory中获取create_date，但问题是性能...它将为每个记录命中数据库，并且将永远加载数据。
从这个特定查询中获取数据的最有效方法是什么？

django

来源：https://stackoverflow.com/questions/76387020/django-complex-query-multiple-models

1条答案

按热度按时间

vptzau2j1#

我想这个Django ORM可以帮助你。您可以在其中根据筛选条件查找所有行。

# For Single comapny
QuoteStatusHistory.objecjs.filter(quote__company_id=19, 
quote__status__type_class = 'QUOTE',quote__status__type_code = 'AVAILABLE').values(id=F('quote__id'), send_date=F('create_date')


# for multiple company ids you can simply pass an array of company ids in quote_company 

QuoteStatusHistory.objecjs.filter(quote__company_id__in=[1,2,3,4], quote__status__type_class = 'QUOTE',quote__status__type_code = 'AVAILABLE').values(id=F('quote__id'), send_date=F('create_date'))

如果你想从Quote表中过滤数据，那么你必须为反向关系添加related_query_name，然后你就可以使用这个查询。

class QuoteStatusHistory(StatusHistoryBase):
    quote = models.ForeignKey(Quote, on_delete=models.CASCADE, related_query_name="quote_query")
    create_date = models.DateTimeField(auto_now_add=True)

# for multiple company ids
Quote.objects.filter(company_id__in=[1,2,3,4], status__type_class = 'QUOTE', status__type_code = 'AVAILABLE').values(id=F('id'), send_date=F('quote_query__create_date'))
# For Single comapny
Quote.objects.filter(company_id=19, status__type_class = 'QUOTE', status__type_code = 'AVAILABLE').values(id=F('id'), send_date=F('quote_query__create_date'))

第三个选项是使用SubQuery：----

from django.db.models import Subquery, OuterRef

subquery = QuoteStatusHistory.objects.filter(
    quote_id=OuterRef('id'),
    quote__type_class='QUOTE',
    quote__type_code='AVAILABLE'
).values('create_date')[:1]

#For single company
quotes = Quote.objects.annotate(
    send_date=Subquery(subquery)
).filter(company_id=90).values('id', 'send_date')

#For multiple comapny
quotes = Quote.objects.annotate(
    send_date=Subquery(subquery)
).filter(company_id__in=[90, 10, 29]).values('id', 'send_date')

赞(0）回复(0）举报 2023-06-07

我来回答

Django复杂查询多个模型

1条答案

相关问题

热门标签

最新问答