Group by in frappe.call

Frappe Framework Custom Script
1

How to use ‘group by’ in frappe call ??

I want to get total count of all the user in each category

EDIT
Need to do this via custom script

This is the sql query

Select COUNT(service_name) from `tabSA`where CURDATE() BETWEEN start_date AND end_date group by service_name
2

Hi @nikzz,

as far as I know the frappe.call does neither allows direct SQL execution (consider security), nor a grouping function (refer to Developer Cheatsheet · frappe/frappe Wiki · GitHub).

However, with your requirement to purely run this from a custom script, why not aggregate in your browser? I.e. run something like

    frappe.call({
        method:"frappe.client.get_list",
        args:{
     	doctype: "SA",
     	filters: [
     	    ["start_date","<=", frappe.datetime.nowdate()],
     	    ["end_date",">=", frappe.datetime.nowdate()]
     	],
            fields: ["service_name"]
        },
        callback: function (response) {
            if (response.message) {
                 // this will be your dict with service_name, count
                 var services = {};
                 response.message.forEach(function (svc) {
                     if (services[svc.service_name] === undefined) {
                         // first occurence
                         services[svc.service_name] = 1;
                     } else {
                        // add quantity
                        services[svc.service_name] = services[svc.service_name] + 1;
                     }
                 });
            }
        }
    });
1 Like
3

The group by logic goes in Server side whitelisted method. JS is not a safe side to do it.

4

Thanks man that helped alot

5

Guess thats why it was depreciated in version 4 or 5.

Anyway thanks

6

If the requirement was “has to be custom script”, then the assumption is that we do not have server side options. This would require either a custom app or a fork of ERPNext… Provided you use a current browser, it should be no problem running this in the browser. If there are concerns then not the safety, but the performance :wink: