How to use groupBy in Bookshelf js?

Question

how to use groupBy in Bookshelf JS, here is my Controller code.

router.route('/fetchStudentAttendance')
.post(function(req, res) {
 StudentAttendance
  .query(function (qb){
    qb.where('date', '>=', req.body.date);
    qb.groupBy("students_id");
  })
.where({'class_id': req.body.class_id, 'section_id': req.body.section_id})
.fetchAll({ withRelated: [{'StudentRef':function(qb) {qb.column('id', 'first_name', 'middle_name', 'last_name')}}]})
.then(studentAttendance => {
  let content = {
    data: studentAttendance,
    success: true,
    message: 'Record Not Found',
  };
  return res.send(content);
})
.catch(error => {
  let content = {
      data: error,
      success: false,
      message: 'Error while fetching Student Attendance.',
    };
  return res.send(content);
});

});

when i am trying to "groupBy" employee_id it will give Error like this.

code:"42703"
file:"parse_relation.c"
length:110
line:"3194"
name:"error"
position:"127"
routine:"check_ungrouped_columns_walker"
severity:"ERROR"

Answer 1

TL;DR

Use Bookshelf's built-in Collection manipulation to do custom groupBy after the .fetchAll() OR Use raw Knex to generate your query and results as needed, since groupBy will require some SQL aggregation definitions. This can be a Knex query result, or a Bookshelf object.

More Words

Bookshelf isn't exactly a query generator; not like the query libray KnexJS it is built off of. Bookshelf is made as a way to collect rows from your DB as Object Models. A SQL "groupBy" clause is kind of custom to the query, and Bookshelf itself implements lodash methods, like lodash's groupBy as stated in their docs. However, that is a server-side grouping on the queried data, it is not using SQL to do so.

When using your Model in Bookshelf, you should use new Model or Model.forge() before you fetch (or in case of many, .fetchAll()). You can start a Knex query, in a way, by using Model.query (see docs), but be aware that is returning Bookshelf objects. I typically use .query(function (qb) {}) to do some custom knex, like a custom WHERE clause or an qb.orderBy('prop'). Since groupBy would include 'many', you would want Model.query().fetchAll(), and be aware of custom aggregation in that query (Bookshelf will likely handle it, but it wouldn't be exactly like the model you defined, especially with custom methods). Using Knex directly might be a good option too.