SQL Expressions

hypequery provides a way to use raw SQL expressions and ClickHouse functions in your queries while maintaining type safety.

Raw SQL Expressions

You can use the raw and rawAs functions to include raw SQL expressions in your queries:

import { raw, rawAs } from '@hypequery/clickhouse';

// Using raw SQL expression
const query = builder
  .table('events')
  .select([
    'id',
    'user_id',
    rawAs('COUNT(*)', 'total_count') // SQL function with alias
  ])
  .groupBy(['id', 'user_id'])
  .toSQL();

// Result: SELECT id, user_id, COUNT(*) AS total_count FROM events GROUP BY id, user_id

ClickHouse Functions

hypequery provides several helper functions for common ClickHouse operations:

import { 
  toDateTime,
  formatDateTime,
  toStartOfInterval,
  datePart
} from '@hypequery/clickhouse';

const query = builder
  .table('events')
  .select([
    'id',
    'event_type',
    toDateTime('timestamp', 'event_time'),            // toDateTime(timestamp) AS event_time
    formatDateTime('timestamp', 'Y-m-d', 'date'),     // formatDateTime(timestamp, 'Y-m-d') AS date
    toStartOfInterval('timestamp', '1 hour', 'hour')  // toStartOfInterval(timestamp, INTERVAL 1 hour) AS hour
  ])
  .where('event_type', 'eq', 'purchase')
  .groupBy(['event_type', 'hour'])
  .toSQL();

Custom Functions

You can use raw and rawAs for any custom ClickHouse function:

// Using custom ClickHouse functions
const query = builder
  .table('events')
  .select([
    rawAs('arrayJoin(tags)', 'tag'),            // arrayJoin extraction
    rawAs('sum(revenue)', 'total_revenue'),     // Aggregation
    rawAs('round(avg(duration), 2)', 'avg_duration') // Multiple functions
  ])
  .toSQL();

Type Handling

When using SQL expressions, the resulting value will be typed as any in the return type. You can use TypeScript assertions if you need more specific typing:

const result = await builder
  .table('events')
  .select([
    'id',
    rawAs('toDate(timestamp)', 'date')
  ])
  .execute();

// TypeScript doesn't know the type of 'date', so you can assert it:
const dates = result.map(row => row.date as Date);

Usage in Aggregations

SQL expressions are particularly useful in aggregation scenarios:

const query = builder
  .table('events')
  .select([
    datePart('month', 'timestamp', 'month'),
    rawAs('sum(amount)', 'total_amount'),
    rawAs('count()', 'event_count')
  ])
  .groupBy(['month'])
  .orderBy('month')
  .toSQL();

// Result: SELECT toMonth(timestamp) AS month, sum(amount) AS total_amount, count() AS event_count FROM events GROUP BY month ORDER BY month ASC