Having

The having method allows you to filter grouped query results based on aggregated values. Unlike where which filters individual rows, having filters the results after grouping and aggregation.

Basic Usage

import { createQueryBuilder } from '@hypequery/clickhouse';

const db = createQueryBuilder<Schema>();

// Filter grouped results
const query = db
  .table('orders')
  .select(['user_id'])
  .sum('total', 'total_spent')
  .groupBy(['user_id'])
  .having('total_spent > 1000')
  .toSQL();
// Result: SELECT user_id, SUM(total) AS total_spent FROM orders GROUP BY user_id HAVING total_spent > 1000

Type Definitions

having(condition: string, parameters?: any[]): this

Parameters

• condition: The HAVING condition as a string (e.g., ‘total_spent > 1000’)
• parameters: Optional array of parameters for the condition (for parameterized queries)

Returns

Returns the query builder instance for method chaining.

Multiple Conditions

Multiple having() calls are automatically combined with AND operators:

.having('total_spent > 1000')
.having('order_count >= 5')
// Results in: HAVING total_spent > 1000 AND order_count >= 5

Examples

Basic Having Clause

// Filter users who spent more than $1000
const query = db
  .table('orders')
  .select(['user_id'])
  .sum('total', 'total_spent')
  .groupBy(['user_id'])
  .having('total_spent > 1000')
  .toSQL();
// Result: SELECT user_id, SUM(total) AS total_spent FROM orders GROUP BY user_id HAVING total_spent > 1000

Multiple Having Conditions

// Filter with multiple conditions (automatically combined with AND)
const query = db
  .table('orders')
  .select(['user_id'])
  .sum('total', 'total_spent')
  .count('id', 'order_count')
  .groupBy(['user_id'])
  .having('total_spent > 1000')
  .having('order_count >= 5')
  .toSQL();
// Result: SELECT user_id, SUM(total) AS total_spent, COUNT(id) AS order_count FROM orders GROUP BY user_id HAVING total_spent > 1000 AND order_count >= 5

Type Safety

The having method accepts string conditions, so TypeScript validation is limited:

interface OrderSchema {
  orders: {
    id: 'UInt32';
    user_id: 'UInt32';
    total: 'Float64';
    status: 'String';
  };
}

const db = createQueryBuilder<OrderSchema>();

// ✅ Valid having condition
const query = db
  .table('orders')
  .select(['user_id'])
  .sum('total', 'total_spent')
  .groupBy(['user_id'])
  .having('total_spent > 1000') // String condition
  .toSQL();

// ⚠️ No TypeScript validation for having conditions
// The condition is passed as a string, so errors won't be caught at compile time

Important Notes

• String-based conditions: All having conditions are passed as strings, so there’s no compile-time validation
• AND combination: Multiple having() calls are automatically combined with AND operators
• Parameter support: Use the parameters array for dynamic values to prevent SQL injection
• Complex conditions: For complex OR conditions, use a single having() call with the full condition string