CODEWITHSUNDEEP
pythondjangocommon-table-expressiondjango-ormdjango-annotate
TechSavy
TechSavy

Reputation: 1340

Django-cte gives: 'QuerySet' object has no attribute 'with_cte'

I have records in below format:

| id  | name |       created                  |
-----------------------------------------------
|1    | A    |2024-04-10T02:49:47.327583-07:00|
|2    | A    |2024-04-01T02:49:47.327583-07:00|
|3    | A    |2024-03-01T02:49:47.327583-07:00|
|4    | A    |2024-02-01T02:49:47.327583-07:00|
|5    | B    |2024-02-01T02:49:47.327583-07:00|

Model:

class Model1(model.Models):
   name = models.CharField(max_length=100)
   created = models.DateTimeField(auto_now_add=True)

I want to perform a group by in django with month from field created and get latest record from that month.

Expected output:

| id  | name |       created                  |
-----------------------------------------------
|1    | A    |2024-04-10T02:49:47.327583-07:00|
|3    | A    |2024-03-01T02:49:47.327583-07:00|
|4    | A    |2024-02-01T02:49:47.327583-07:00|

I am using django-cte to perform the above action

from django.db.models.functions import DenseRank, ExtractMonth
from django_cte import With
m = Model1.objects.get(id=1)
cte = With(
        Model1.objects.filter(name=m.name)
          rank=Window(
            expression=DenseRank(),
            partition_by=[ExtractMonth("created")],
            order_by=F("created").desc(),
        )
     )
qs = cte.queryset().with_cte(cte).filter(rank=1)

But the above give error:

qs = cte.queryset().with_cte(cte).filter(rank=1)
                    ^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'QuerySet' object has no attribute 'with_cte'

Please help!

Upvotes: 3

Views: 270

Answers (2)

Sunderam Dubey
Sunderam Dubey

Reputation: 8837

You can also explicitly defines the CTEs in the query.

At first annotate each record with the month extracted from the created field and rank them within each month using the DenseRank() function. Then, filter the queryset to get the records with rank 1, which corresponds to the latest record for each month.

Try this:

from django.db.models import Window, F
from django.db.models.functions import DenseRank, ExtractMonth
from django_cte import With


class Model1(models.Model):
    name = models.CharField(max_length=100)
    created = models.DateTimeField(auto_now_add=True)


cte = With(
    Model1.objects.annotate(
        month=ExtractMonth("created"),
        rank=Window(
            expression=DenseRank(),
            partition_by=[ExtractMonth("created")],
            order_by=F("created").desc(),
        ),
    )
)


latest_records = Model1.objects.annotate(
    month=ExtractMonth("created"),
    rank=Window(
        expression=DenseRank(),
        partition_by=[ExtractMonth("created")],
        order_by=F("created").desc(),
    ),
).filter(rank=1)

for record in latest_records:
    print(record.id, record.name, record.created)

Upvotes: 1

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477533

You need to mix in the CTEManager, otherwise you get a "vanilla" QuerySet:

from django_cte import CTEManager


class Model1(model.Models):
    name = models.CharField(max_length=100)
    created = models.DateTimeField(auto_now_add=True)
    objects = CTEManager()

Upvotes: 3

Related Questions