Count distinct values of Subquery

I've got these models:

class Component(models.Model)
    name = models.CharField(max_length=50)

class ComponentAttribute(models.Model)
    component   = models.ForeignKey(Component)
    context     = models.ForeignKey(Context)
    subcategory = models.ForeignKey(SubCategory)
    material    = models.ForeignKey(Material)
    measurement = models.ForeignKey(Measurement)
    value       = models.FloatField()

I want to annotate the count of unique attributes to each component in a QuerySet. A attribue is unique when it has a unique combination of context, subcategory and material for a given component.

Based on other posts here and the Django documentation I craftet a Query based on a subquery.

# Create a subquery that counts the distinct combinations
float_attributes_count = Subquery(
        ComponentAttributeFloat.objects
        .filter(component=OuterRef('pk'))
        .order_by(
            'subcategory_id',
            'context_id',
            'material_id')
        .distinct(
            'subcategory_id',
            'context_id',
            'material_id')
        .values("component__id")
        .annotate(count=Count('id'))
        .values('count'))

components = Component.objects.annotate(
    unique_attributes=float_attributes_count
)

When I now try to equate that query with this basic loop to print my result.

for component in components:
    print(f'{component}: {component.unique_attributes}')

I get hit with this error:

NotImplementedError: annotate() + distinct(fields) is not implemented.

It looks like the combination of requirements I have lead to an query that is unexecutable and I can't find examples that check all the boxes for me. Can anyone help me with a working query using an other path? (For refrence I use a PostgreSQL database which should support this distinct statement see: https://docs.djangoproject.com/en/5.2/ref/models/querysets/#django.db.models.query.QuerySet.distinct)

You can perhaps count the combinations of the values, like:

from django.db.models import CharField, Value
from django.db.models.functions import Cast, Concat

sep = Value(',')

float_attributes_count = Subquery(
    ComponentAttributeFloat.objects.filter(component=OuterRef('pk'))
    .annotate(
        count=Count(
            Concat(
                Cast('subcategory_id', output_field=CharField()),
                sep,
                Cast('context_id', output_field=CharField()),
                sep,
                Cast('material_id', output_field=CharField()),
            ),
            distinct=True,
        )
    )
    .values('count')
)

components = Component.objects.annotate(unique_attributes=float_attributes_count)
Вернуться на верх