Advanced Filtering

hypequery provides powerful filtering capabilities through the CrossFilter class and direct query methods. This guide covers all the filtering options available.

Basic Filtering

Use the where method for simple conditions:

const results = await db
  .table('users')
  .where('age', 'gt', 18)
  .where('status', 'eq', 'active')
  .execute();

Complex Filtering with CrossFilter

The CrossFilter class provides more advanced filtering capabilities with support for nested groups and complex conditions.

Basic CrossFilter Usage

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

const filter = new CrossFilter()
  .add({
    column: 'status',
    operator: 'in',
    value: ['active', 'pending']
  })
  .addGroup([
    {
      column: 'created_at',
      operator: 'gte',
      value: new Date('2023-01-01')
    },
    {
      column: 'total',
      operator: 'gt',
      value: 1000
    }
  ], 'OR');

const results = await db
  .table('orders')
  .applyCrossFilters(filter)
  .execute();

CrossFilter Properties

FilterConditionInput Interface

Each filter condition uses the FilterConditionInput interface:

interface FilterConditionInput<T = any, Schema = any, OriginalT = any> {
  column: keyof OriginalT | TableColumn<Schema>;
  operator: FilterOperator;
  value: T;
  conjunction?: 'AND' | 'OR';
}

Properties:

• column: The column name to filter on (supports cross-table references)
• operator: The comparison operator (see supported operators below)
• value: The value to compare against
• conjunction: Optional logical operator (‘AND’ or ‘OR’) for combining conditions

Supported Operators

FilterGroup Interface

For nested filter groups:

interface FilterGroup<Schema = any, OriginalT = any> {
  operator: 'AND' | 'OR';
  conditions: Array<FilterConditionInput | FilterGroup>;
  limit?: number;
  orderBy?: {
    column: keyof OriginalT;
    direction: 'ASC' | 'DESC';
  };
}

Properties:

• operator: Logical operator to combine conditions (‘AND’ or ‘OR’)
• conditions: Array of filter conditions or nested groups
• limit: Optional limit for the number of results
• orderBy: Optional ordering configuration

CrossFilter Methods

Core Methods

add(condition: FilterConditionInput)

Adds a single filter condition:

filter.add({
  column: 'status',
  operator: 'eq',
  value: 'active'
});

addMultiple(conditions: FilterConditionInput[])

Adds multiple filter conditions at once:

filter.addMultiple([
  { column: 'status', operator: 'eq', value: 'active' },
  { column: 'age', operator: 'gte', value: 18 }
]);

addGroup(conditions: Array<FilterConditionInput | FilterGroup>, operator: 'AND' | 'OR')

Adds a nested group of conditions:

filter.addGroup([
  { column: 'price', operator: 'gte', value: 100 },
  { column: 'price', operator: 'lte', value: 500 }
], 'AND');

getConditions(): FilterGroup

Returns the current filter tree:

const conditions = filter.getConditions();
console.log(conditions);

Advanced Methods

topN(valueColumn, n, orderBy)

Creates a filter for top N records:

filter.topN('revenue', 10, 'desc'); // Top 10 by revenue

Date Filtering Patterns

Using date-fns

import { startOfDay, endOfDay, subDays, startOfMonth, endOfMonth } from 'date-fns';

// Today's range
const today = new Date();
const todayStart = startOfDay(today);
const todayEnd = endOfDay(today);

const filter = new CrossFilter()
  .add({
    column: 'created_at',
    operator: 'between',
    value: [todayStart.toISOString(), todayEnd.toISOString()]
  });

// Last 7 days
const sevenDaysAgo = subDays(today, 7);
const filter = new CrossFilter()
  .add({
    column: 'created_at',
    operator: 'between',
    value: [sevenDaysAgo.toISOString(), today.toISOString()]
  });

// This month
const monthStart = startOfMonth(today);
const monthEnd = endOfMonth(today);
const filter = new CrossFilter()
  .add({
    column: 'created_at',
    operator: 'between',
    value: [monthStart.toISOString(), monthEnd.toISOString()]
  });

Using dayjs

import dayjs from 'dayjs';

// Today's range
const today = dayjs();
const filter = new CrossFilter()
  .add({
    column: 'created_at',
    operator: 'between',
    value: [
      today.startOf('day').toISOString(),
      today.endOf('day').toISOString()
    ]
  });

// Last 30 days
const thirtyDaysAgo = dayjs().subtract(30, 'day');
const filter = new CrossFilter()
  .add({
    column: 'created_at',
    operator: 'between',
    value: [
      thirtyDaysAgo.toISOString(),
      today.toISOString()
    ]
  });

Complex Examples

Nested Filter Groups

const filter = new CrossFilter()
  .add({ column: 'status', operator: 'eq', value: 'active' })
  .addGroup([
    {
      column: 'category',
      operator: 'in',
      value: ['electronics', 'books']
    },
    {
      column: 'price',
      operator: 'between',
      value: [50, 200]
    }
  ], 'AND')
  .addGroup([
    {
      column: 'created_at',
      operator: 'gte',
      value: new Date('2024-01-01')
    },
    {
      column: 'rating',
      operator: 'gte',
      value: 4.0
    }
  ], 'OR');

Date-Based Filtering with date-fns

import { startOfMonth, endOfMonth, subDays } from 'date-fns';

const today = new Date();
const monthStart = startOfMonth(today);
const monthEnd = endOfMonth(today);
const sevenDaysAgo = subDays(today, 7);

const filter = new CrossFilter()
  .add({
    column: 'created_at',
    operator: 'between',
    value: [monthStart.toISOString(), monthEnd.toISOString()]
  })
  .add({
    column: 'updated_at',
    operator: 'between',
    value: [sevenDaysAgo.toISOString(), today.toISOString()]
  });

Top N with Date Filtering

import { subDays } from 'date-fns';

const thirtyDaysAgo = subDays(new Date(), 30);

const filter = new CrossFilter()
  .add({
    column: 'created_at',
    operator: 'between',
    value: [thirtyDaysAgo.toISOString(), new Date().toISOString()]
  })
  .topN('revenue', 10, 'desc');

Cross-Table Filtering

const filter = new CrossFilter()
  .add({ column: 'users.status', operator: 'eq', value: 'active' })
  .add({ column: 'orders.total', operator: 'gt', value: 1000 });

Type Safety

CrossFilter provides full TypeScript support when initialized with a schema:

interface Schema {
  users: {
    id: 'UInt32';
    name: 'String';
    age: 'UInt32';
    status: 'String';
    created_at: 'DateTime';
  };
  orders: {
    id: 'UInt32';
    user_id: 'UInt32';
    total: 'Float64';
    status: 'String';
  };
}

const filter = new CrossFilter<Schema, 'users'>(schema);
// Now you get type checking for column names and values