Grouping in Heap

Heap allows you to group data using aggregate functions and various grouping methods such as select, group, having, and limit. Let's look at how to do this with an example.

Example of a Grouping Query

Creating a Table

First, we create the Orders table:

const Orders = Heap.Table('hql-orders', {
  total: Heap.Money(),
  product: Heap.Optional(Heap.RefLink(Products)),
  count: Heap.Integer({ minimum: 1 }),
})

The table stores orders with a total amount total, a reference to the product product, and the quantity of ordered units count.

Grouping Data

Next, we create a view that groups orders by products and calculates aggregates:

const SalesByProductView = Orders.select({
  productId: 'product',
  totalIncome: { $sum: [['total', 'amount']] },
  totalSoldCount: { $sum: ['count'] },
  orderCount: { $count: ['*'] },
})
  .where({
    product: {
      $in: Products.select('id').where({ price: { amount: { $lt: 800 } } }),
    },
  })
  .group('productId')
  .having({ orderCount: { $gte: 2 } })
  .order({ orderCount: 'desc' })
  .limit(25)
  .asView()

1. Selecting Data for Grouping:

   • productId: Product identifier.
   • totalIncome: Total income from product sales.
   • totalSoldCount: Total number of units sold.
   • orderCount: Total number of orders.

2. Filtering where({ price: { amount: { $lt: 800 } } }): Only products with a price less than 800 are considered.

3. Grouping group('productId'): Data is grouped by productId.

4. Having having({ orderCount: { $gte: 2 } }): Only groups with an order count of at least 2 are included.

5. Sorting order({ orderCount: 'desc' }): Results are sorted by order count in descending order.

6. Limiting limit(25): The first 25 records are selected from the results.

Result of Grouping

Conclusion

Grouping in Heap is performed using the methods select, group, having, and others. This allows for efficient aggregation and analysis of data while applying conditions and sorting.