Efficient Cross-DB Queries with frappe.qb in Empress

Developers
1

Introduction

Welcome to this comprehensive guide where we’ll delve into the feature frappe.qb, a query builder written around PyPika to create a single interface for cross-db queries in Empress. We’ll explore the nitty-gritty of this feature, from its basic functionalities to the more advanced use-cases, emphasizing backend functionality, code integration, and technical details.

Introduction to frappe.qb

As a developer, you often need to retrieve specific data from the database while building apps. The traditional way to do this is by using frappe.db.sql and writing raw SQL queries as shown below:

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}
    """
)

However, the frappe.qb feature simplifies this process by providing a Pythonic API to build SQL queries without limiting the flexibility of handwritten SQL. The same query using frappe.qb would look like this:

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()

Delving into frappe.qb’s Methods

The frappe.qb returns a Pypika query object which lets you build queries. Here are some of its significant methods:

1. frappe.qb.from_(doctype)

This method allows you to construct a from query to select data. Here’s a basic select query:

query = frappe.qb.from_('Customer').select('id', 'fname', 'lname', 'phone')

This will build the SQL query:

SELECT `id`,`fname`,`lname`,`phone` FROM `tabCustomer`

2. frappe.qb.Doctype(name_of_table) and frappe.qb.Table(name_of_table)

The frappe.qb.DocType method returns a PyPika table object which can be used elsewhere. It will automatically add ‘tab’ if necessary. The frappe.qb.Table does the same but will not append ‘tab’. It’s intended to be used with ‘__Auth’ like tables.

3. frappe.qb.Field(name_of_column)

This method returns a PyPika field object, representing a column. They are usually used to compare columns with values.

lname = frappe.qb.Field("lname")
q = frapppe.qb.from_("customers").select("*").where(lname == 'Mustermann')

4. Executing Queries

Queries built using the frappe.qb namespace are PyPika objects. They need to be converted to string objects so that your DBMS can recognize them. This can be done with str or the .get_sql method.

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

5. frappe.query_builder.functions

This module provides standard functions you might need while building queries, like Count() and Sum().

Conclusion

The frappe.qb feature in Empress offers an efficient way to build SQL queries in a Pythonic manner, simplifying the process of data retrieval from databases. It provides a range of methods and functionalities, from basic select queries to more complex operations such as building custom functions and executing queries. By leveraging this feature, developers can greatly streamline their software development and customization tasks, while maintaining the flexibility and power of SQL.