mirror of
https://github.com/knex/knex.git
synced 2025-07-11 11:01:54 +00:00
754 lines
18 KiB
JavaScript
754 lines
18 KiB
JavaScript
'use strict';
|
|
|
|
const { isMysql, isPostgreSQL } = require('../../util/db-helpers');
|
|
const { dropTables, createAccounts } = require('../../util/tableCreatorHelper');
|
|
const { insertAccounts } = require('../../util/dataInsertHelper');
|
|
|
|
module.exports = function (knex) {
|
|
describe('Aggregate', function () {
|
|
before(async () => {
|
|
await dropTables(knex);
|
|
await createAccounts(knex);
|
|
|
|
await insertAccounts(knex);
|
|
});
|
|
|
|
it('has a sum', function () {
|
|
return knex('accounts')
|
|
.sum('logins')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select sum(`logins`) from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
'sum(`logins`)': 10,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mysql2',
|
|
'select sum(`logins`) from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
'sum(`logins`)': '10',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select sum("logins") from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
sum: '10',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select sum("logins") from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
sum: '10',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select sum(`logins`) from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
'sum(`logins`)': 10,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select sum("logins") from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
'SUM("LOGINS")': 10,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select sum([logins]) from [accounts]',
|
|
[],
|
|
[
|
|
{
|
|
'': 10,
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('supports sum with an alias', function () {
|
|
return knex('accounts')
|
|
.sum('logins', { as: 'login_sum' })
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select sum(`logins`) as `login_sum` from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
login_sum: 10,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mysql2',
|
|
'select sum(`logins`) as `login_sum` from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
login_sum: '10',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select sum("logins") as "login_sum" from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
login_sum: '10',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select sum("logins") as "login_sum" from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
login_sum: '10',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select sum(`logins`) as `login_sum` from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
login_sum: 10,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select sum("logins") "login_sum" from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
login_sum: 10,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select sum([logins]) as [login_sum] from [accounts]',
|
|
[],
|
|
[
|
|
{
|
|
login_sum: 10,
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('supports sum through object containing multiple aliases', function () {
|
|
return knex('accounts')
|
|
.sum({ login_sum: 'logins', balance_sum: 'balance' })
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select sum(`logins`) as `login_sum`, sum(`balance`) as `balance_sum` from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
balance_sum: 0,
|
|
login_sum: 10,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mysql2',
|
|
'select sum(`logins`) as `login_sum`, sum(`balance`) as `balance_sum` from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
balance_sum: 0,
|
|
login_sum: '10',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select sum("logins") as "login_sum", sum("balance") as "balance_sum" from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
balance_sum: 0,
|
|
login_sum: '10',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select sum("logins") as "login_sum", sum("balance") as "balance_sum" from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
balance_sum: '0',
|
|
login_sum: '10',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select sum(`logins`) as `login_sum`, sum(`balance`) as `balance_sum` from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
balance_sum: 0,
|
|
login_sum: 10,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select sum("logins") "login_sum", sum("balance") "balance_sum" from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
balance_sum: 0,
|
|
login_sum: 10,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select sum([logins]) as [login_sum], sum([balance]) as [balance_sum] from [accounts]',
|
|
[],
|
|
[
|
|
{
|
|
balance_sum: 0,
|
|
login_sum: 10,
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('has an avg', function () {
|
|
return knex('accounts')
|
|
.avg('logins')
|
|
.testSql(function (tester) {
|
|
function checkResRange(key, resp) {
|
|
return Math.abs(10 / 6 - +resp[0][key]) < 0.001;
|
|
}
|
|
function checkResRangeMssql(key, resp) {
|
|
return +resp[0][key] === 1;
|
|
}
|
|
|
|
// mysql: 1.6667
|
|
tester(
|
|
'mysql',
|
|
'select avg(`logins`) from `accounts`',
|
|
[],
|
|
checkResRange.bind(null, 'avg(`logins`)')
|
|
);
|
|
// sqlite: 1.6666666666666667
|
|
tester(
|
|
'sqlite3',
|
|
'select avg(`logins`) from `accounts`',
|
|
[],
|
|
checkResRange.bind(null, 'avg(`logins`)')
|
|
);
|
|
// pg: '1.6666666666666667'
|
|
tester(
|
|
'pg',
|
|
'select avg("logins") from "accounts"',
|
|
[],
|
|
checkResRange.bind(null, 'avg')
|
|
);
|
|
// pg-redshift: '1.6666666666666667'
|
|
tester(
|
|
'pg-redshift',
|
|
'select avg("logins") from "accounts"',
|
|
[],
|
|
checkResRangeMssql.bind(null, 'avg')
|
|
);
|
|
// oracle: 1.66666666666667
|
|
tester(
|
|
'oracledb',
|
|
'select avg("logins") from "accounts"',
|
|
[],
|
|
checkResRange.bind(null, 'AVG("LOGINS")')
|
|
);
|
|
// mssql: 1
|
|
tester(
|
|
'mssql',
|
|
'select avg([logins]) from [accounts]',
|
|
[],
|
|
checkResRangeMssql.bind(null, '')
|
|
);
|
|
});
|
|
});
|
|
|
|
it('has a count', function () {
|
|
return knex('accounts')
|
|
.count('id')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select count(`id`) from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
'count(`id`)': 6,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select count("id") from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
count: '6',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select count("id") from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
count: '6',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select count(`id`) from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
'count(`id`)': 6,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select count("id") from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
'COUNT("ID")': 6,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select count([id]) from [accounts]',
|
|
[],
|
|
[
|
|
{
|
|
'': 6,
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('supports multiple aggregate functions', function () {
|
|
return knex('accounts')
|
|
.count('id')
|
|
.max('logins')
|
|
.min('logins')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select count(`id`), max(`logins`), min(`logins`) from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
'count(`id`)': 6,
|
|
'max(`logins`)': 2,
|
|
'min(`logins`)': 1,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select count("id"), max("logins"), min("logins") from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
count: '6',
|
|
max: 2,
|
|
min: 1,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select count("id"), max("logins"), min("logins") from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
count: '6',
|
|
max: 2,
|
|
min: 1,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select count(`id`), max(`logins`), min(`logins`) from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
'count(`id`)': 6,
|
|
'max(`logins`)': 2,
|
|
'min(`logins`)': 1,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select count("id"), max("logins"), min("logins") from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
'COUNT("ID")': 6,
|
|
'MAX("LOGINS")': 2,
|
|
'MIN("LOGINS")': 1,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select count([id]), max([logins]), min([logins]) from [accounts]',
|
|
[],
|
|
[
|
|
{
|
|
'': [6, 2, 1],
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('has distinct modifier for aggregates', function () {
|
|
return knex('accounts')
|
|
.countDistinct('id')
|
|
.sumDistinct('logins')
|
|
.avgDistinct('logins')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select count(distinct `id`), sum(distinct `logins`), avg(distinct `logins`) from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
'count(distinct `id`)': 6,
|
|
'sum(distinct `logins`)': 3,
|
|
'avg(distinct `logins`)': 1.5,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select count(distinct "id"), sum(distinct "logins"), avg(distinct "logins") from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
count: '6',
|
|
sum: '3',
|
|
avg: '1.5000000000000000',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select count(distinct "id"), sum(distinct "logins"), avg(distinct "logins") from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
count: '6',
|
|
sum: '3',
|
|
avg: '1',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select count(distinct `id`), sum(distinct `logins`), avg(distinct `logins`) from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
'count(distinct `id`)': 6,
|
|
'sum(distinct `logins`)': 3,
|
|
'avg(distinct `logins`)': 1.5,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select count(distinct "id"), sum(distinct "logins"), avg(distinct "logins") from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
'COUNT(DISTINCT"ID")': 6,
|
|
'SUM(DISTINCT"LOGINS")': 3,
|
|
'AVG(DISTINCT"LOGINS")': 1.5,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select count(distinct [id]), sum(distinct [logins]), avg(distinct [logins]) from [accounts]',
|
|
[],
|
|
[
|
|
{
|
|
'': [6, 3, 1],
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
const testWithMultipleColumns = isMysql(knex) || isPostgreSQL(knex);
|
|
|
|
it('supports countDistinct with multiple columns', function () {
|
|
if (!testWithMultipleColumns) {
|
|
return this.skip();
|
|
}
|
|
|
|
return knex('accounts')
|
|
.countDistinct('id', 'logins')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select count(distinct `id`, `logins`) from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
'count(distinct `id`, `logins`)': 6,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select count(distinct("id", "logins")) from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
count: '6',
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('supports countDistinct with multiple columns with alias', function () {
|
|
if (!testWithMultipleColumns) {
|
|
return this.skip();
|
|
}
|
|
|
|
return knex('accounts')
|
|
.countDistinct({ count: ['id', 'logins'] })
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select count(distinct `id`, `logins`) as `count` from `accounts`',
|
|
[],
|
|
[
|
|
{
|
|
count: 6,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select count(distinct("id", "logins")) as "count" from "accounts"',
|
|
[],
|
|
[
|
|
{
|
|
count: '6',
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
|
|
it('support the groupBy function', function () {
|
|
return knex('accounts')
|
|
.count('id')
|
|
.groupBy('logins')
|
|
.orderBy('logins', 'asc')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select count(`id`) from `accounts` group by `logins` order by `logins` asc',
|
|
[],
|
|
[
|
|
{
|
|
'count(`id`)': 2,
|
|
},
|
|
{
|
|
'count(`id`)': 4,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select count("id") from "accounts" group by "logins" order by "logins" asc',
|
|
[],
|
|
[
|
|
{
|
|
count: '2',
|
|
},
|
|
{
|
|
count: '4',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select count("id") from "accounts" group by "logins" order by "logins" asc',
|
|
[],
|
|
[
|
|
{
|
|
count: '2',
|
|
},
|
|
{
|
|
count: '4',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select count(`id`) from `accounts` group by `logins` order by `logins` asc',
|
|
[],
|
|
[
|
|
{
|
|
'count(`id`)': 2,
|
|
},
|
|
{
|
|
'count(`id`)': 4,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select count("id") from "accounts" group by "logins" order by "logins" asc',
|
|
[],
|
|
[
|
|
{
|
|
'COUNT("ID")': 2,
|
|
},
|
|
{
|
|
'COUNT("ID")': 4,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select count([id]) from [accounts] group by [logins] order by [logins] asc',
|
|
[],
|
|
[
|
|
{
|
|
'': 2,
|
|
},
|
|
{
|
|
'': 4,
|
|
},
|
|
]
|
|
);
|
|
})
|
|
.then(function () {
|
|
return knex('accounts')
|
|
.count('id')
|
|
.groupBy('first_name')
|
|
.testSql(function (tester) {
|
|
tester(
|
|
'mysql',
|
|
'select count(`id`) from `accounts` group by `first_name`',
|
|
[],
|
|
[
|
|
{
|
|
'count(`id`)': 6,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg',
|
|
'select count("id") from "accounts" group by "first_name"',
|
|
[],
|
|
[
|
|
{
|
|
count: '6',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'pg-redshift',
|
|
'select count("id") from "accounts" group by "first_name"',
|
|
[],
|
|
[
|
|
{
|
|
count: '6',
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'sqlite3',
|
|
'select count(`id`) from `accounts` group by `first_name`',
|
|
[],
|
|
[
|
|
{
|
|
'count(`id`)': 6,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'oracledb',
|
|
'select count("id") from "accounts" group by "first_name"',
|
|
[],
|
|
[
|
|
{
|
|
'COUNT("ID")': 6,
|
|
},
|
|
]
|
|
);
|
|
tester(
|
|
'mssql',
|
|
'select count([id]) from [accounts] group by [first_name]',
|
|
[],
|
|
[
|
|
{
|
|
'': 6,
|
|
},
|
|
]
|
|
);
|
|
});
|
|
});
|
|
});
|
|
});
|
|
};
|