Django Group By Aggregation works until additional fields added

Question

I'd like to group the following track variations at the given circuit so on an index page I only have a single entry for each circuit. On a subsequent circuit detail page I will show the various configurations.

If I keep the query simple as below it works.

    track_listing = (Tracks.objects
                 .values('sku', 'track_name')
                 .annotate(variations=Count('sku'))
                 .order_by()
                 )

However adding other fields such as track_id or location breaks or changes the grouping.

        track_listing = (Tracks.objects
                 .values('sku', 'track_name', 'track_id')
                 .annotate(variations=Count('sku'))
                 .order_by()
                 )

Is there a way to keep the group while including other fields. The location is not unique to each row and having one example of track_id allows me to retrieve a track image.

Answer 1

One way to resolve this issue is to group the results by the sku and track_name fields, and use a separate query to retrieve the track_id and other details for each track variation, like the following:

# First query to retrieve the track variations
track_variations = (Tracks.objects
                    .values('sku', 'track_name')
                    .annotate(variations=Count('sku'))
                    .order_by())

# Loop through the track variations and retrieve additional details for each variation
for track in track_variations:
    # Second query to retrieve details for each track variation
    track_details = Tracks.objects.filter(sku=track['sku'], track_name=track['track_name']).values('track_id', 'location', 'other_fields')
    track['variations'] = list(track_details)