SQL function builders

import { countDistinct, dateTrunc, rowNumber, col } from 'ts-prorm-orm';

const dialect = prorm.getDialect(); // 'postgres' | 'mysql' | 'sqlite' | 'mssql' | …

countDistinct(dialect, 'id'); // COUNT(DISTINCT "id") on PostgreSQL
dateTrunc(dialect, 'month', col('createdAt')); // DATE_TRUNC('month', "createdAt")
rowNumber().forDialect(dialect).partitionBy('userId').orderBy('total', 'DESC').toSQL();
// ROW_NUMBER() OVER (PARTITION BY "userId" ORDER BY "total" DESC)

import { col, count, dateTrunc, sqlSum } from 'ts-prorm-orm';

const d = prorm.getDialect();
const month = dateTrunc(d, 'month', col('createdAt'));

const rows = await Order.findAll({
  attributes: [
    [month, 'month'],
    [count(d), 'orders'],
    [sqlSum(d, col('total'), { filter: { status: 'paid' } }), 'paidRevenue'],
  ],
  group: [month],
  order: [['month', 'ASC']],
});

await Order.findAll({ attributes: [[month, 'month']], group: [month], order: [[month, 'ASC']] });

import { concat, col } from 'ts-prorm-orm';

concat(dialect, 'first', ' ', 'last');
// 'first' || ' ' || 'last'          — three literals

concat(dialect, col('firstName'), ' ', col('lastName'));
// "firstName" || ' ' || "lastName"  — two columns and a literal

import { countDistinct, sqlSum, col } from 'ts-prorm-orm';

countDistinct(dialect, 'id'); // COUNT(DISTINCT "id")
sqlSum(dialect, col('total')); // SUM("total")   — col() also works

Error: percentileCont() is not supported on dialect "mysql": mysql has no PERCENTILE_CONT
function; compute the percentile in a subquery with ORDER BY and LIMIT/OFFSET instead

import { stringAgg, col } from 'ts-prorm-orm';

stringAgg(dialect, col('name'), ', ', { orderBy: 'name', distinct: true });

Error: stringAgg() is not supported on dialect "sqlite": SQLite's GROUP_CONCAT does not
support an ORDER BY clause; pre-sort via a subquery instead

Error: stringAgg() is not supported on dialect "mssql": MSSQL STRING_AGG does not support DISTINCT

import { countDistinct, col } from 'ts-prorm-orm';

countDistinct(dialect, [col('userId'), col('productId')]);

import { rowNumber, lag, ntile } from 'ts-prorm-orm';

rowNumber().forDialect('postgres').partitionBy('userId').orderBy('total', 'DESC').toSQL();
// ROW_NUMBER() OVER (PARTITION BY "userId" ORDER BY "total" DESC)

lag('total', 1).forDialect('mysql').partitionBy('userId').orderBy('createdAt').toSQL();
// LAG(`total`, 1) OVER (PARTITION BY `userId` ORDER BY `createdAt`)

ntile(4).forDialect('postgres').orderBy('total', 'DESC').toSQL();
// NTILE(4) OVER (ORDER BY "total" DESC)

import { rowNumber } from 'ts-prorm-orm';

const d = prorm.getDialect();
await Order.findAll({
  attributes: [
    'id',
    'userId',
    'total',
    rowNumber().forDialect(d).partitionBy('userId').orderBy('total', 'DESC').as('rankInUser'),
  ],
});

import { sumOver, avgOver } from 'ts-prorm-orm';

sumOver('total').forDialect('postgres').orderBy('id')
  .rows({ start: 'unboundedPreceding', end: 'currentRow' })
  .toSQL();
// SUM("total") OVER (ORDER BY "id" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

avgOver('total').forDialect('mysql').partitionBy('userId')
  .rows({ start: { preceding: 2 }, end: { following: 2 } })
  .toSQL();
// AVG(`total`) OVER (PARTITION BY `userId` ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

import { avgOver } from 'ts-prorm-orm';

avgOver('total').forDialect(dialect).orderBy('id')
  .rows({ start: { preceding: 2 }, end: { following: 2 } })
  .exclude('currentRow')
  .toSQL();

Error: Window frame exclusion (EXCLUDE CURRENT ROW) is not supported on dialect "mysql";
no other frame specification expresses it

Error: Window frame exclusion (EXCLUDE CURRENT ROW) is not supported on dialect "mssql";
no other frame specification expresses it

import { namedWindow, sumOver } from 'ts-prorm-orm';

const w = namedWindow('w').forDialect('postgres').partitionBy('userId').orderBy('createdAt');

w.toSQL(); // "w" AS (PARTITION BY "userId" ORDER BY "createdAt")  — for the WINDOW clause
sumOver('total').forDialect('postgres').over(w).toSQL(); // SUM("total") OVER "w"

import { jsonExtract, jsonObject, col } from 'ts-prorm-orm';

jsonExtract(dialect, col('shipping'), '$.address.city');
jsonObject(dialect, { id: col('id'), total: col('total') });

import { fullTextMatch, fullTextRank } from 'ts-prorm-orm';

fullTextMatch(dialect, ['title', 'body'], 'database index');
fullTextRank(dialect, ['title', 'body'], 'database index');

import { fullTextMatch } from 'ts-prorm-orm';

await Product.findAll({ where: fullTextMatch(prorm.getDialect(), ['name'], 'wireless mouse') });
// PostgreSQL: SELECT * FROM "products" WHERE to_tsvector('english', "name") @@ plainto_tsquery('english', 'wireless mouse')
// SQLite:     SELECT * FROM "products" WHERE ("name" MATCH 'wireless mouse')

import { sqlCoalesce, nullif, round, col } from 'ts-prorm-orm';

sqlCoalesce(dialect, col('nickname'), col('name'), 'anonymous'); // COALESCE("nickname", "name", 'anonymous')
nullif(dialect, col('discount'), 0); // NULLIF("discount", 0)
round(dialect, col('total'), 2); // ROUND("total", 2)

import { greatest, col } from 'ts-prorm-orm';

greatest(dialect, col('a'), col('b'));

import { caseWhen, caseOf } from 'ts-prorm-orm';

caseWhen()
  .when({ status: 'active' }, 1)
  .else(0)
  .toSQL('postgres');
// CASE WHEN "status" = 'active' THEN 1 ELSE 0 END

caseOf('status').when('a', 1).else(0).toSQL('mysql');
// CASE `status` WHEN 'a' THEN 1 ELSE 0 END — the simple (switch) form

import { caseWhen, count, sqlSum } from 'ts-prorm-orm';

const d = prorm.getDialect();
await Order.findAll({
  attributes: [
    [count(d), 'total'],
    [sqlSum(d, caseWhen(d).when({ status: 'paid' }, 1).else(0).toLiteral()), 'paid'],
  ],
});