Table of contents

1. Final Table
2. Prewhere
3. Replace
4. With (Non CTE)

import { table, dsql as sql, fromStringifiedSelectStatement } from "../../src";

Final Table

const chTableRegular = table(["col1", "col2"], "tableName");
chTableRegular.selectStar().stringify();

SELECT
  *
FROM
  `tableName`

const chTableFinal = chTableRegular.clickhouse.final();
chTableFinal.selectStar().stringify();

SELECT
  *
FROM
  `tableName` FINAL

table(["col1", "col2"], "alias", "tableName")
    .clickhouse.final()
    .selectStar()
    .stringify();

SELECT
  *
FROM
  `tableName` AS `alias` FINAL

Prewhere

The API is like WHERE’s.

chTableFinal
    .selectStar()
    .where((f) => f.col2)
    .clickhouse.prewhere((f) => f.col1)
    .stringify();

SELECT
  *
FROM
  `tableName` FINAL PREWHERE `col1`
WHERE
  `col2`

chTableFinal
    .selectStar()
    .clickhouse.prewhere((f) => f.col1)
    .clickhouse.prewhere((f) => f.col2)
    .where((f) => f.col2)
    .stringify();

SELECT
  *
FROM
  `tableName` FINAL PREWHERE `col1`
  AND `col2`
WHERE
  `col2`

Replace

chTableRegular
    .selectStar()
    .clickhouse.replace((f) => [["col1", sql`${f.col1}+1`]])
    .stringify();

SELECT
  * REPLACE (`col1` + 1 AS `col1`)
FROM
  `tableName`

With (Non CTE)

Alongside Common Table Expressions, Clickhouse’s syntax extension of WITH is also supported.

chTableRegular
    .select((f) => ({
        res1: f.col1,
    }))
    .clickhouse.with_({
        abc: chTableFinal.select((_f) => ({ count: sql`COUNT()` })),
    })
    .appendSelect((f) => ({ res2: sql`${f.col2} + ${f.abc}` }))
    .stringify();

WITH
  (
    SELECT
      COUNT() AS `count`
    FROM
      `tableName` FINAL
  ) AS `abc`
SELECT
  `col1` AS `res1`,
  `col2` + `abc` AS `res2`
FROM
  `tableName`

chTableRegular
    .select((f) => ({
        res1: f.col1,
    }))
    .clickhouse.with_({
        abc: fromStringifiedSelectStatement(sql`20`),
    })
    .appendSelect((f) => ({ res2: sql`${f.col2} + ${f.abc}` }))
    .stringify();

WITH
  (20) AS `abc`
SELECT
  `col1` AS `res1`,
  `col2` + `abc` AS `res2`
FROM
  `tableName`

---

This document used eval-md