nikzz
September 27, 2018, 2:20pm
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
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
The group by logic goes in Server side whitelisted method. JS is not a safe side to do it.
nikzz
September 28, 2018, 4:25am
4
Thanks man that helped alot
nikzz
September 28, 2018, 4:26am
5
Guess thats why it was depreciated in version 4 or 5.
Anyway thanks
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