分类： 查询构建器

查询构建器

frappe.qb 是一个基于 PyPika 构建的查询构建器，用于为跨数据库查询提供统一接口。

在开发应用程序时，您经常需要从数据库中检索特定数据。一种方法是使用 frappe.db.sql 并编写原始 SQL 查询。

可能类似于这样

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

查询构建器 API 通过提供简单的 Pythonic API 来构建 SQL 查询，同时不限制手写 SQL 的灵活性，从而使这一过程更加容易。

同样的查询在查询构建器中看起来会是这样

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

返回一个 Pypika 查询对象，用于构建查询。使用此对象构建的查询将是 pypika.dialects 中的类型，并带有一些 Frappe 的增强功能。它的一些方法包括：

frappe.qb.from_(doctype)

允许您构建一个 from 查询来选择数据。

选择查询

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

构建的 SQL 查询为

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

一个复杂的 Select 示例

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

构建的 SQL 查询为

SELECT `id`,`fname`,`lname`,`phone` FROM `tabCustomer` WHERE (`fname`='Max' OR `id` LIKE 'RA%') AND `lname`='Mustermann'

一些值得注意的事项

  • 我们创建了一个 customers 变量来引用查询中的表。
  • Select 可以接受任意数量的参数，选择各种字段。
  • 可以使用 ‘|’（管道符）或 ‘&’（与符号）运算符来表示 ‘OR’ 或 ‘AND’。
  • 链式调用 where() 方法默认会追加 ‘AND’。

您可以在 Pypika 仓库中阅读有关其他函数的更多信息。

frappe.qb.Doctype(name_of_table)

返回一个 PyPika 表对象，可在其他地方使用。如有必要，它会自动添加 ‘tab’ 前缀。

frappe.qb.Table(name_of_table)

与 frappe.qb.DocType 功能相同，但不会追加 ‘tab’ 前缀。它旨在用于像 ‘__Auth’ 这样的表。

注意：只有在您清楚自己在做什么的情况下才应使用此功能。

frappe.qb.Field(name_of_coloum)

返回一个 PyPika 字段对象，代表一个列。它们通常用于将列与值进行比较。

一个例子是

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

执行查询

使用 frappe.qb 命名空间构建的查询是 PyPika 对象。它们必须转换为字符串对象，以便您的数据库管理系统能够识别它们。

要检查您的查询对象如何转换，您可以使用 str 进行类型转换，或使用它们自带的 .get_sql 方法。

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 方法

所有通过 frappe.qb 构建的查询默认都是参数化的。所有输入字段、原始值和函数都被分离为命名参数，并以字典形式发送到数据库。参数化是为了净化查询，防止 SQL 注入。

您可以使用 walk 方法查看哪些部分被参数化了。它返回参数化的查询和相应的字典。

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 方法

这是执行查询最推荐的方法。每个有效的查询都有 run 方法，您可以使用它来执行查询。

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

run 方法接受 kwargs，这些参数将在查询执行时传递。您可以通过 run 方法传递 frappe.db.sql 中可用的任何选项。

要对查询进行调试，或以 List[Dict] 的形式获取结果，您可以分别使用以下方法：

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'}]

run 方法在内部调用更底层的 frappe.db.sql API。

frappe.db.sql

您也可以选择直接将查询对象传递给 frappe.db.sql。但这会忽略查询的权限和参数化。

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

frappe.query_builder.functions

此模块提供了您在构建查询时可能需要的标准函数，例如 Count() 和 Sum().。

连接和子查询

您可以查看 pypika 文档来了解如何连接表和添加子查询。请使用 frappe.qb.DocType 代替 Table。

示例：

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

简单函数

假设您想计算 Notes 表中的所有条目。您可以这样做

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)

JSON 函数

注意：此功能在 v16+ 版本中可用。

这些辅助函数通过在内部映射到正确的 SQL 方言，使 JSON 查询能够在 MariaDB 和 Postgres 之间移植。

使用场景：

  1. 按路径读取 JSON 对象值
  2. 读取标量/文本值以进行过滤
  3. 检查 JSON 对象/数组是否包含某个值

可用的辅助函数：

  1. JSONExtract(field, path)
  2. JSONValue(field, path)
  3. JSONContains(target, candidate)

示例：JSON 对象字段

import frappe
from frappe.query_builder.functions import JSONExtract, JSONValue

CustomerProfile = frappe.qb.DocType("Customer Profile")

# preferences_json:
# {"notifications": {"email": true, "sms": false}, "language": "en"}

query = (
    frappe.qb.from_(CustomerProfile)
    .select(
        CustomerProfile.customer_name,
        JSONValue(CustomerProfile.preferences_json, "$.language").as_("preferred_language"),
        JSONValue(CustomerProfile.preferences_json, "$.notifications.email").as_("email_notifications"),
    )
    .where(JSONValue(CustomerProfile.preferences_json, "$.notifications.email") == "true")
)

示例：JSON 列表字段

import frappe
from frappe.query_builder.functions import JSONContains, JSONExtract

SalesOrder = frappe.qb.DocType("Sales Order")

# applied_discounts_json:
# {"codes": ["WELCOME10", "FREESHIP", "VIP"]}

query = (
    frappe.qb.from_(SalesOrder)
    .select(SalesOrder.name, SalesOrder.customer)
    .where(JSONContains(JSONExtract(SalesOrder.applied_discounts_json, "$.codes"), "FREESHIP"))
)

自定义函数

frappe.query_builder.functions 是 pypika.functions 的超集，因此它拥有所有 PyPika 函数以及我们创建的一些自定义函数。您可以通过从 PyPika 导入 CustomFunction 类来创建自定义函数。

DateDiff 函数的一个实现

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

如果我们打印 q，我们会得到

SELECT DATE_DIFF('day',"created_date","updated_date") FROM "Customer"

请注意我们如何指定参数和实际的 SQL 文本。确切的格式可能不适用于更复杂的函数。高级部分涵盖了更复杂的方法。

常量列

ConstantColumn 是一个用于定义具有常量值的伪列的类。

from frappe.query_builder.custom import ConstantColumn

frappe.qb.from_("DocType").select("name", ConstantColumn("john").as_("user"))
# SELECT `name`,'john' `user` FROM `tabDocType`

这里我们定义了一个值为“john”的列 user。

高级

特殊函数

其中一个这样的函数是 Match Against。它之所以特殊，是因为它有一个链式的 against 参数。要实现类似的功能，你需要继承 PyPika 的 DistinctOptionFunction 类。

当前的 MATCH 类看起来像这样


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
  • __init__() 方法的工作方式类似于上面的 CustomFunction 类。你需要列出所有参数和 SQL 文本。
  • Against() 方法仅存储一个值，该值将在 get_function_sql() 中使用
  • 它还有 @builder 包装器。简而言之，它通过复制对象使这些函数可以链式调用。
  • 我们包装了 get_function_sql() 方法，这使我们能够追加 Against 所需的 SQL 文本。
  • 这可以进一步扩展以使用任意数量的其他链。

在使用中，Match 类看起来像这样

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)

工具

ImportMapper(dict)

在极少数情况下，对于不同的 SQL 方言，你有不同的函数，但它们执行相同的操作，你可以使用 ImportMapper 工具。它根据 SQL 方言映射函数，因此一个查询可以在不同的 SQL 方言中工作。

它接受一个将函数映射到数据库的字典。

例如，GroupConat 的映射看起来像这样


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