Bookshelf.js groupBy on multiple columns

user_2525319 0
Viewed 2691

I'm building a program that uses Bookshelf to access a MySQL database. One of the things I'm trying to make is grouping data by two columns. But I can't figure out how to do it.

I tried using knex groupByRaw and lodash _.groupBy, but I don't think I'm using them in the right place.

Here's the situation:

Table

I'm storing timers in a table with the following columns: id, userID, projectID, startTime, stopTime.

I want to select the acumulative times for each user in a given calendar week.

My guess is the resulting query would be something like this:

SELECT SUM(TIMESTAMPDIFF(SECOND, startTime, endTime)), userID, YEARWEEK(startTime, 1), projectID FROM time GROUP BY userID, YEARWEEK(startTime, 1) HAVING projectID = 1

bookshelf.js knex.js mysql
1 Answers

Bit late, but for the record: you can call Knex's groupBy multiple times in succession. So:

  .groupBy("userID")
  .groupByRaw("YEARWEEK(startTime, 1)")
user_1226430
Related