July 3 2015

Getting missing duplicate columns in Knex.js

I was making a query like this to a PostgreSQL-database today:

SQL

SELECT
    extract(DAY FROM "time"),
    extract(HOUR FROM "time"),
    extract(MINUTE FROM "time"),
    count(DISTINCT "full_name")
FROM
    "hits"
WHERE
    "time"
BETWEEN
    '2015-02-23'
    AND
    '2015-02-24'
GROUP BY
    extract(DAY FROM "time"),
    extract(HOUR FROM "time"),
    extract(MINUTE FROM "time");

This is the kind of result I would get when trying it out in PSequel:

+-----------+-----------+-----------+-------+
| date_part | date_part | date_part | count |
+-----------+-----------+-----------+-------+
|        23 |         0 |         0 |     5 |
|        23 |         0 |         1 |     1 |
|        23 |         0 |         2 |     1 |
|        23 |         0 |         3 |     1 |
|        23 |         0 |         4 |     4 |
|        23 |         0 |         5 |     2 |
|        23 |         0 |         6 |     2 |
|        23 |         0 |         7 |     3 |
+-----------+-----------+-----------+-------+

I built the same query using Knex.js:

const resolution = 'extract(day from "time"), extract(hour from "time"), extract(minute from "time")'

db('hits')
	.select(db.raw(`${resolution}, count(distinct "full_name")`))
	.whereBetween('time', [t1, t2])
	.groupByRaw(resolution)
	.then(respond)
	.catch(next)

This, however, got me a result like this:

result = [
	{ date_part: 0, count: '5' },
	{ date_part: 1, count: '1' },
	{ date_part: 2, count: '1' },
	{ date_part: 3, count: '1' },
	{ date_part: 4, count: '4' },
	{ date_part: 5, count: '2' },
	{ date_part: 6, count: '2' },
	{ date_part: 7, count: '3' },
]

As you can tell, date_part-columns for day and hour are missing. This is understandable because JavaScript objects can not have duplicate keys. To get around this issue, we need Knex.js to display our results using arrays. This is possible by using options:

db('hits')
	.select(db.raw(`${resolution}, count(distinct "full_name")`))
	.whereBetween('time', [t1, t2])
	.groupByRaw(resolution)
	.options({ rowMode: 'array' })
	.then(respond)
	.catch(next)

With the correct options, the result from Knex.js will look like this:

result = [
	[23, 0, 0, '5'],
	[23, 0, 1, '1'],
	[23, 0, 2, '1'],
	[23, 0, 3, '1'],
	[23, 0, 4, '4'],
	[23, 0, 5, '2'],
	[23, 0, 6, '2'],
	[23, 0, 7, '3'],
]