Frappe Syntax Query Builder

Frappe Query Builder (frappe.qb)

Quick Reference

from frappe.query_builder import DocType, Field
from frappe.query_builder.functions import Count, Sum, IfNull
from frappe.query_builder.custom import ConstantColumn, GROUP_CONCAT
from frappe.query_builder.terms import SubQuery
from frappe.query_builder.utils import ImportMapper, db_type_is
from pypika.terms import Case, ValueWrapper
from pypika import CustomFunction, Order

---

Decision Tree

Need to query the database?
│
├─ Simple get/list → frappe.db.get_value(), frappe.get_all()
│  (See frappe-core-database skill)
│
├─ Complex query with joins/aggregates/subqueries → frappe.qb ✓
│
├─ Very complex SQL not expressible in qb → frappe.db.sql()
│  (ALWAYS use parameterized values: frappe.db.sql(query, values))
│
└─ Need cross-DB compatibility → frappe.qb + ImportMapper ✓

Using frappe.qb?
│
├─ Table reference → DocType("Sales Order") — NEVER use Table()
├─ Field reference → dt.field_name or Field("field_name")
├─ Execute → .run() for tuples, .run(as_dict=True) for dicts
├─ DB-specific function → ImportMapper({db_type_is.MARIADB: X, db_type_is.POSTGRES: Y})
└─ Get SQL string → query.get_sql() — NEVER pass to frappe.db.sql()

---

Core Patterns

SELECT with DocType

# ALWAYS use DocType() for table references — adds "tab" prefix
so = frappe.qb.DocType("Sales Order")
soi = frappe.qb.DocType("Sales Order Item")

orders = (
    frappe.qb.from_(so)
    .select(so.name, so.customer, so.grand_total)
    .where(so.status == "To Deliver and Bill")
    .where(so.docstatus == 1)
    .orderby(so.creation, order=Order.desc)
    .limit(20)
    .run(as_dict=True)
)

JOIN

result = (
    frappe.qb.from_(so)
    .left_join(soi).on(soi.parent == so.name)
    .select(so.name, so.customer, soi.item_code, soi.qty)
    .where(so.docstatus == 1)
    .where(soi.item_code.like("ITEM-%"))
    .run(as_dict=True)
)

Aggregation

from frappe.query_builder.functions import Count, Sum

gl = frappe.qb.DocType("GL Entry")
result = (
    frappe.qb.from_(gl)
    .select(gl.account, Sum(gl.debit).as_("total_debit"), Count("*").as_("entries"))
    .where(gl.docstatus == 1)
    .groupby(gl.account)
    .run(as_dict=True)
)

# Shortcut aggregation methods
total = frappe.qb.sum("GL Entry", "debit", filters={"account": "Sales"})
max_qty = frappe.qb.max("Stock Ledger Entry", "actual_qty", filters={"item_code": "ITEM-001"})

INSERT / UPDATE / DELETE

# INSERT
frappe.qb.into("Activity Log").columns("user", "action").insert("admin", "login").run()

# UPDATE
customer = frappe.qb.DocType("Customer")
(frappe.qb.update(customer)
    .set(customer.status, "Active")
    .where(customer.name == "CUST-001")
    .run())

# DELETE
frappe.qb.from_("Error Log").delete().where(Field("creation") < "2024-01-01").run()

---

Filtering

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

# Equality
.where(dt.status == "Open")

# OR (pipe operator)
.where((dt.status == "Open") | (dt.status == "Draft"))

# AND (chain .where() calls)
.where(dt.status == "Open")
.where(dt.docstatus == 1)

# LIKE
.where(dt.customer.like("CUST-%"))

# IN
.where(dt.status.isin(["Open", "Draft"]))

# BETWEEN (bracket syntax)
.where(dt.creation[start_date:end_date])

# NULL checks
.where(dt.email.isnotnull())
.where(dt.phone.isnull())

# Comparison
.where(dt.grand_total > 1000)
.where(dt.grand_total >= 500)
.where(dt.status != "Cancelled")

---

Cross-DB Compatibility

from frappe.query_builder.utils import ImportMapper, db_type_is
from frappe.query_builder.custom import GROUP_CONCAT, STRING_AGG

# ImportMapper selects correct function per database
GroupConcat = ImportMapper({
    db_type_is.MARIADB: GROUP_CONCAT,
    db_type_is.POSTGRES: STRING_AGG,
})

dt = frappe.qb.DocType("Has Role")
result = (
    frappe.qb.from_(dt)
    .select(dt.parent, GroupConcat(dt.role))
    .groupby(dt.parent)
    .run(as_dict=True)
)

---

Anti-patterns

1. NEVER pass qb query to `frappe.db.sql()` — bypasses parameterization 2. NEVER use `Table()` for DocTypes — use DocType() (adds tab prefix) 3. NEVER forget `.run()` — without it you get a query object, not results 4. NEVER use raw SQL strings in `frappe.get_all(fields=[...])` — use dict syntax 5. ALWAYS use `ImportMapper` for DB-specific functions 6. ALWAYS chain `.run(as_dict=True)` when you need dicts — default is tuples

---

Version Differences

---

Reference Files

• Functions & Aggregates — All available qb functions
• Migration Guide — Converting raw SQL and get_all patterns
• Cross-DB Patterns — ImportMapper and DB-specific functions

Cross-DB Patterns — frappe.qb

The Problem

Frappe supports both MariaDB and PostgreSQL. Some SQL functions have different names or syntax between databases. Writing raw SQL locks you to one DB.

ImportMapper — The Solution

ImportMapper automatically selects the correct function based on frappe.conf.db_type.

from frappe.query_builder.utils import ImportMapper, db_type_is

db_type_is Enum

Common ImportMapper Patterns

GROUP_CONCAT / STRING_AGG

from frappe.query_builder.custom import GROUP_CONCAT, STRING_AGG

GroupConcat = ImportMapper({
    db_type_is.MARIADB: GROUP_CONCAT,
    db_type_is.POSTGRES: STRING_AGG,
})

dt = frappe.qb.DocType("Has Role")
result = (
    frappe.qb.from_(dt)
    .select(dt.parent, GroupConcat(dt.role))
    .groupby(dt.parent)
    .run(as_dict=True)
)
# MariaDB:   GROUP_CONCAT(`role`)
# PostgreSQL: STRING_AGG(`role`, ',')

Full-Text Search

from frappe.query_builder.custom import MATCH, TO_TSVECTOR

FullTextSearch = ImportMapper({
    db_type_is.MARIADB: MATCH,
    db_type_is.POSTGRES: TO_TSVECTOR,
})

dt = frappe.qb.DocType("Web Page")
# MariaDB:   MATCH(`content`) AGAINST('search term')
# PostgreSQL: TO_TSVECTOR(`content`) @@ PLAINTO_TSQUERY('search term')

String Position

from frappe.query_builder.functions import Locate, Strpos

StringPosition = ImportMapper({
    db_type_is.MARIADB: Locate,
    db_type_is.POSTGRES: Strpos,
})

Auto-Handled Differences

These functions are automatically translated by the query builder — no ImportMapper needed:

PostgreSQL-Specific Field Translations

The Postgres builder automatically maps system table queries:

Building Cross-DB Compatible Code

Rule 1: ALWAYS use DocType(), NEVER raw table names

# ✅ CORRECT — DocType adds "tab" prefix correctly per DB
dt = frappe.qb.DocType("Sales Order")

# ❌ WRONG — no "tab" prefix, may break
from pypika import Table
dt = Table("tabSales Order")  # Don't do this

Rule 2: Use ImportMapper for DB-specific functions

# ✅ CORRECT — works on both MariaDB and PostgreSQL
GroupConcat = ImportMapper({
    db_type_is.MARIADB: GROUP_CONCAT,
    db_type_is.POSTGRES: STRING_AGG,
})

# ❌ WRONG — breaks on PostgreSQL
from frappe.query_builder.custom import GROUP_CONCAT
# Direct use only works on MariaDB

Rule 3: Avoid DB-specific SQL in frappe.db.sql

# ❌ WRONG — MariaDB-only syntax
frappe.db.sql("SELECT GROUP_CONCAT(role) FROM `tabHas Role`")

# ✅ CORRECT — use query builder with ImportMapper
GroupConcat = ImportMapper({...})
frappe.qb.from_(dt).select(GroupConcat(dt.role)).run()

Rule 4: Test on both databases when possible

If your Frappe app claims PostgreSQL support, test queries on both databases. The query builder handles most differences, but edge cases can slip through with custom functions or raw SQL fragments.

Checking Current Database Type

# In Python
db_type = frappe.conf.db_type  # "mariadb" or "postgres"

# Conditional logic (when ImportMapper isn't enough)
if frappe.conf.db_type == "mariadb":
    # MariaDB-specific logic
    pass
elif frappe.conf.db_type == "postgres":
    # PostgreSQL-specific logic
    pass

Functions & Aggregates — frappe.qb

Standard Aggregates

from frappe.query_builder.functions import Count, Sum, Avg, Min, Max

dt = frappe.qb.DocType("GL Entry")

# Count
Count("*")                        # COUNT(*)
Count(dt.name).as_("total")       # COUNT(`name`) AS `total`

# Sum
Sum(dt.debit).as_("total_debit")  # SUM(`debit`) AS `total_debit`

# Average
Avg(dt.amount).as_("avg_amount")  # AVG(`amount`) AS `avg_amount`

# Min / Max
Min(dt.creation).as_("oldest")
Max(dt.creation).as_("newest")

Shortcut Aggregation Methods

# Patched onto frappe.qb — convenience wrappers
frappe.qb.sum("GL Entry", "debit", filters={"account": "Sales"})
frappe.qb.max("Stock Ledger Entry", "actual_qty", filters={"item_code": "ITEM-001"})
frappe.qb.min("Sales Order", "creation", filters={"status": "Open"})
frappe.qb.avg("Sales Invoice Item", "rate", filters={"item_code": "ITEM-001"})

Built-in Functions (frappe.query_builder.functions)

Custom Functions (frappe.query_builder.custom)

Case Expressions

from pypika.terms import Case

# Simple CASE
case = (
    Case()
    .when(dt.status == "Open", "Active")
    .when(dt.status == "Closed", "Inactive")
    .else_("Unknown")
    .as_("status_label")
)

# CASE with aggregation
case_unique = Case().when(dt.is_unique == "1", "1")
count_unique = Count(case_unique).as_("unique_visits")

# Full query with CASE
result = (
    frappe.qb.from_(dt)
    .select(dt.name, case)
    .run(as_dict=True)
)

Subqueries

from frappe.query_builder.terms import SubQuery

# Subquery in WHERE ... IN
soi = frappe.qb.DocType("Sales Order Item")
so = frappe.qb.DocType("Sales Order")

inner = (
    frappe.qb.from_(soi)
    .select(soi.parent)
    .where(soi.item_code == "ITEM-001")
)

orders = (
    frappe.qb.from_(so)
    .select(so.name, so.customer)
    .where(so.name.isin(SubQuery(inner)))
    .run(as_dict=True)
)

# Subquery as computed field
count_sub = SubQuery(
    frappe.qb.from_(soi)
    .select(Count("*"))
    .where(soi.parent == so.name)
)

result = (
    frappe.qb.from_(so)
    .select(so.name, count_sub.as_("item_count"))
    .run(as_dict=True)
)

ValueWrapper & ConstantColumn

from pypika.terms import ValueWrapper
from frappe.query_builder.custom import ConstantColumn

# Literal value as field
result = (
    frappe.qb.from_(dt)
    .select(dt.name, ValueWrapper("Sales Order").as_("doctype"))
    .run(as_dict=True)
)
# Returns: [{"name": "SO-001", "doctype": "Sales Order"}, ...]

# ConstantColumn — similar but for column-like constants
result = (
    frappe.qb.from_(dt)
    .select(dt.name, ConstantColumn("Active").as_("status"))
    .run(as_dict=True)
)

Custom PyPika Functions

from pypika import CustomFunction

# Define custom SQL function
JsonExtract = CustomFunction("JSON_EXTRACT", ["field", "path"])

dt = frappe.qb.DocType("Custom DocType")
result = (
    frappe.qb.from_(dt)
    .select(dt.name, JsonExtract(dt.json_field, "$.key").as_("value"))
    .run(as_dict=True)
)

Query Execution

query = frappe.qb.from_(dt).select(dt.name).where(dt.status == "Open")

# Execute and get results
tuples = query.run()                    # [(name,), ...]
dicts = query.run(as_dict=True)         # [{"name": "..."}, ...]

# Get SQL string (for debugging)
sql = query.get_sql()                   # "SELECT `name` FROM `tabSales Order` WHERE ..."

# NEVER do this — bypasses parameterization:
# frappe.db.sql(query.get_sql())  # ❌ WRONG

Migration Guide — Raw SQL to frappe.qb

frappe.get_all / frappe.get_list Migration

Aggregation in fields

# ❌ OLD — raw SQL string in fields
frappe.get_all("Stock Ledger Entry",
    fields=["sum(actual_qty) as qty"],
    filters={"item_code": "ITEM-001"}
)

# ✅ NEW — dict syntax
frappe.get_all("Stock Ledger Entry",
    fields=[{"SUM": "actual_qty", "as": "qty"}],
    filters={"item_code": "ITEM-001"}
)

# ✅ NEW — function objects
from frappe.query_builder import DocType
from frappe.query_builder.functions import Sum
sle = DocType("Stock Ledger Entry")
frappe.get_all("Stock Ledger Entry",
    fields=[(Sum(sle.actual_qty)).as_("qty")],
    filters={"item_code": "ITEM-001"}
)

IFNULL in filters

# ❌ OLD — raw SQL in filter key
frappe.get_all("Item",
    filters={"ifnull(is_stock_item, 0)": 0}
)

# ✅ NEW — IfNull function
from frappe.query_builder import Field
from frappe.query_builder.functions import IfNull
frappe.get_all("Item",
    filters=[IfNull(Field("is_stock_item"), 0) == 0]
)

DISTINCT

# ❌ OLD
frappe.get_all("Stock Ledger Entry",
    fields=["distinct batch_no"]
)

# ✅ NEW
frappe.get_all("Stock Ledger Entry",
    fields=["batch_no"],
    distinct=True
)

Literal values as fields

# ❌ OLD
frappe.get_all("Leave Application",
    fields=["'Leave Application' as doctype", "name"]
)

# ✅ NEW
from pypika.terms import ValueWrapper
frappe.get_all("Leave Application",
    fields=[ValueWrapper("Leave Application").as_("doctype"), "name"]
)

ORDER BY with functions

# ❌ OLD
frappe.get_all("Stock Ledger Entry",
    order_by="timestamp(posting_date, posting_time), creation"
)

# ✅ NEW — full qb query
from frappe.query_builder.functions import Timestamp
sle = frappe.qb.DocType("Stock Ledger Entry")
result = (
    frappe.qb.from_(sle)
    .select(sle.star)
    .orderby(Timestamp(sle.posting_date, sle.posting_time))
    .orderby(sle.creation)
    .run(as_dict=True)
)

run=False Behavior Change

# In v14+, run=False returns a QueryBuilder object, NOT a SQL string
query = frappe.get_all("Sales Order", run=False)
type(query)       # <class 'frappe.query_builder.builder.MariaDB'>

# To get the SQL string:
sql_string = query.get_sql()

frappe.db.sql Migration

Simple SELECT

# ❌ OLD — raw SQL
result = frappe.db.sql("""
    SELECT name, customer, grand_total
    FROM `tabSales Order`
    WHERE status = %s AND docstatus = 1
    ORDER BY creation DESC
    LIMIT 20
""", ("To Deliver and Bill",), as_dict=True)

# ✅ NEW — query builder
from pypika import Order
so = frappe.qb.DocType("Sales Order")
result = (
    frappe.qb.from_(so)
    .select(so.name, so.customer, so.grand_total)
    .where(so.status == "To Deliver and Bill")
    .where(so.docstatus == 1)
    .orderby(so.creation, order=Order.desc)
    .limit(20)
    .run(as_dict=True)
)

JOIN query

# ❌ OLD
result = frappe.db.sql("""
    SELECT so.name, so.customer, soi.item_code, soi.qty
    FROM `tabSales Order` so
    LEFT JOIN `tabSales Order Item` soi ON soi.parent = so.name
    WHERE so.docstatus = 1
""", as_dict=True)

# ✅ NEW
so = frappe.qb.DocType("Sales Order")
soi = frappe.qb.DocType("Sales Order Item")
result = (
    frappe.qb.from_(so)
    .left_join(soi).on(soi.parent == so.name)
    .select(so.name, so.customer, soi.item_code, soi.qty)
    .where(so.docstatus == 1)
    .run(as_dict=True)
)

GROUP BY with aggregation

# ❌ OLD
result = frappe.db.sql("""
    SELECT account, SUM(debit) as total_debit, COUNT(*) as entries
    FROM `tabGL Entry`
    WHERE docstatus = 1
    GROUP BY account
    HAVING SUM(debit) > 0
""", as_dict=True)

# ✅ NEW
from frappe.query_builder.functions import Count, Sum
gl = frappe.qb.DocType("GL Entry")
total_debit = Sum(gl.debit).as_("total_debit")
result = (
    frappe.qb.from_(gl)
    .select(gl.account, total_debit, Count("*").as_("entries"))
    .where(gl.docstatus == 1)
    .groupby(gl.account)
    .having(Sum(gl.debit) > 0)
    .run(as_dict=True)
)

When to Keep Using frappe.db.sql

Some queries are too complex for the query builder:

• UNION queries with different structures
• Complex nested subqueries with multiple levels
• Database-specific syntax not covered by PyPika
• Temporary tables or CTEs (Common Table Expressions)

For these cases, ALWAYS use parameterized queries:

# ✅ Safe raw SQL with parameterized values
result = frappe.db.sql("""
    SELECT name FROM `tabSales Order`
    WHERE customer = %(customer)s
    AND creation > %(date)s
""", {"customer": customer, "date": start_date}, as_dict=True)

# ❌ NEVER interpolate values into SQL
result = frappe.db.sql(f"SELECT * FROM `tabSales Order` WHERE customer = '{customer}'")