Fixing the Unique Parts Count Annotation

The error you're encountering (more than one row returned by a subquery used as an expression) occurs because a subquery used in an annotation must return exactly one row when used as an expression. Your union query is returning multiple rows (one for each unique part), which causes the error.

Solution: Use a Better Approach with Aggregation

Instead of using a union with subqueries, you can leverage Django's aggregation capabilities directly. Here's a more effective approach:

PYTHONCopied
from django.db.models import Count, Q

def annotate_machine_query_with_num_parts(machine_query):
    return machine_query.annotate(
        num_parts=Count(
            'engine_components__parts',
            filter=Q(engine_components__parts__is_active=True) |
                  Q(transmission_components__parts__is_active=True),
            distinct=True
        )
    )

However, this approach might not work correctly because it would still count parts separately from engine and transmission components. A better solution would be:

PYTHONCopied
from django.db.models import Count, OuterRef, Subquery, F

def annotate_machine_query_with_num_parts(machine_query):
    # Get all part IDs related to each machine through either engine or transmission components
    engine_parts = machine_query.model.objects.filter(
        id=OuterRef('id')
    ).values(
        'engine_components__parts'
    ).filter(
        engine_components__parts__is_active=True
    )
    
    transmission_parts = machine_query.model.objects.filter(
        id=OuterRef('id')
    ).values(
        'transmission_components__parts'
    ).filter(
        transmission_components__parts__is_active=True
    )
    
    # Combine the part IDs and count distinct values
    return machine_query.annotate(
        num_parts=Count(
            'engine_components__parts',
            filter=Q(engine_components__parts__is_active=True),
            distinct=True
        ) + Count(
            'transmission_components__parts',
            filter=Q(transmission_components__parts__is_active=True) & 
                  ~Q(transmission_components__parts__in=Subquery(engine_parts)),
            distinct=True
        )
    )

Alternative Approach Using Raw SQL

If the above solutions don't work for your specific case, you might need to use a raw SQL query with RawSQL:

PYTHONCopied
from django.db.models.expressions import RawSQL

def annotate_machine_query_with_num_parts(machine_query):
    # This SQL will count distinct part IDs across both relationships
    sql = """
    SELECT COUNT(DISTINCT p.id) 
    FROM part p
    JOIN component_parts cp ON p.id = cp.part_id
    JOIN component c ON cp.component_id = c.id
    WHERE (c.machine_engine_id = machine.id OR c.machine_transmission_id = machine.id)
    AND p.is_active = TRUE
    """
    
    return machine_query.annotate(
        num_parts=RawSQL(sql, [])
    )

Simplest Solution

The most straightforward approach might be to use a combination of Q objects and Count with distinct=True:

PYTHONCopied
from django.db.models import Count, Q

def annotate_machine_query_with_num_parts(machine_query):
    return machine_query.annotate(
        num_parts=Count(
            'engine_components__parts__id',
            distinct=True,
            filter=Q(engine_components__parts__is_active=True)
        ) + Count(
            'transmission_components__parts__id',
            distinct=True,
            filter=Q(
                transmission_components__parts__is_active=True,
                # Exclude parts already counted in engine components
                ~Q(transmission_components__parts__in=F('engine_components__parts'))
            )
        )
    )

The key is to ensure you're not double-counting parts that appear in both engine and transmission components, which is why we use the exclusion condition in the second count.