Using Date function with queryset

Using Django
1

Hi All (probably Ken),

I’m creating a dashboard and one of the indicators I want to get is the change in the number of leads on a month-by-month basis, and I have a created_date field in my model, so what I want to do is to simply get a list of all the new leads created every month

I was reading the documentation and you can use the queryset API date to get the datefields,but I’m trying to use it and is currently only returning one value:

Leads.objects.all().dates('created_at','month')

I’m not sure why the query is only returning 1 value?

If I have a list with all the leads and the month, then I can account for the new leads every month.

class Leads(models.Model):

    project_id = models.BigAutoField(primary_key=True, serialize=False)
    created_at = models.DateTimeField(auto_now_add=True)
    expected_revenue = MoneyField(decimal_places=2,max_digits=14, default_currency='USD')
    expected_licenses = models.IntegerField()
    country = CountryField(blank_label='(select_country)')
    status = models.CharField(choices=[('Open', 'Open'), ('Closed', 'Closed'), ('Canceled', 'Canceled'),
                                       ('Idle', 'Idle')
                                       ], max_length=10)
    estimated_closing_date = models.DateField()
    services = models.CharField(choices=[('Illumination Studies', 'Illumination Studies'),
                                  ('Training', 'Training'),('Survey Design Consultancy', 'Survey Design Consultancy'),
                                  ('Software License', 'Software License'),
                                  ('Software Development','Software Development')], max_length=40)
    agent = models.ForeignKey(Profile, default='agent',on_delete=models.CASCADE)
    company = models.ForeignKey(Company,on_delete=models.CASCADE)
    point_of_contact = models.ForeignKey(Client, default='agent',on_delete=models.CASCADE)
    updated_at = models.DateTimeField(auto_now=True)
    application = models.CharField(choices=[('O&G','O&G'),('Renewables','Renewables'),('Mining','Mining'),
                                         ('Other','Other'),('CSS','CSS')],
                                default='O&G',max_length=20)
    sub_category = models.CharField(choices=[('Wind','Wind'),('Geo-Thermal','Geo-Thermal'),('Solar','Solar'),
                                             ('Tidal','Tidal')], max_length=20, blank=True)

    @property
    def age_in_days(self):
        today = date.today()
        result = self.estimated_closing_date - today
        return result.days

    def __str__(self):
        return f'{self.project_id}'


```
2

I’m not sure this is doing what you think it’s going to do.

To be clear - it’s not getting the month for every created_at field in the table, it’s getting a list of all the unique months. If all rows have a created_at in the same month, you’re only going to get one value.

I get the impression that what you’re really looking to do is annotate each row with the month from the date - a different operation.

3

:joy:

FYI .all() is redundant. QuerySets always operate on all rows until you filter.

Indeed. I think you want TruncMonth:

Lead.objects.annotate(created_at_month=TruncMonth('created_at'))

FYI from Django 3.0 you can use enumeration types for a better experience - see my post.

4

Thanks Adam,

That is exactly what I want it to get:

test = Leads.objects.annotate(created_at_month=TruncMonth('created_at'))
    for i in test:
        print(i.created_at_month.month)
5

Thanks a lot to all, this is what I really want it to get, a simply count of how many items are created per month

Leads.objects.annotate(created_at_month=TruncMonth('created_at')).values('created_at_month').annotate(count=Count('expected_revenue'))

I’m pretty impressed with the power of the querysets

Thanks a lot !