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")

that's a pretty complex query. have you printed it out so you can see the actual sql being executed? — Jason
– Jason, Commented Dec 26, 2017 at 14:40
I have recently answered the similar question . Hope you will found this useful check out this. stackoverflow.com/questions/47977653/… — Akshay Kathpal
– Akshay Kathpal, Commented Dec 26, 2017 at 15:14
@babygame0ver unfortunately it is not complete answer for my quetion — Vassily
– Vassily, Commented Dec 26, 2017 at 15:19
Are you able to execute the query in your db shell with no issues? — Jason
– Jason, Commented Dec 26, 2017 at 15:52

arielnmz · Accepted Answer · 2017-12-26 16:55:40Z

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.

Collectives™ on Stack Overflow

How to annotate queryset with another queryset

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related