Function createGroupByTests

packages/db/tests/query/group-by.test.ts:225–2237

(autoIndex: `off` | `eager`)

}

function createGroupByTests(autoIndex: `off` | `eager`): void {
  describe(`with autoIndex ${autoIndex}`, () => {
    describe(`Single Column Grouping`, () => {
      let ordersCollection: ReturnType<typeof createOrdersCollection>

      beforeEach(() => {
        ordersCollection = createOrdersCollection(autoIndex)
      })

      test(`group by customer_id with aggregates`, () => {
        const customerSummary = createLiveQueryCollection({
          startSync: true,
          query: (q) =>
            q
              .from({ orders: ordersCollection })
              .groupBy(({ orders }) => orders.customer_id)
              .select(({ orders }) => ({
                customer_id: orders.customer_id,
                total_amount: sum(orders.amount),
                order_count: count(orders.id),
                avg_amount: avg(orders.amount),
                min_amount: min(orders.amount),
                max_amount: max(orders.amount),
                min_date: min(orders.date),
                max_date: max(orders.date),
              })),
        })

        expect(customerSummary.size).toBe(3) // 3 customers

        // Customer 1: orders 1, 2, 7 (amounts: 100, 200, 400)
        const customer1 = customerSummary.get(1)
        expect(customer1).toBeDefined()
        expect(customer1?.customer_id).toBe(1)
        expect(customer1?.total_amount).toBe(700)
        expect(customer1?.order_count).toBe(3)
        expect(customer1?.avg_amount).toBe(233.33333333333334) // (100+200+400)/3
        expect(customer1?.min_amount).toBe(100)
        expect(customer1?.max_amount).toBe(400)
        expect(customer1?.min_date.toISOString()).toBe(
          new Date(`2023-01-01`).toISOString(),
        )
        expect(customer1?.max_date.toISOString()).toBe(
          new Date(`2023-03-01`).toISOString(),
        )

        // Customer 2: orders 3, 4 (amounts: 150, 300)
        const customer2 = customerSummary.get(2)
        expect(customer2).toBeDefined()
        expect(customer2?.customer_id).toBe(2)
        expect(customer2?.total_amount).toBe(450)
        expect(customer2?.order_count).toBe(2)
        expect(customer2?.avg_amount).toBe(225) // (150+300)/2
        expect(customer2?.min_amount).toBe(150)
        expect(customer2?.max_amount).toBe(300)
        expect(customer2?.min_date.toISOString()).toBe(
          new Date(`2023-01-20`).toISOString(),
        )