Nested Query Based on The Select Related in Django Models-CodePudding

I have two models where the second has a dependency on the first. The Structure is as follows.

class Crop(models.Model):
    name = models.CharField(max_length=100, unique=True)
    description = models.TextField(null=True, blank=True)

the other is

class Plotting(models.Model):
    crop = models.ForeignKey(
        Crop, on_delete=models.CASCADE, limit_choices_to={"is_active": True}
    )

    sowing_date = models.DateField(null=True, blank=True)
    harvesting_date = models.DateField(null=True, blank=True)

    acre = models.DecimalField(
        max_digits=10, decimal_places=2, default=0, help_text="Area in Acres"
    )

I want to fetch the data in the following format.

CROP NAME | JAN | FEB | MAR | APR | MAY
crop 1    | 20  | 10  | 35  | 45  | 35 
crop 2    | 20  | 10  | 35  | 45  | 35
crop 3    | 20  | 10  | 35  | 45  | 35
crop 4    | 20  | 10  | 35  | 45  | 35

CROP 1 JAN defines the sum of acres harvested in January based on the harvesting_date. I also want to filter with dates and exclude the crops whose value is the sum of acres is 0.

CodePudding user response：

Maybe queryset like this:

queryset = Crop.objects.values('name')
queryset = queryset.aggregate(harvesting_date_jan=Count('harvesting_date', filter=Q(harvesting_date__month=1)))
queryset = queryset.aggregate(harvesting_date_feb=Count('harvesting_date', filter=Q(harvesting_date__month=2)))
...

CodePudding user response：

one way to get the required data

p = Plotting.objects.all() # could filter for date and exclude acre == 0 
p = p.values('crop__name', 'harvesting_date__month').annotate(sum_of_acres=Sum('acre')).order_by('crop__name', harvesting_date__month')

in the template you could use regroup