Help Needed: Generating Month Ranges with django-cte - Recursive CTE Implementation for a Model

[aashayamballi]

I'm trying to generate a series of month ranges using django-cte based on my Cost model. Here's what I have so far:

from django.db import models
from django_cte import CTEManager

class Cost(models.Model):
    start_date = models.DateField(null=True)
    end_date = models.DateField(null=True)
    cost = models.DecimalField(max_digits=10, decimal_places=2, null=True)

    objects = CTEManager()

My goal is to generate month ranges from the minimum start_date to the maximum end_date in the Cost table. For example, if I have data like this:

start_date  | end_date    | cost
2024-03-15  | 2024-04-20  | 500.00
2024-04-01  | 2024-04-30  | 300.00
2024-05-15  | 2024-07-10  | 400.00

I want to generate month ranges like:

2024-03-01
2024-04-01
2024-05-01
2024-06-01
2024-07-01

I've tried using SQL with a recursive CTE:

WITH RECURSIVE month_ranges AS (
    SELECT 
        DATE_TRUNC('month', MIN("start_date"))::date AS month_start,
        DATE_TRUNC('month', MAX("end_date"))::date AS max_month_end
    FROM cost
    UNION ALL
    SELECT 
        (month_start + INTERVAL '1 month')::date,
        (max_month_end)::date
    FROM month_ranges
    WHERE (month_start + INTERVAL '1 month')::date <= max_month_end
)

select month_start from month_ranges;

This SQL approach works, but I'm struggling to translate it to django-cte. I've tried using Django's aggregation to get the initial month_start and max_month_end:

Cost.objects.annotate(
    month_start=TruncMonth(Min('start_date')),
    max_month_end=TruncMonth(Max('end_date'))
)

However, when I try to use this with the With class from django-cte, I encounter errors.

Could someone please help me implement this month range generation using django-cte? Any guidance or examples would be greatly appreciated.

[millerdev]

What error(s) are you getting? And what version of Django are you using?

[aashayamballi]

Thank you for your response. I apologize for not providing more specific information initially. To answer your questions:

What error(s) are you getting?

I'm not currently experiencing a specific error, as I've been exploring different approaches to achieve the results none of them were working.

what version of Django are you using

I'm using Django 4.2.

My main question is actually about using django-cte to generate a series or range of data. Most of the examples in the documentation focus on self-referencing foreign keys, but I'm looking to create something more like a sequence of numbers or dates without writing raw SQL.

For instance, I'd like to generate a series of dates that I can use in a CTE as provided in the description. Is there a way to achieve this using django-cte? If so, could you provide some guidance or point me toward relevant examples?

My goal is to avoid writing raw SQL while still leveraging django-cte for this type of data generation. Any insights or suggestions would be greatly appreciated.