FEATURE REQUEST: Implement $groupBy and $count

[vasilevich]

Hello, I know this has already been asked here #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.