0
# Column Operations
1

2
This document covers rich column expressions including comparisons, mathematical operations, string functions, type casting, and all available column methods for building complex SQL expressions.
3

4
## Column Class
5

6
The Column class represents database columns and provides methods for creating SQL expressions.
7

8
```javascript { .api }
9
class Column {
10
  // Properties
11
  name: string;
12
  table: Table;
13
  alias: string;
14
  dataType: string;
15
  defaultValue: any;
16
  
17
  // Value and node operations
18
  value(value: any): Column;
19
  getValue(): any;
20
  toNode(): ColumnNode;
21
  as(alias: string): ColumnNode;
22
  toQuery(): QueryResult;
23
  
24
  // Aggregation functions
25
  count(alias?: string): Column;
26
  sum(alias?: string): Column;
27
  avg(alias?: string): Column;
28
  min(alias?: string): Column;
29
  max(alias?: string): Column;
30
  distinct(): Column;
31
  arrayAgg(alias?: string): Column;
32
  aggregate(alias: string, aggregator: string): Column;
33
  
34
  // Ordering
35
  asc: OrderByValueNode;
36
  ascending: OrderByValueNode;
37
  desc: OrderByValueNode; 
38
  descending: OrderByValueNode;
39
}
40
```
41

42
## Comparison Operations
43

44
### Equality and Inequality
45

46
```javascript { .api }
47
column.equals(value: any): BinaryExpression;
48
column.equal(value: any): BinaryExpression;    // Alias for equals
49
column.notEquals(value: any): BinaryExpression;
50
column.notEqual(value: any): BinaryExpression; // Alias for notEquals
51
```
52

53
Usage examples:
54

55
```javascript
56
// Basic equality
57
const equalQuery = user
58
  .select()
59
  .where(user.name.equals('John'))
60
  .toQuery();
61

62
// Not equal
63
const notEqualQuery = user
64
  .select()
65
  .where(user.status.notEquals('inactive'))
66
  .toQuery();
67

68
// Compare with another column
69
const columnCompareQuery = user
70
  .select()
71
  .where(user.created_at.equals(user.updated_at))
72
  .toQuery();
73
```
74

75
### Relational Comparisons
76

77
```javascript { .api }
78
column.gt(value: any): BinaryExpression;    // Greater than
79
column.gte(value: any): BinaryExpression;   // Greater than or equal
80
column.lt(value: any): BinaryExpression;    // Less than
81
column.lte(value: any): BinaryExpression;   // Less than or equal
82
```
83

84
Usage examples:
85

86
```javascript
87
// Age comparisons
88
const adultUsers = user
89
  .select()
90
  .where(user.age.gte(18))
91
  .toQuery();
92

93
// Date comparisons
94
const recentPosts = post
95
  .select()
96
  .where(post.created_at.gt(new Date('2023-01-01')))
97
  .toQuery();
98

99
// Salary range
100
const salaryRangeQuery = employee
101
  .select()
102
  .where(
103
    employee.salary.gte(50000).and(employee.salary.lte(100000))
104
  )
105
  .toQuery();
106
```
107

108
## Null Checks
109

110
```javascript { .api }
111
column.isNull(): UnaryExpression;
112
column.isNotNull(): UnaryExpression;
113
```
114

115
Usage examples:
116

117
```javascript
118
// Find users without email
119
const noEmailQuery = user
120
  .select()
121
  .where(user.email.isNull())
122
  .toQuery();
123

124
// Find users with phone numbers
125
const hasPhoneQuery = user
126
  .select()
127
  .where(user.phone.isNotNull())
128
  .toQuery();
129
```
130

131
## String Operations
132

133
### Pattern Matching
134

135
```javascript { .api }
136
column.like(pattern: string): BinaryExpression;
137
column.notLike(pattern: string): BinaryExpression;
138
column.ilike(pattern: string): BinaryExpression;      // Case-insensitive LIKE (PostgreSQL)
139
column.notIlike(pattern: string): BinaryExpression;   // Case-insensitive NOT LIKE (PostgreSQL)
140
column.rlike(pattern: string): BinaryExpression;      // RLIKE (MySQL)
141
```
142

143
Usage examples:
144

145
```javascript
146
// LIKE pattern matching
147
const likeQuery = user
148
  .select()
149
  .where(user.name.like('John%'))
150
  .toQuery();
151

152
// Case-insensitive search (PostgreSQL)
153
const ilikeQuery = user
154
  .select()
155
  .where(user.email.ilike('%@gmail.com'))
156
  .toQuery();
157

158
// NOT LIKE
159
const notLikeQuery = user
160
  .select()
161
  .where(user.email.notLike('%temp%'))
162
  .toQuery();
163
```
164

165
### Regular Expressions
166

167
```javascript { .api }
168
column.regex(pattern: string): BinaryExpression;      // ~ (PostgreSQL)
169
column.iregex(pattern: string): BinaryExpression;     // ~* (PostgreSQL)
170
column.notRegex(pattern: string): BinaryExpression;   // !~ (PostgreSQL)
171
column.notIregex(pattern: string): BinaryExpression;  // !~* (PostgreSQL)
172
column.regexp(pattern: string): BinaryExpression;     // REGEXP (MySQL)
173
column.match(pattern: string): BinaryExpression;      // @@ full-text search (PostgreSQL)
174
```
175

176
Usage examples:
177

178
```javascript
179
// Regular expression matching (PostgreSQL)
180
const regexQuery = user
181
  .select()
182
  .where(user.phone.regex('^\\+1'))
183
  .toQuery();
184

185
// Case-insensitive regex (PostgreSQL)
186
const iregexQuery = user
187
  .select()
188
  .where(user.name.iregex('^(john|jane)'))
189
  .toQuery();
190

191
// MySQL REGEXP
192
const mysqlRegexQuery = user
193
  .select()
194
  .where(user.email.regexp('[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}'))
195
  .toQuery();
196

197
// Full-text search (PostgreSQL)
198
const fullTextQuery = document
199
  .select()
200
  .where(document.content_vector.match('search & terms'))
201
  .toQuery();
202
```
203

204
## Set Operations
205

206
### IN and NOT IN
207

208
```javascript { .api }
209
column.in(values: any[]): BinaryExpression;
210
column.in(subquery: SubQuery): BinaryExpression;
211
column.notIn(values: any[]): BinaryExpression;
212
column.notIn(subquery: SubQuery): BinaryExpression;
213
```
214

215
Usage examples:
216

217
```javascript
218
// IN with array
219
const inArrayQuery = user
220
  .select()
221
  .where(user.status.in(['active', 'pending', 'verified']))
222
  .toQuery();
223

224
// IN with subquery
225
const activeUserIds = user.subQuery()
226
  .select(user.id)
227
  .where(user.active.equals(true));
228

229
const postsFromActiveUsers = post
230
  .select()
231
  .where(post.userId.in(activeUserIds))
232
  .toQuery();
233

234
// NOT IN
235
const notInQuery = user
236
  .select()
237
  .where(user.role.notIn(['banned', 'suspended']))
238
  .toQuery();
239
```
240

241
### BETWEEN
242

243
```javascript { .api }
244
column.between(start: any, end: any): TernaryExpression;
245
column.notBetween(start: any, end: any): TernaryExpression;
246
```
247

248
Usage examples:
249

250
```javascript
251
// Age range
252
const ageRangeQuery = user
253
  .select()
254
  .where(user.age.between(18, 65))
255
  .toQuery();
256

257
// Date range
258
const dateRangeQuery = post
259
  .select()
260
  .where(post.created_at.between('2023-01-01', '2023-12-31'))
261
  .toQuery();
262

263
// NOT BETWEEN
264
const notBetweenQuery = employee
265
  .select()
266
  .where(employee.salary.notBetween(30000, 40000))
267
  .toQuery();
268
```
269

270
## Mathematical Operations
271

272
```javascript { .api }
273
column.plus(value: any): BinaryExpression;      // Addition (+)
274
column.minus(value: any): BinaryExpression;     // Subtraction (-)
275
column.multiply(value: any): BinaryExpression;  // Multiplication (*)
276
column.divide(value: any): BinaryExpression;    // Division (/)
277
column.modulo(value: any): BinaryExpression;    // Modulo (%)
278
```
279

280
Usage examples:
281

282
```javascript
283
// Calculate total with tax
284
const totalWithTaxQuery = order
285
  .select(
286
    order.id,
287
    order.subtotal,
288
    order.subtotal.multiply(1.08).as('total_with_tax')
289
  )
290
  .toQuery();
291

292
// Age in months
293
const ageInMonthsQuery = user
294
  .select(
295
    user.name,
296
    user.age.multiply(12).as('age_in_months')
297
  )
298
  .toQuery();
299

300
// Discount calculation
301
const discountQuery = product
302
  .select(
303
    product.name,
304
    product.price,
305
    product.price.minus(product.price.multiply(0.1)).as('discounted_price')
306
  )
307
  .toQuery();
308
```
309

310
## Bitwise Operations
311

312
```javascript { .api }
313
column.leftShift(value: any): BinaryExpression;   // << (left shift)
314
column.rightShift(value: any): BinaryExpression;  // >> (right shift)
315
column.bitwiseAnd(value: any): BinaryExpression;  // & (bitwise AND)
316
column.bitwiseOr(value: any): BinaryExpression;   // | (bitwise OR)
317
column.bitwiseXor(value: any): BinaryExpression;  // # (bitwise XOR)
318
column.bitwiseNot(value: any): BinaryExpression;  // ~ (bitwise NOT)
319
```
320

321
Usage examples:
322

323
```javascript
324
// Permission checking with bitwise operations
325
const permissionQuery = user
326
  .select()
327
  .where(user.permissions.bitwiseAnd(4).gt(0))  // Check for specific permission bit
328
  .toQuery();
329

330
// Bitwise flags
331
const flagQuery = item
332
  .select()
333
  .where(item.flags.bitwiseOr(8).equals(item.flags))
334
  .toQuery();
335
```
336

337
## String/JSON Operations
338

339
### String Concatenation
340

341
```javascript { .api }
342
column.concat(value: any): BinaryExpression;     // || (string concatenation)
343
```
344

345
Usage examples:
346

347
```javascript
348
// Concatenate first and last name
349
const fullNameQuery = user
350
  .select(
351
    user.id,
352
    user.first_name.concat(' ').concat(user.last_name).as('full_name')
353
  )
354
  .toQuery();
355
```
356

357
### JSON Operations (PostgreSQL)
358

359
```javascript { .api }
360
column.key(key: string): BinaryExpression;       // -> (JSON key access)
361
column.keyText(key: string): BinaryExpression;   // ->> (JSON key access as text)
362
column.path(path: string[]): BinaryExpression;   // #> (JSON path access)
363
column.pathText(path: string[]): BinaryExpression; // #>> (JSON path access as text)
364
```
365

366
Usage examples:
367

368
```javascript
369
// JSON key access
370
const jsonKeyQuery = user
371
  .select(
372
    user.id,
373
    user.metadata.key('preferences').as('user_preferences')
374
  )
375
  .toQuery();
376

377
// JSON key as text
378
const jsonTextQuery = user
379
  .select(
380
    user.id,
381
    user.profile.keyText('name').as('profile_name')
382
  )
383
  .toQuery();
384

385
// JSON path access
386
const jsonPathQuery = user
387
  .select(
388
    user.id,
389
    user.settings.path(['ui', 'theme']).as('theme_setting')
390
  )
391
  .toQuery();
392
```
393

394
## Array/JSON Advanced Operations (PostgreSQL)
395

396
```javascript { .api }
397
column.contains(value: any): BinaryExpression;      // @> (contains)
398
column.containedBy(value: any): BinaryExpression;   // <@ (contained by)
399
column.containsKey(key: string): BinaryExpression;  // ? (contains key)
400
column.overlap(value: any): BinaryExpression;       // && (overlaps)
401
column.at(index: number): Expression;               // Array element access
402
column.slice(start: number, end?: number): Expression; // Array slice
403
```
404

405
Usage examples:
406

407
```javascript
408
// Array contains
409
const containsQuery = post
410
  .select()
411
  .where(post.tags.contains(['javascript', 'node']))
412
  .toQuery();
413

414
// JSON contains key
415
const hasKeyQuery = user
416
  .select()
417
  .where(user.metadata.containsKey('preferences'))
418
  .toQuery();
419

420
// Array overlap
421
const overlapQuery = user
422
  .select()
423
  .where(user.interests.overlap(['programming', 'music']))
424
  .toQuery();
425

426
// Array element access
427
const firstTagQuery = post
428
  .select(
429
    post.id,
430
    post.tags.at(0).as('first_tag')
431
  )
432
  .toQuery();
433

434
// Array slice
435
const firstThreeTagsQuery = post
436
  .select(
437
    post.id,
438
    post.tags.slice(0, 3).as('first_three_tags')
439
  )
440
  .toQuery();
441
```
442

443
## Logical Operations
444

445
```javascript { .api }
446
column.and(condition: any): BinaryExpression;
447
column.or(condition: any): BinaryExpression;
448
```
449

450
Usage examples:
451

452
```javascript
453
// Complex logical conditions
454
const complexQuery = user
455
  .select()
456
  .where(
457
    user.active.equals(true)
458
      .and(user.verified.equals(true))
459
      .or(user.role.equals('admin'))
460
  )
461
  .toQuery();
462
```
463

464
## Type Operations
465

466
### Type Casting
467

468
```javascript { .api }
469
column.cast(dataType: string): CastExpression;
470
```
471

472
Usage examples:
473

474
```javascript
475
// Cast to different type
476
const castQuery = user
477
  .select(
478
    user.id,
479
    user.age.cast('VARCHAR').as('age_string'),
480
    user.created_at.cast('DATE').as('created_date')
481
  )
482
  .toQuery();
483
```
484

485
### CASE Expressions
486

487
```javascript { .api }
488
column.case(whenConditions: any[], thenValues: any[], elseValue?: any): CaseExpression;
489
```
490

491
Usage examples:
492

493
```javascript
494
// Simple CASE expression
495
const caseQuery = user
496
  .select(
497
    user.name,
498
    user.age.case(
499
      [user.age.lt(18), user.age.between(18, 65)],
500
      ['Minor', 'Adult'],
501
      'Senior'
502
    ).as('age_category')
503
  )
504
  .toQuery();
505

506
// CASE with multiple conditions
507
const statusCaseQuery = order
508
  .select(
509
    order.id,
510
    order.status.case(
511
      [
512
        order.status.equals('pending'),
513
        order.status.equals('shipped'),
514
        order.status.equals('delivered')
515
      ],
516
      ['Processing', 'In Transit', 'Completed'],
517
      'Unknown'
518
    ).as('status_description')
519
  )
520
  .toQuery();
521
```
522

523
## Aggregation Functions
524

525
```javascript { .api }
526
column.count(alias?: string): Column;
527
column.sum(alias?: string): Column;
528
column.avg(alias?: string): Column;
529
column.min(alias?: string): Column;
530
column.max(alias?: string): Column;
531
column.distinct(): Column;
532
column.arrayAgg(alias?: string): Column;        // PostgreSQL
533
column.aggregate(alias: string, aggregator: string): Column;
534
```
535

536
Usage examples:
537

538
```javascript
539
// Basic aggregations
540
const aggregateQuery = order
541
  .select(
542
    order.userId,
543
    order.amount.sum('total_amount'),
544
    order.amount.avg('avg_amount'),
545
    order.amount.min('min_amount'),
546
    order.amount.max('max_amount'),
547
    order.id.count('order_count')
548
  )
549
  .group(order.userId)
550
  .toQuery();
551

552
// Distinct count
553
const distinctQuery = user
554
  .select(
555
    user.department.distinct().count('unique_departments')
556
  )
557
  .toQuery();
558

559
// Array aggregation (PostgreSQL)
560
const arrayAggQuery = user
561
  .select(
562
    user.department,
563
    user.name.arrayAgg('user_names')
564
  )
565
  .group(user.department)
566
  .toQuery();
567

568
// Custom aggregation
569
const customAggQuery = product
570
  .select(
571
    product.category,
572
    product.price.aggregate('median_price', 'PERCENTILE_CONT(0.5)')
573
  )
574
  .group(product.category)
575
  .toQuery();
576
```
577

578
## Ordering Operations
579

580
```javascript { .api }
581
// Ordering properties
582
column.asc: OrderByValueNode;         // Ascending order
583
column.ascending: OrderByValueNode;   // Ascending order (alias)
584
column.desc: OrderByValueNode;        // Descending order
585
column.descending: OrderByValueNode;  // Descending order (alias)
586
```
587

588
Usage examples:
589

590
```javascript
591
// Simple ordering
592
const orderedQuery = user
593
  .select()
594
  .order(user.name.asc, user.age.desc)
595
  .toQuery();
596

597
// Ordering with expressions
598
const expressionOrderQuery = user
599
  .select()
600
  .order(
601
    user.last_name.asc,
602
    user.first_name.asc,
603
    user.created_at.desc
604
  )
605
  .toQuery();
606
```
607

608
## Column Value Assignment
609

610
```javascript { .api }
611
column.value(value: any): Column;
612
column.getValue(): any;
613
```
614

615
Usage examples:
616

617
```javascript
618
// Set column values for INSERT
619
const insertQuery = user
620
  .insert(
621
    user.name.value('John Doe'),
622
    user.email.value('john@example.com'),
623
    user.age.value(30)
624
  )
625
  .toQuery();
626

627
// Use in UPDATE
628
const updateQuery = user
629
  .update({
630
    name: 'Jane Doe',
631
    updated_at: user.updated_at.value(new Date())
632
  })
633
  .where(user.id.equals(1))
634
  .toQuery();
635
```
636

637
## Column Aliases
638

639
```javascript { .api }
640
column.as(alias: string): ColumnNode;
641
```
642

643
Usage examples:
644

645
```javascript
646
// Column aliases in SELECT
647
const aliasQuery = user
648
  .select(
649
    user.id,
650
    user.first_name.as('firstName'),
651
    user.last_name.as('lastName'),
652
    user.email.as('emailAddress')
653
  )
654
  .toQuery();
655

656
// Aliases with expressions
657
const expressionAliasQuery = order
658
  .select(
659
    order.id,
660
    order.subtotal.multiply(1.08).as('totalWithTax'),
661
    order.created_at.cast('DATE').as('orderDate')
662
  )
663
  .toQuery();
664
```