-1

I have two models, RetailLocation and Transaction, which share a one-to-many relationship respectively. I trying to annotate the total number of days (Count) a RetailLocation has any number of Transactions for. In doing so, I am filtering the Transaction.date field to just Date instead of Datetime, and trying to SELECT DISTINCT dates, but am running into an error "NotImplementedError: annotate() + distinct(fields) is not implemented."

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)

Attempted Code

test = RetailLocation.objects.annotate(                                                    
    days_operating=Subquery(                                                     
        Transaction.objects.filter(r_loc=OuterRef("pk"))                          
        .distinct('date__date')                                                  
        .annotate(count=Count('pk'))                                             
        .values('count')                                                         
    )                                                                            
)

I tried to reference this solution in combination with an earlier post solved by Willem, but using distinct seems to cause the NotImplementedError referenced above. I believe there also might be a solution using Count( , distinct=True), but it wouldn't help unless I could distinct on date__date, as I am only trying to find days that any number of Transactions occurred on.

Thank you very much for your time.

hEX90
  • 21
  • 4

1 Answers1

0

Solution 1 (using ForeignKey)

RetailLocation.objects.annotate(days_operating=Count('transaction__date__date', distinct=True))

Using the ForeignKey relationship between RetailLocation and Transaction, first you follow the ForeignKey to get the related set of Transaction objects ('transaction'), then select the 'date' column ('transaction_date'), and finally cast it as a Date ('transaction_date_date'). This returns a list of dictionaries {date_date: date objects}, and using Count( , distinct=True) counts the set of unique dates.

Solution 2 (using Subquery)

test = RetailLocation.objects.annotate(                                                    
    days_operating=Subquery(                                                     
    Transaction.objects.filter(r_loc=OuterRef("pk"))
    .values('r_loc')
    .annotate(count=Count('date__date', distinct=True))                                             
    .values('count')
    )
)

This works basically the same way as solution 1, but you don't need to have a ForeignKey relationship between the two models if you wanted to filter based on something else (date perhaps).

Useful link on Subquery Aggregation

hEX90
  • 21
  • 4