使用 ForeignKey、Django Subquery 和 OuterRef 将一个模型中的字段合并到另一个模型中 [英] Merging a field in one model to another with ForeignKey, Django Subquery and OuterRef

问题描述

我有两个模型，RetailLocation 和 Transaction，它们分别共享一对多的关系.我的目标是用与该 RetailLocation 相对应的最新交易的日期来注释 RetailLocation.

I have two models, RetailLocation and Transaction, which share a one-to-many relationship respectively. My goal is to annotate RetailLocation with the date of the latest Transaction which corresponds to that RetailLocation.

模型交易包括r_loc_name"；这是与 RetailLocation.name 完全相同的字符串.我相信这个字段是多余的，我希望有一个更好的解决方案，只使用 RetailLocation 中的 r_loc ForeignKey(和设置)，但我还没有想出如何.

The model Transaction includes "r_loc_name" which is the exact same string as RetailLocation.name. I believe this field is redundant, and I expect there is a better solution using only the r_loc ForeignKey (and set) in RetailLocation, however I have not figured out how.

class RetailLocation(models.Model):
    name = models.CharField(max_length=200, null=True)

class Transaction(models.Model):
    date = models.DateTimeField(null=True)
    r_loc = models.ForeignKey(RetailLocation, null=True, on_delete=models.SET_NULL)
    r_loc_name = models.CharField(max_length=200, null=True) # Likely redundant

尝试的代码

loc_latest = RetailLocation.objects.all().annotate(                              
    latest_txn_time=Subquery(                                                    
        Transaction.objects.filter(r_loc_name=OuterRef("name"))                  
        .order_by("date")                                                        
        .values("date")                                                          
        .last()                                                                  
    )                                                                            
)

另一次尝试

loc_latest = RetailLocation.objects.all().annotate(                              
    latest_txn_time=Value(                                                                
        Subquery(                                                                
            RetailLocation.objects.get(name=OuterRef("name"))               
            .transaction_set.order_by("date")                                    
            .last()                                                              
            .date                                                                
        ),                                                                       
        DateTimeField(),                                                         
    )                                                                            
)

我的目标是引用RetailLocation.latest_txn_time，这将是引用该RetailLocation 的最新交易的注释日期时间.

My goal is to to reference RetailLocation.latest_txn_time, which would be the annotated datetime of the latest Transaction referenced to that RetailLocation.

非常感谢

推荐答案

您可以在主键上使用 OuterRef，并使用 slicing ([:1]) 获得最后一项，所以:

You can work with the OuterRef on the primary key, and use slicing ([:1]) to obtain the last item, so:

loc_latest = RetailLocation.objects.annotate(
    latest_txn_time=Subquery(
        Transaction.objects.filter(
            r_loc=OuterRef('pk')
        ).order_by('-date').values('date')[:1]
    )
)

r_loc_name 确实是多余的.对于 Transaction 对象，您可以使用 my_transaction.r_loc.name，并且您可以使用 Transaction.objects.filter(r_loc__name='some-名称').

The r_loc_name is indeed redundant. For a Transaction object you can work with my_transaction.r_loc.name, and you can filter, etc. with Transaction.objects.filter(r_loc__name='some-name').

这篇关于使用 ForeignKey、Django Subquery 和 OuterRef 将一个模型中的字段合并到另一个模型中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！