Hi,
I am trying to retrieve records over a 30 day period, then group via the fk and annotate the item with a number of different metrics, but for some reason the grouping isn’t grouping. Instead its just returning every item.
im using .values(contract).annotate(...
end_date = datetime.now().date() - timedelta(days=1)
start_date = end_date - timedelta(days=29)
metrics = ContractMetric.objects.filter(capture_date__range=[start_date, end_date])
# Subqueries for latest and previous values
latest_floor_price_subquery = metrics.filter(contract=OuterRef('contract')).order_by('-capture_date').values('floor_price')[:1]
previous_floor_price_change_subquery = metrics.filter(contract=OuterRef('contract'), capture_date=start_date).values('floor_price')[:1]
previous_unique_holders_subquery = metrics.filter(contract=OuterRef('contract'), capture_date=start_date).values('total_owners')[:1]
contract_sums = metrics.values('contract__id').annotate(
capture_count=Count('id'),
total_volume_traded_change_sum=Sum('total_volume_traded_change'),
latest_floor_price=Coalesce(Subquery(latest_floor_price_subquery, output_field=FloatField()), Value(0, output_field=FloatField())),
previous_floor_price=Coalesce(Subquery(previous_floor_price_change_subquery, output_field=FloatField()), Value(0, output_field=FloatField())),
floor_price_change_over_7_days=ExpressionWrapper(
F('latest_floor_price') - F('previous_floor_price'),
output_field=FloatField()
),
When i print the value of capture_count i am expecting to get 30. One for each record over the 30 day period.
but it returns 1
for item in contract_sums:
print(f"Contract ID: {item['contract']}, Capture Count: {item['capture_count']}")
returns:
Contract ID: 340, Capture Count: 1
Contract ID: 340, Capture Count: 1
Contract ID: 340, Capture Count: 1
Contract ID: 340, Capture Count: 1
Contract ID: 340, Capture Count: 1
Contract ID: 340, Capture Count: 1
Contract ID: 340, Capture Count: 1
...
Im not sure if its something weird with my model or I’m misunderstanding how to group, but i use the same syntax on other views and it seems to work perfectly
class Contract(models.Model):
address = models.CharField(max_length=150, null=True, blank=True, unique=False)
opensea_collection = models.URLField(max_length=150, null=True, blank=True, unique=True)
blockchain = models.CharField(max_length=50, blank=True,null=True)
slug = models.CharField(max_length=150, null=True, blank=True, unique=False)
nft = models.BooleanField(default=True)
creator_royalty_percentage = models.FloatField(null=True, blank=True)
def __str__(self):
return str(self.address)
class ContractMetric(models.Model):
contract = models.ForeignKey(Contract, on_delete=models.CASCADE)
collection_name = models.CharField(max_length=200, null=True, blank=True)
capture_date = models.DateField(null=False,blank=True)
...
def __str__(self):
return str(self.contract)
Where am i going wrong?
Thanks
Tom
