How to annotate queryset with another queryset

Question

Now I'm trying to build complex queryset that uses annotations with conditional related queries.

I have the following models:

class MenuItemCategory(CreateUpdateModel):
    name = models.CharField(max_length=255, blank=True, null=True)

class MenuItem(CreateUpdateModel):
    category = models.ForeignKey(MenuItemCategory, blank=True, null=True)
    name = models.CharField(max_length=255, blank=True, null=True)

class LineItem(models.Model):
    order = models.ForeignKey(Orders, blank=True, null=True)
    menu_item = models.ForeignKey(MenuItems, blank=True, null=True)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    quantity = models.DecimalField(max_digits=10, decimal_places=3)
    amount = models.DecimalField(max_digits=10, decimal_places=2)

class Order(CreateUpdateModel):
    waiter = models.ForeignKey(Employees, blank=True, null=True)
    guests_count = models.IntegerField(blank=True, null=True, default=0)
    closed_at = models.DateTimeField(blank=True, null=True, db_index=True)                       

class Employees(CreateUpdateModel):
    restaurant = models.ForeignKey(Restaurants, blank=True, null=True)
    name = models.CharField(max_length=255, blank=True, null=True)

My goal is to build json with following scheme:

[
    {
        employee_name: 'Jane',    
        menu_item_categories: [
            {
                name: 'Drinks',
                line_items_quantity: 10, //times when this waiter brings any item from this category to the customer at the period
                amount: 49.00, // price of all drinks sold by this waiter at the period
                menu_items: [
                    name: 'Vodka',
                    amount: 1.00, 
                    line_items_quantity: 4, # times when this item has been ordered for this waiter at the period
                ]
            }
        ],
        visits: 618,
        guests: 813,
        cycle_time: 363
    }
]

With following serializer:

class EmployeeSerializer(serializers.ModelSerializer):
    name = serializers.CharField(max_length=255)
    visits = serializers.SerializerMethodField()
    guests = serializers.SerializerMethodField()
    cycle_time = serializers.SerializerMethodField()
    menu_item_categories = serializers.SerializerMethodField()

    def get_visits(self, obj):
        # works

    def get_guests(self, obj):
        # works

    def get_cycle_time(self, obj):
        # works

    def get_menu_item_categories(self, obj):
        qs = MenuItemCategories.objects.annotate(
            line_items_quantity=Count('menuitems__lineitems__order',
                                      filter=Q(
                                          menuitems__lineitems__order__closed_at__range=self.context.get('period'),
                                          menuitems__lineitems__order__waiter=obj)
                                      ),
            amount=Sum('menuitems__lineitems__amount',
                       filter=Q(
                           menuitems__lineitems__order__closed_at__range=self.context.get('period'),
                           menuitems__lineitems__order__waiter=obj)
                       ),
            menu_items=Subquery(
                MenuItems.objects.filter(
                    lineitems__order__closed_at__range=self.context.get('period'),
                    lineitems__order__waiter=obj
                ).annotate(amount=Sum('lineitems__amount', filter=Q(lineitems__order__closed_at__range=self.context.get('period'),
                                                                    lineitems__order__waiter=obj)))
            )
        )
        return MenuItemCategorySerializer(qs, many=True).data

But when I try to build menu_item_categories value - it gives me an error: subquery must return only one column. As I understand, my goal is to annotate categories queryset with custom subquery and my trouble is that I don't understand how subquery works or I use incorrect toolkit to build orm query. So, how can I build this json with orm query and this serializer?

UPD

current query is

SELECT 
  "menu_item_categories"."id", "menu_item_categories"."created_at",
  "menu_item_categories"."updated_at", "menu_item_categories"."restaurant_id", 
  "menu_item_categories"."name", "menu_item_categories"."is_active",
COUNT("line_items"."order_id") AS "line_items_quantity", 
  (SELECT 
      U0."id", U0."created_at", U0."updated_at", 
      U0."restaurant_id", U0."category_id", U0."name", 
      SUM(U1."amount") AS "amount"
  FROM "menu_items" 
  U0 INNER JOIN "line_items" U1 
  ON (U0."id" = U1."menu_item_id") 
  INNER JOIN "orders" U2 
  ON (U1."order_id" = U2."id") 
  WHERE (
      U2."waiter_id" = 5 AND U2."closed_at" 
      BETWEEN 2017-12-20 14:19:16+00:00 AND 2017-12-26 14:19:16+00:00)
      GROUP BY U0."id") 
  AS "menu_items", 
  SUM("line_items"."amount") AS "amount" 
  FROM "menu_item_categories" 
  LEFT OUTER JOIN "menu_items" 
  ON ("menu_item_categories"."id" = "menu_items"."category_id") 
  LEFT OUTER JOIN "line_items" 
  ON ("menu_items"."id" = "line_items"."menu_item_id") 
  GROUP BY "menu_item_categories"."id", 
  (
      SELECT 
          U0."id", U0."created_at", 
          U0."updated_at", U0."restaurant_id", 
          U0."category_id", U0."name", SUM(U1."amount"
  ) AS "amount" 
  FROM "menu_items" U0 
  INNER JOIN "line_items" U1 
  ON (U0."id" = U1."menu_item_id") 
  INNER JOIN "orders" U2 
  ON (U1."order_id" = U2."id") 
  WHERE (U2."waiter_id" = 5 
         AND U2."closed_at" 
         BETWEEN 2017-12-20 14:19:16+00:00 
         AND 2017-12-26 14:19:16+00:00) 
  GROUP BY U0."id")

Answer 1

You can't get that kind of structure directly with one single query, simply because of how RDBMS's work. You can, however, get a big result with lots of redundant information, all the way from the simplest items so you can group data programatically to generate your json structure, or you can just do that in one step by iterating over your querysets:

t_range=self.context.get('period')
employees = Employees.objects.filter(order__closed_at__range=t_range) \
    .annotate(
        visits=Count(...),
        guests=Count(...),
        cycle_time=Sum(...),
    )

result = []

for employee in employees:
    menu_item_categories = MenuItemCategory.objects.filter(menuitem__lineitem__order__waiter=employee) \
        .annotate(
            line_items_quantity=Count(...),
            amount=Sum(...),
        )
    _cats = []
    for cat in menu_item_categories:
        menu_items = cat.menuitem_set.filter(order__waiter=employee) \
            .annotate(
                amount=Sum(...),
                line_items_quantity=Count(...),
            )
        _menu_items = []
        for menu_item in menu_items:
            _menu_item = {
                'name': menu_item.name,
                'amount': menu_item.amount,
                'line_items_quantity': menu_item.line_items_quantity,
            }
            _menu_items.append(_menu_item)

        _cats.append({
            'name': cat.name,
            'line_items_quantity': cat.line_items_quantity,
            'amount': cat.amount,
            'menu_items': _menu_items
        })

    result.append({
        'employee_name': employee.name,
        'visits': employee.visits,
        'guests': employee.guests,
        'cycle_time': employee.cycle_time,
        'menu_item_categories': _cats
    })

Sure, this will hit the database more than once so unless you prefer performance over this approach, this will do the trick.