python - Collect ids as list during annotate grouping by different parameters in Django

Question

I have to collect distinct vehicle_type_ids while annotating in django .

This is what I have right now along with grouping by city_code and final_driver_owner_contact. I wish to collect all corresponding vehicle_types grouped by them (city_code, final_driver_owner_contact) and month.

od_engagement = list(FieldOpsBooking.objects.using(
    'analytics').exclude(**exclude_query).filter(**filter_query).values(
    'city_code', 'final_driver_owner_contact').annotate(
    booking_count=Count('final_driver_owner_contact'),
    vehicle_count=Count('final_driver_vehicle_no', distinct=True),
    total_cost=Sum('final_driver_rate'),
    month=TruncMonth('pick_up_at')))

Is it possible to add it to this query (similar to mongodb which lets you push ids while grouping in aggregate.

Or is there another way to get the same using a separate query.

Any help is appreciated.

Answer 1

Since I have been using postgresql alongside Django . This worked for me without the distinct part.

There are plenty of articles on Group Concat, haven't found anything to work.

Here is the solution only for Postgres

Using StringAgg('vehicle_type_id', ',')

from django.contrib.postgres.aggregates import StringAgg

od_engagement = list(FieldOpsBooking.objects.using(
    'analytics').exclude(**exclude_query).filter(**filter_query).values(
    'city_code', 'final_driver_owner_contact').annotate(
    vehicle_types = StringAgg('vehicle_type_id', ','),
    booking_count=Count('final_driver_owner_contact'),
    vehicle_count=Count('final_driver_vehicle_no', distinct=True),
    total_cost=Sum('final_driver_rate'),
    month=TruncMonth('pick_up_at')))