Query Builder
frappe.qb
is a query builder written around PyPika to build a single interface for cross-db queries
While developing apps, you'll often need to retrieve some specific data from the database. One way to do this is to use
frappe.db.sql
and write raw SQL queries.
Maybe something like
result = frappe.db.sql(
f"""
SELECT `path`,
COUNT(*) as count,
COUNT(CASE WHEN CAST(`is_unique` as Integer) = 1 THEN 1 END) as unique_count
FROM `tabWeb Page View`
WHERE `creation` BETWEEN {some_date} AND {some_later_date}
"""
)
The query builder API makes this easier by providing a simple pythonic API to build SQL queries without limiting the flexibility of handwritten SQL.
The same query in the query builder would look something like
import frappe
from frappe.query_builder import DocType
from frappe.query_builder.functions import Count
from pypika.terms import Case
WebPageView = DocType("Web Page View") # you can also use frappe.qb.DocType to bypass an import
count_all = Count('*').as_("count")
case = Case().when(WebPageView.is_unique == "1", "1")
count_is_unique = Count(case).as_("unique_count")
result = (
frappe.qb.from_(WebPageView)
.select(WebPageView.path, count_all, count_is_unique)
.where(Web_Page_View.creation[some_date:some_later_date])
).run()
frappe.qb
Returns a Pypika query object which lets you build queries. Queries built using this object will be a
type from
pypika.dialects
, sprinkled with some Frappe sugar. Some of its methods are:
frappe.qb.from_(doctype)
lets you construct a from query to select data
Select query
query = frappe.qb.from_('Customer').select('id', 'fname', 'lname', 'phone')
The SQL query built is
SELECT `id`,`fname`,`lname`,`phone` FROM `tabCustomer`
A complex Select example
customers = frappe.qb.DocType('Customer')
q = (
frappe.qb.from_(customers)
.select(customers.id, customers.fname,customers.lname, customers.phone)
.where((customers.fname == 'Max') | (customers.id.like('RA%')) )
.where(customers.lname == 'Mustermann')
)
The SQL query built is
SELECT `id`,`fname`,`lname`,`phone` FROM `tabCustomer` WHERE (`fname`='Max' OR `id` LIKE 'RA%') AND `lname`='Mustermann'
Some noteworthy things
-
We have created a
customers
variable to refer to the table in the query. - Select can take any number of arguments, selecting various Fields.
- The '|' (pipe) or '&' (ampersand) operators can be used to refer to 'OR' or 'AND'.
-
Chaining the
where()
method appends 'AND' by default
You can read more about the other functions at the Pypika repo.
frappe.qb.Doctype(name_of_table)
Returns a PyPika table object which can be used elsewhere. It will automatically add 'tab' if necessary.
frappe.qb.Table(name_of_table)
Does the same thing as
frappe.qb.DocType
but will not append 'tab'. It's intended to be used with '__Auth' like tables.
Note: You should only use this if you know what you are doing.
frappe.qb.Field(name_of_coloum)
Return a PyPika field object, this represents a column. They are usually used to compare columns with values.
One example would be
lname = frappe.qb.Field("lname")
q = frapppe.qb.from_("customers").select("*").where(lname == 'Mustermann')
Executing queries
Queries built using the
frappe.qb
namespace are PyPika objects. They will have to be converted to string objects so that your database management system can recognize them.
To check how your query object translates, you can type cast it with
str
or use the
.get_sql
method they come with.
query = frappe.qb.from_('Customer').select('id', 'fname', 'lname', 'phone')
str(query)
# SELECT "id","fname","lname","phone" FROM "tabCustomer"
query.get_sql()
# SELECT "id","fname","lname","phone" FROM "tabCustomer"
str(query) == query.get_sql()
# True
Walk method
All queries built through
frappe.qb
are parameterized by default. All input fields, raw values, and functions are separated as Named parameters and sent to the database as a dictionary. Parameterization is done to sanitize queries which would prevent SQL injections.
You use the
walk
method to check out which parts are parameterized. It returns the parameterized query and the corresponding dictionary.
doctype = frappe.qb.DocType("DocType")
frappe.qb.from_(doctype).select('*').where(doctype.name == "somename").walk()
# ('SELECT * FROM `tabDocType` WHERE `name`=%(param1)s', {'param1': 'somename'})
Run method
This is the most preferred method to execute your queries. Every valid query has the
run
method which you
may use to execute the query with.
frappe.qb.from_('Customer').select('id', 'fname', 'lname', 'phone').run()
The
run
method accepts
kwargs
which will be passed on while the query is being executed. You may pass whatever options are available in
frappe.db.sql
, through the
run
method.
To run debug on your query, or get the result in the form of List[Dict], you may use the following respectively:
In [7]: frappe.qb.from_('ToDo').select('name').run(debug=True)
SELECT "name" FROM "tabToDo"
Execution time: 0.0 sec
Out[7]: [('8d765f73a2',)]
In [8]: frappe.qb.from_('ToDo').select('name').run(as_dict=True)
Out[8]: [{'name': '8d765f73a2'}]
The
run
method internally calls the lower levelfrappe.db.sql
API.
frappe.db.sql
ou may choose to directly pass your query objects to
frappe.db.sql
too. This ignores permisions and paramaterisation of queries.
query = frappe.qb.from_('Customer').select('id', 'fname', 'lname', 'phone')
frappe.db.sql(query)
frappe.query_builder.functions
This module provides standard functions you might need while building queries, like
Count()
and
Sum().
Joins and Sub-queries
You can check
pypika documentation
to join tables and add subqueries. Instead of
Table
, use
frappe.qb.DocType
Example:
HasRole = frappe.qb.DocType('Has Role')
CustomRole = frappe.qb.DocType('Custom Role')
query = (frappe.qb.from_(HasRole)
.inner_join(CustomRole)
.on(CustomRole.name == HasRole.parent)
.select(CustomRole.page, HasRole.parent, HasRole.role))
Simple functions
Say you want to count all the entries in a Notes table. You could do something like
from frappe.query_builder.functions import Count
Notes = frappe.qb.DocType("Notes")
count_pages = Count(Notes.content).as_("Pages")
result = frappe.qb.from_(Notes).select(count_pages).run(as_dict=True)
Custom Functions
frappe.query_builder.functions
is a superset of
pypika.functions
, so it has all PyPika functions and some custom ones we made. You can make your custom functions by importing the
CustomFunction
class from PyPika
One implementation of the DateDiff function
from pypika import CustomFunction
customers = Tables('Customer')
DateDiff = CustomFunction('DATE_DIFF', ['interval', 'start_date', 'end_date'])
q = Query.from_(customers).select(
DateDiff('day', customers.created_date, customers.updated_date)
)
If we print
q
, we would get
SELECT DATE_DIFF('day',"created_date","updated_date") FROM "Customer"
Notice how we specify arguments and the actual SQL text. The exact format might not work for more complex functions. The advanced section covers more complicated methods.
Constant Column
ConstantColumn
is a class to define a pseudo column with a constant value.
from frappe.query_builder.custom import ConstantColumn
frappe.qb.from_("DocType").select("name", ConstantColumn("john").as_("user"))
# SELECT `name`,'john' `user` FROM `tabDocType`
Here we define a column user with the value "john."
Advanced
Special functions
One such function is
Match Against
. It's different because it has a chained against argument. To implement something like this you need to inherit from PyPika's
DistinctOptionFunction
class.
The current MATCH class looks something like
from pypika.functions import DistinctOptionFunction
from pypika.utils import builder
class MATCH(DistinctOptionFunction):
def __init__(self, column: str, *args:
super(MATCH, self)._init_(" MATCH", column, *args)
self._Against = False
def get_function_sql(self, **kwargs):
s = super(DistinctOptionFunction, self).get_function_sql(**kwargs)
if self._Against:
return f"{s} AGAINST (f'+{self._Against}*') IN BOOLEAN MODE)"
return s
@builder
def Against(self, text: str):
self._Against = text
-
The
__init__()
method works similar to CustomFunction class above. You mention all the arguments and the SQL text. -
The
Against()
method only stores a value that will be used in theget_function_sql()
-
It also has the
@builder
wrapper. In short, it lets these functions be chainable by making a copy of the object. -
We have wrapped the
get_function_sql()
method, which allows us to append the required SQL text for Against. - this can further be extended to use any number other chains.
In use, the Match class looks like this
from frappe.query_builder.functions import Match
match = Match("Coloum name").Against("Some_text_match")
# MATCH('Coloum name') AGAINST ('+Some_text_match*' IN BOOLEAN MODE)
Utils
ImportMapper(dict)
In the rare case where you have different functions for different SQL dialects, Which do the same thing, you can use the ImportMapper Utility. It maps functions based on the SQL dialect, so one query works across different SQL dialects.
It takes in a dict which maps functions to databases.
For example the the mapping for GroupConat looks like this
from frappe.query_builder.utils import ImportMapper, db_type_is
from frappe.query_builder.custom import GROUP_CONCAT, STRING_AGG
GroupConcat = ImportMapper(
{
db_type_is.MARIADB: GROUP_CONCAT,
db_type_is.POSTGRES: STRING_AGG
}
)