Merging a field in one model to another with ForeignKey, Django Subquery and OuterRef

Question

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.

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.

Models

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

Attempted Code

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

Another Attempt

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(),                                                         
    )                                                                            
)

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

Thank you very much

score 1 · Accepted Answer · answered Jul 18 '21 at 19:16

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]
    )
)

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').

Merging a field in one model to another with ForeignKey, Django Subquery and OuterRef

Models

Attempted Code

Another Attempt

1 Answers1

Linked