peterg
September 29, 2022, 10:42am
1
Hello,
Does anyone know if it’s possible to run SUM or COUNT aggregations using the parts of query builder exposed to the Script API (i.e., the stuff that can be used in a Server Script or a Custom Report)?
There’s an explanation of how to do it here:https://frappeframework.com/docs/v13/user/en/api/query-builder#simple-functions
…but the example requires an import statement. Are these methods exposed in any way to safe_exec environments? Is there another way to accomplish SUMs or other aggregations?
Yes, you can use query builder in server scripts.frappe/frappe/utils/safe_exec.py at develop · frappe/frappe · GitHub
You can’t run an update query but you can run simple select and aggregation functions.
peterg
September 29, 2022, 1:44pm
3
I am aware that you can use query builder in server scripts. That’s not what I was asking.
In my post, I asked if it is possible to use the SUM function in server scripts.
peterg
October 3, 2022, 6:56am
4
Any tips or suggestions by people who have been using query builder?
you can use frappe.qb.functions
could be rewrite in Server Script this way:
Notes = frappe.qb.DocType("Notes")
count_pages = frappe.qb.functions("Count", Notes.content).as_("Pages")
result = frappe.qb.from_(Notes).select(count_pages)
frappe.db.sql(result, as_dict=True)
1 Like
peterg
October 3, 2022, 2:51pm
6
Awesome, thank you so much @amadhaji . That’s exactly what I was looking for. I see that utility method now in the source:
Table = Table
# Added dynamic type hints for engine attribute
# which is to be assigned later.
if typing.TYPE_CHECKING:
from frappe.database.query import Engine
engine: Engine
@staticmethod
def functions(name: str, *args, **kwargs) -> Function:
return Function(name, *args, **kwargs)
@staticmethod
def DocType(table_name: str, *args, **kwargs) -> Table:
table_name = get_table_name(table_name)
return Table(table_name, *args, **kwargs)
@classmethod
def into(cls, table, *args, **kwargs) -> QueryBuilder:
if isinstance(table, str):