Where

The where method allows you to filter your query results based on specified conditions.

Use orWhere to create OR conditions between multiple filters.

Use whereGroup and orWhereGroup to create parenthesized groups of conditions.

Basic Usage

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

const db = createQueryBuilder<Schema>();

// Simple where clause
const query = db
  .table('users')
  .where('age', 'gte', 18)
  .toSQL();
// Result: SELECT * FROM users WHERE age >= 18

Type Definitions

where(column: keyof T | TableColumn<Schema>, operator: WhereOperator, value: any): this
where(condition: WhereExpression): this

orWhere(column: keyof T | TableColumn<Schema>, operator: WhereOperator, value: any): this

whereGroup(callback: (builder: this) => void): this
orWhereGroup(callback: (builder: this) => void): this

Parameters

• column: The column to filter on
• operator: Comparison operator (eq, neq, gt, gte, lt, lte, like, notLike, in, notIn, between)
• value: The value to compare against
• condition: A WhereExpression object
• callback: A function that receives a WhereBuilder for complex conditions

Returns

Returns the query builder instance for method chaining.

Comparison Operators

Examples

Simple Conditions

// Equal
db.table('users').where('status', 'eq', 'active')

// Greater than
db.table('users').where('age', 'gt', 18)

// Like pattern
db.table('users').where('name', 'like', '%john%')

// In array
db.table('users').where('status', 'in', ['active', 'pending'])

Multiple Conditions

// Chain multiple where clauses (AND)
const query = db
  .table('users')
  .where('age', 'gte', 18)
  .where('status', 'eq', 'active')
  .where('country', 'eq', 'US')
  .toSQL();
// Result: SELECT * FROM users WHERE age >= 18 AND status = 'active' AND country = 'US'

OR Conditions with orWhere

// Use orWhere for OR conditions
const query = db
  .table('users')
  .where('status', 'eq', 'active')
  .orWhere('role', 'eq', 'admin')
  .orWhere('email', 'like', '%@company.com')
  .toSQL();
// Result: SELECT * FROM users WHERE status = 'active' OR role = 'admin' OR email LIKE '%@company.com'

Mixing AND and OR Conditions

// Combine AND and OR conditions
const query = db
  .table('users')
  .where('age', 'gte', 18)
  .where('country', 'eq', 'US')
  .orWhere('role', 'eq', 'admin')
  .toSQL();
// Result: SELECT * FROM users WHERE age >= 18 AND country = 'US' OR role = 'admin'

Complex Conditions with Groups

// Use whereGroup for parenthesized AND conditions
const query = db
  .table('users')
  .whereGroup((builder) => {
    builder
      .where('age', 'gte', 18)
      .orWhere('parent_consent', 'eq', true);
  })
  .where('status', 'eq', 'active')
  .toSQL();
// Result: SELECT * FROM users WHERE (age >= 18 OR parent_consent = true) AND status = 'active'

Nested Groups

// Complex nested conditions with multiple groups
const query = db
  .table('orders')
  .whereGroup((builder) => {
    builder
      .where('status', 'eq', 'completed')
      .orWhereGroup((innerBuilder) => {
        innerBuilder
          .where('total', 'gte', 100)
          .orWhere('priority', 'eq', 'high');
      });
  })
  .toSQL();
// Result: SELECT * FROM orders WHERE (status = 'completed' OR (total >= 100 OR priority = 'high'))

Date and Time Filtering

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

// Filter by date range
const query = db
  .table('orders')
  .where('created_at', 'gte', toDateTime('2024-01-01'))
  .where('created_at', 'lt', toDateTime('2024-02-01'))
  .toSQL();
// Result: SELECT * FROM orders WHERE created_at >= toDateTime('2024-01-01') AND created_at < toDateTime('2024-02-01')

Array Operations

// Check if array contains value
const query = db
  .table('products')
  .where('tags', 'like', '%electronics%')
  .toSQL();
// Result: SELECT * FROM products WHERE tags LIKE '%electronics%'

// Check if value is in array
const query2 = db
  .table('users')
  .where('role', 'in', ['admin', 'moderator'])
  .toSQL();
// Result: SELECT * FROM users WHERE role IN ('admin', 'moderator')

Subqueries in Where Clauses

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

// Where with subquery
const query = db
  .table('users')
  .where('id', 'in', raw('(SELECT user_id FROM orders WHERE total > 1000)'))
  .toSQL();
// Result: SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000)

Joins with Where Conditions

// Where conditions on joined tables
const query = db
  .table('orders')
  .leftJoin('users', 'orders.user_id', 'users.id')
  .where('orders.status', 'eq', 'completed')
  .where('users.country', 'eq', 'US')
  .toSQL();
// Result: SELECT * FROM orders LEFT JOIN users ON orders.user_id = users.id WHERE orders.status = 'completed' AND users.country = 'US'

Advanced Patterns

Conditional Where Clauses

// Build dynamic where clauses
function buildUserQuery(filters: {
  minAge?: number;
  status?: string;
  country?: string;
}) {
  let query = db.table('users');
  
  if (filters.minAge) {
    query = query.where('age', 'gte', filters.minAge);
  }
  
  if (filters.status) {
    query = query.where('status', 'eq', filters.status);
  }
  
  if (filters.country) {
    query = query.where('country', 'eq', filters.country);
  }
  
  return query;
}

// Usage
const result = buildUserQuery({ minAge: 18, status: 'active' });

OR Conditions

// Multiple OR conditions using whereGroup
const query = db
  .table('users')
  .whereGroup((builder) => {
    builder
      .where('email', 'like', '%@gmail.com')
      .orWhere('email', 'like', '%@yahoo.com')
      .orWhere('email', 'like', '%@hotmail.com');
  })
  .toSQL();
// Result: SELECT * FROM users WHERE (email LIKE '%@gmail.com' OR email LIKE '%@yahoo.com' OR email LIKE '%@hotmail.com')

Advanced IN Operators

The where method supports advanced IN operators for ClickHouse, including distributed/global, tuple, subquery, and table reference forms.

Supported IN Operators

Supported IN operators:

• in
• notIn
• globalIn
• globalNotIn
• inSubquery
• globalInSubquery
• inTable
• globalInTable
• inTuple
• globalInTuple

Usage Examples

// Standard IN
query.where('status', 'in', ['active', 'pending']);

// GLOBAL IN
query.where('user_id', 'globalIn', [1, 2, 3]);

// IN with subquery
query.where('user_id', 'inSubquery', 'SELECT id FROM users WHERE status = "active"');

// IN with table reference
query.where('user_id', 'inTable', 'users');

// Tuple IN
query.where(['counter_id', 'user_id'], 'inTuple', [[34, 123], [101500, 456]]);

// GLOBAL Tuple IN
query.where(['counter_id', 'user_id'], 'globalInTuple', [[34, 123], [101500, 456]]);

Tuple IN Usage

• Pass an array of columns as the first argument, and an array of value tuples as the value.
• Example: where(['col1', 'col2'], 'inTuple', [[1,2],[3,4]])

Subquery/Table IN Usage

• Pass a string as the value (the subquery or table name).
• Example: where('user_id', 'inSubquery', 'SELECT id FROM users')
• Example: where('user_id', 'inTable', 'users')

Type Safety & Error Handling

• All IN operators are type-checked for column names and value types.
• Errors are thrown at runtime for invalid types (e.g., passing a string instead of an array for in).
• Tuple IN and subquery/table IN require special argument types as described above.

Type Safety

The where method provides full TypeScript support:

interface UserSchema {
  users: {
    id: 'UInt32';
    name: 'String';
    age: 'UInt8';
    email: 'String';
    status: 'String';
  };
}

const db = createQueryBuilder<UserSchema>();

// ✅ Type-safe column references
const query = db
  .table('users')
  .where('age', 'gte', 18) // TypeScript knows 'age' is a valid column
  .where('status', 'eq', 'active')
  .toSQL();

// ❌ TypeScript error for invalid column
const query2 = db
  .table('users')
  .where('invalid_column', 'eq', 'value') // TypeScript error
  .toSQL();