Select

The select method allows you to specify which columns and expressions to include in your query results.

Basic Usage

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

const db = createQueryBuilder<Schema>();

// Select specific columns
const query = db
  .table('users')
  .select(['id', 'name', 'email'])
  .toSQL();
// Result: SELECT id, name, email FROM users

Type Definitions

select(columns: (keyof T | TableColumn<Schema> | SqlExpression | AliasedExpression)[]): this

Parameters

• columns: Array of columns or expressions to select
  • keyof T: Column names from the table type
  • TableColumn<Schema>: Cross-table column references
  • SqlExpression: Raw SQL expressions
  • AliasedExpression: SQL expressions with aliases

Returns

Returns the query builder instance for method chaining.

Examples

Select All Columns

// Select all columns from the table
const query = db
  .table('users')
  .select('*')
  .toSQL();
// Result: SELECT * FROM users

Select Specific Columns

// Select specific columns
const query = db
  .table('users')
  .select(['id', 'name', 'created_at'])
  .toSQL();
// Result: SELECT id, name, created_at FROM users

Select with Aliases

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

// Select columns with aliases
const query = db
  .table('users')
  .select([
    'id',
    rawAs('name', 'user_name'),
    rawAs('created_at', 'signup_date')
  ])
  .toSQL();
// Result: SELECT id, name AS user_name, created_at AS signup_date FROM users

Column Aliasing Options

hypequery provides multiple ways to alias columns depending on your needs:

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

// 1. Simple column renaming - use 'rawAs' function
db.table('users').select([
  rawAs('name', 'user_name'),
  rawAs('email', 'user_email')
])

// 2. Complex expressions - use 'rawAs' function
db.table('users').select([
  rawAs('LENGTH(name)', 'name_length'),
  rawAs('UPPER(email)', 'email_uppercase')
])

// 3. Built-in aggregation aliases
db.table('orders')
  .sum('total', 'total_revenue')
  .count('id', 'order_count')
  .avg('total', 'average_order')

Select with Expressions

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

// Select with computed expressions
const query = db
  .table('users')
  .select([
    'id',
    'name',
    rawAs('LENGTH(name)', 'name_length'),
    toDateTime('created_at', 'signup_datetime')
  ])
  .toSQL();
// Result: SELECT id, name, LENGTH(name) AS name_length, toDateTime(created_at) AS signup_datetime FROM users

Select from Joined Tables

// Select columns from multiple tables
const query = db
  .table('orders')
  .leftJoin('users', 'orders.user_id', 'users.id')
  .select([
    'orders.id',
    'orders.total',
    rawAs('users.name', 'customer_name'),
    rawAs('users.email', 'customer_email')
  ])
  .toSQL();
// Result: SELECT orders.id, orders.total, users.name AS customer_name, users.email AS customer_email FROM orders LEFT JOIN users ON orders.user_id = users.id

Select with Aggregations

// Select with aggregate functions
const query = db
  .table('orders')
  .select([
    'user_id',
    rawAs('COUNT(*)', 'order_count'),
    rawAs('SUM(total)', 'total_spent'),
    rawAs('AVG(total)', 'avg_order')
  ])
  .groupBy(['user_id'])
  .toSQL();
// Result: SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent, AVG(total) AS avg_order FROM orders GROUP BY user_id

Select with Conditional Logic

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

// Select with CASE statements
const query = db
  .table('users')
  .select([
    'id',
    'name',
    rawAs(`
      CASE 
        WHEN age < 18 THEN 'minor'
        WHEN age < 65 THEN 'adult'
        ELSE 'senior'
      END
    `, 'age_group')
  ])
  .toSQL();
// Result: SELECT id, name, CASE WHEN age < 18 THEN 'minor' WHEN age < 65 THEN 'adult' ELSE 'senior' END AS age_group FROM users

Common Patterns

Select with Subqueries

// Select with subquery
const query = db
  .table('users')
  .select([
    'id',
    'name',
    rawAs('(SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id)', 'order_count')
  ])
  .toSQL();
// Result: SELECT id, name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count FROM users

Select with Window Functions

// Select with window functions
const query = db
  .table('orders')
  .select([
    'id',
    'user_id',
    'total',
    rawAs('ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC)', 'rank')
  ])
  .toSQL();
// Result: SELECT id, user_id, total, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rank FROM orders

Type Safety

The select method provides full TypeScript support:

interface UserSchema {
  users: {
    id: 'UInt32';
    name: 'String';
    email: 'String';
    created_at: 'DateTime';
  };
}

const db = createQueryBuilder<UserSchema>();

// ✅ Type-safe column selection
const query = db
  .table('users')
  .select(['id', 'name', 'email']) // TypeScript knows these are valid columns
  .toSQL();

// ❌ TypeScript error for invalid column
const query2 = db
  .table('users')
  .select(['id', 'invalid_column']) // TypeScript error
  .toSQL();