Advanced Filtering

hypequery exposes powerful filtering controls directly inside defineServe. Use fluent where clauses for simple logic or the CrossFilter helper when you need nested groups, reusable predicates, or top-N shortcuts.

Basic Filtering

Chain multiple where calls inside any query definition. Every predicate stays type-safe thanks to the schema you generated via hypequery init.

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

export const api = defineServe({
  queries: {
    activeAdultUsers: {
      query: ({ ctx }) =>
        ctx.db
          .table('users')
          .where('age', 'gt', 18)
          .where('status', 'eq', 'active'),
    },
  },
});

Complex Filtering with CrossFilter

CrossFilter lets you compose nested AND/OR groups, reuse common predicates, and share filter trees across queries.

Basic CrossFilter usage

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

const ordersFilter = 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',
  );

export const api = defineServe({
  queries: {
    filteredOrders: {
      query: ({ ctx }) => ctx.db.table('orders').applyCrossFilters(ordersFilter),
    },
  },
});

FilterConditionInput interface

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

• column: Column reference (local table or joined tables).
• operator: Comparison operator (see table below).
• value: The literal/array passed to that operator.
• conjunction: Optional logical operator to connect the condition to its siblings.

Supported operators

FilterGroup interface

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

• operator: Controls how nested conditions combine.
• conditions: Array of conditions or deeper groups.
• limit: Optional row cap for the subquery powering the filter.
• orderBy: Sort criteria for that subquery.

CrossFilter methods

Core helpers

• add(condition: FilterConditionInput) – append a single predicate.
• addMultiple(conditions: FilterConditionInput[]) – append multiple conditions.
• addGroup(conditions, operator) – create nested AND/OR blocks.
• getConditions() – inspect the current filter tree (useful for tests or logging).

const filter = new CrossFilter()
  .add({ column: 'status', operator: 'eq', value: 'active' })
  .addMultiple([
    { column: 'age', operator: 'gte', value: 18 },
    { column: 'age', operator: 'lte', value: 65 },
  ])
  .addGroup([
    { column: 'price', operator: 'gte', value: 100 },
    { column: 'price', operator: 'lte', value: 500 },
  ], 'AND');

const conditions = filter.getConditions();

Advanced helpers

• topN(valueColumn, n, orderBy) – builds a filter targeting the top N by a metric.

const filter = new CrossFilter().topN('revenue', 10, 'desc');

Date filtering patterns

Using date-fns

import { CrossFilter, defineServe } from '@hypequery/clickhouse';
import { endOfDay, endOfMonth, startOfDay, startOfMonth, subDays } from 'date-fns';

export const api = defineServe({
  queries: {
    todaysOrders: {
      query: ({ ctx }) => {
        const today = new Date();
        const filter = new CrossFilter().add({
          column: 'created_at',
          operator: 'between',
          value: [startOfDay(today).toISOString(), endOfDay(today).toISOString()],
        });
        return ctx.db.table('orders').applyCrossFilters(filter);
      },
    },
    lastSevenDays: {
      query: ({ ctx }) => {
        const today = new Date();
        const filter = new CrossFilter().add({
          column: 'created_at',
          operator: 'between',
          value: [subDays(today, 7).toISOString(), today.toISOString()],
        });
        return ctx.db.table('orders').applyCrossFilters(filter);
      },
    },
    thisMonth: {
      query: ({ ctx }) => {
        const today = new Date();
        const filter = new CrossFilter().add({
          column: 'created_at',
          operator: 'between',
          value: [startOfMonth(today).toISOString(), endOfMonth(today).toISOString()],
        });
        return ctx.db.table('orders').applyCrossFilters(filter);
      },
    },
  },
});

Using dayjs

import dayjs from 'dayjs';
import { CrossFilter, defineServe } from '@hypequery/clickhouse';

export const api = defineServe({
  queries: {
    dayJsToday: {
      query: ({ ctx }) => {
        const today = dayjs();
        const filter = new CrossFilter().add({
          column: 'created_at',
          operator: 'between',
          value: [today.startOf('day').toISOString(), today.endOf('day').toISOString()],
        });
        return ctx.db.table('events').applyCrossFilters(filter);
      },
    },
    dayJsLast30: {
      query: ({ ctx }) => {
        const today = dayjs();
        const filter = new CrossFilter().add({
          column: 'created_at',
          operator: 'between',
          value: [today.subtract(30, 'day').toISOString(), today.toISOString()],
        });
        return ctx.db.table('events').applyCrossFilters(filter);
      },
    },
  },
});

Next Steps

Continue: Join Relationships - Define reusable table relationships

Or explore: Query Definitions - Build APIs with serve