Max SchmittMS
3rd July 2015

Getting missing duplicate columns in Knex.js

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

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:

[
{ 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:

;[
[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']
]
Image of my head

About the author

Hi, I’m Max! I'm a fullstack JavaScript developer living in Berlin.

When I’m not working on one of my personal projects, writing blog posts or making YouTube videos, I help my clients bring their ideas to life as a freelance web developer.

If you need help on a project, please reach out and let's work together.

To stay updated with new blog posts, follow me on Twitter or subscribe to my RSS feed.