Feature Request: High-Level API for Simple Doctype JOIN Operations

Frappe Framework
,
1

Problem Statement

Currently, Frappe provides frappe.get_all() as a clean, high-level API for querying Doctypes. However, when developers need to join two independent Doctypes, they are forced to drop down to either:

  • frappe.db.sql() — raw SQL, loses permission checks, error-prone
  • frappe.qb — better, but requires more boilerplate and SQL familiarity

This creates an unnecessary gap for a very common use case — especially for junior developers or those building quick reports/scripts.

Proposed Solution

Either introduce a new API or extend frappe.get_all() to support a join parameter for simple cross-Doctype joins.

Option 1 — Extend frappe.get_all():

frappe.get_all(
    "Customer",
    join={
        "doctype": "Sales Order",
        "on": ["Customer.name", "Sales Order.customer"],
        "type": "left"  # left, inner, right
    },
    fields=["Customer.name", "Sales Order.grand_total"],
    filters={"Customer.country": "Bangladesh"}
)

Option 2 — New dedicated API:

frappe.get_joined(
    doctype="Customer",
    join_doctype="Sales Order",
    on=["Customer.name", "Sales Order.customer"],
    join_type="left",
    fields=["Customer.name", "Sales Order.grand_total"],
    filters={"Customer.country": "Bangladesh"}
)

Why This Matters

  • Consistency — keeps the same familiar API style developers already use
  • Permission checks — unlike raw SQL, a high-level API can enforce Frappe’s permission layer automatically
  • Accessibility — lowers the barrier for developers who don’t need full frappe.qb power
  • Very common use case — joining two related Doctypes (e.g. Customer + Sales Order, Employee + Attendance) is needed constantly in reports and scripts

Current Workaround

Developers currently rely on frappe.qb:

from frappe.query_builder import DocType

Customer = DocType("Customer")
SalesOrder = DocType("Sales Order")

results = (
    frappe.qb.from_(Customer)
    .left_join(SalesOrder)
    .on(SalesOrder.customer == Customer.name)
    .select(Customer.name, SalesOrder.grand_total)
).run(as_dict=True)

While frappe.qb works well, it requires extra imports, more verbosity, and familiarity with a query-builder pattern — which is overkill for simple joins.

Request

Would love to hear the core team’s thoughts on:

  1. Whether extending frappe.get_all() with a join parameter is feasible
  2. Or if a new lightweight frappe.get_joined() API makes more sense

Happy to contribute to the implementation if there’s interest!

3 Likes