sqlbuilder.py

joins = (Sale.s & Product.s).on(Sale.s.product == Product.s.pk)

where = (Product.s.company_id == company_id) & \
   (Sale.s.created_at.between(func.date(start_date), func.date(end_date)))

inner_table = Q()\
        .tables(
            (joins + CategoryAssignment.s).on(CategoryAssignment.s.product == Product.s.pk)
        )\
        .fields(
            CategoryAssignment.s.category_id.as_('cat_id'),
            func.date(Sale.s.created_at).as_('date'),
            func.coalesce(func.sum(Sale.s.quantity), 0).as_('quantity')
        )\
        .where(where) \
        .group_by(func.date(Sale.s.created_at), CategoryAssignment.s.category_id)\
        .as_table('g')

    category_quantity_breakdown_q = Q()\
        .tables(inner_table)\
        .fields(
            inner_table.cat_id,
            func.json_agg(inner_table).as_('days')
        )\
        .group_by(inner_table.cat_id)
        
print(category_quantity_breakdown_q)

# generated SQL + escaped params:
<Query:
SELECT "g"."cat_id",
       JSON_AGG("g") AS "days"
FROM
  (SELECT "sales_categoryassignment"."category_id" AS "cat_id",
          DATE("sales_sale"."created_at") AS "date",
          COALESCE(SUM("sales_sale"."quantity"), %s) AS "quantity"
   FROM "sales_sale"
   INNER JOIN "sales_product" ON ("sales_sale"."product_id" = "sales_product"."id")
   LEFT OUTER JOIN "sales_categoryassignment" ON ("sales_categoryassignment"."product_id" = "sales_product"."id")
   WHERE "sales_product"."company_id" = %s
     AND "sales_sale"."created_at" BETWEEN DATE(%s) AND DATE(%s)
   GROUP BY DATE("sales_sale"."created_at"),
            "sales_categoryassignment"."category_id") AS "g"
GROUP BY "g"."cat_id",
         [0, 3, datetime.datetime(2017, 10, 20, 0, 0), datetime.datetime(2017, 11, 26, 0, 0)]>