FEATURE REQUEST: Implement $groupBy and $count

[vasilevich]

Hello, I know this has already been asked here https://github.com/feathersjs-ecosystem/feathers-objection/issues/101, and your response was its not implemented.

I would like to know why? is it a matter of lack of time?

if so, I would like to keep this issue open to invite myself, and others to implement and make a pull request. to one or more of these features.

so that I can keep my code clean from custom and weird hooks.

I like the feathersjs rest syntax and I really miss the methods above. and prefer not to directly access and implement objection/knex when possible, to keep things more streamlined and clean.

Please add enhancement, or other appropriate labels to this issue if you agree.

Thanks

[avimar]

@vasilevich just wanted to make sure you were aware of this option:

You should be able to write the query inside the model and expose it via the modify functionality https://github.com/feathersjs-ecosystem/feathers-objection#query-operators and https://vincit.github.io/objection.js/api/query-builder/other-methods.html#modify

[vasilevich]

@avimar Thank you! yes I was not aware of this, I will check it out now . Thanks!

[vasilevich]

@avimar I wonder, do you think this feature can be combined with all the other feathersjs methods such as $in , $select etc... to get the good from both worlds? and if so, is there any example about that? my specific use case was: to group by and count a specific column (in my case price) so I had to write this custom hook:

 find: [async (context) => {
      if ('$groupByAndCount' in context.params.query) {
        context.result = {
          data: await context.service.Model
            .query()
            .select(context.params.query.$groupByAndCount)
            .orderBy(context.params.query.$groupByAndCount)
            .count(`${context.params.query.$groupByAndCount} as count`)
            .groupBy(context.params.query.$groupByAndCount)
        };
      } else throw new Error("NOT_IMPLEMENTED");
      return context;
    }],

if you can provide a more pretty example with $modify skipping the hook altogether I will be glad, regardless I will check how to use this feature in depth.

[avimar]

1) I haven't testing your exact code, but I think you would simply move that logic into the model:

static modifiers = {
    groupByAndCount(query, field) { //query is the query object, field and any more args are what you pass in
        query.select(field)
            .orderBy(field)
            .count(`${field} as count`)
            .groupBy(field)
        }
    }

2) Then you can call your queries as normal, with an added $modify, e.g.: service.find({query:{$modify:{groupByAndCount:['myField']}}});

3) It will compose the rest of your query with those added fragments. I think the default select * is removed when you do this, so add that back if necessary.

4) Reminder to whitelist: ['$modify'] inside your service.

5) As an aside, you can debug all your queries by modifying objection.js and adding an on-query handler to see how it all composes:

    knex.on('query', function( queryData ) {
        console.log( queryData.method, queryData.bindings, queryData.sql);
        });

[dekelev]

Hi @vasilevich, Thanks for your interest in this library.

• $count: true can be added as an alias for $limit: 0, which in FeathersJS means that only the count query will be running.

• $groupBy query operator can be added. Input should be an array of strings that is passed to the ObjectionJS/Knex groupBy method.

I'm not available for adding new features myself at the moment, but both features can be added through pull requests.