Common Query Patterns

This is a quick reference for reading data in Frappe: the server-side frappe.get_all family, the client-side frappe.db helpers, and set_query for limiting what a Link field offers. For joins, aggregates, and anything the dictionary filters can't express, see Query Builder & PyPika Extensions.

Server Side (Python)

List Records

frappe.get_all ignores user permissions; frappe.get_list applies them. Both take the same arguments:

contacts = frappe.get_all(
    "Contact",
    filters={"status": "Open"},
    fields=["name", "full_name", "email_id"],
    order_by="creation desc",
    start=0,
    page_length=20,
)

• filters takes a dict ({"status": "Open"}), operators ({"creation": [">", "2024-01-01"]}), or a list of [field, operator, value] lists.

• pluck="name" returns a flat list of one field instead of dicts.

• limit is an alias for page_length. Leave both out to get every row. (The HTTP endpoint frappe.client.get_list and the JavaScript helpers default to 20.)

Filter on a Child Table

Use the four-item form [child_doctype, field, operator, value]. For example, every Contact linked to a given Customer through the links (Dynamic Link) table:

contact_names = frappe.get_all(
    "Contact",
    filters=[
        ["Dynamic Link", "link_doctype", "=", "Customer"],
        ["Dynamic Link", "link_name", "=", "<CUSTOMER_NAME>"],
    ],
    pluck="name",
)

Select Child Table Columns

Reference child columns with the backtick table syntax, and Frappe joins the child table for you. You get one row per child row:

rows = frappe.get_all(
    "Sales Order",
    filters={"docstatus": 1},
    fields=["name", "customer", "`tabSales Order Item`.item_code", "`tabSales Order Item`.qty"],
)

Read a Single Value

status = frappe.db.get_value("Customer", "<CUSTOMER_NAME>", "disabled")
email, phone = frappe.db.get_value("Contact", {"user": frappe.session.user}, ["email_id", "mobile_no"])
exists = frappe.db.exists("Customer", "<CUSTOMER_NAME>")
contact_name = frappe.db.exists("Contact", {"user": frappe.session.user})

frappe.db.exists accepts a name or a filter dict and returns the matching name (or None).

Query Builder

For anything more involved, use the query builder. Note that conditions compare Field objects, not bare names:

Contact = frappe.qb.DocType("Contact")
rows = (
    frappe.qb.from_(Contact)
    .select(Contact.name, Contact.full_name)
    .where(Contact.last_name == "<LAST_NAME>")
    .orderby(Contact.creation, order=frappe.qb.desc)
    .limit(20)
).run(as_dict=True)

Client Side (JavaScript)

The frappe.db helpers return promises and respect the current user's permissions.

List Records

const records = await frappe.db.get_list("Item Price", {
    fields: ["item_code", "price_list_rate"],
    filters: { price_list: "Standard Selling" },
    order_by: "item_code asc",
    limit: 100,
});
records.forEach((row) => console.log(row.item_code, row.price_list_rate));

get_list returns 20 rows unless you pass limit.

Read a Value or a Document

// Single field; the value is on r.message
const r = await frappe.db.get_value("Customer", "<CUSTOMER_NAME>", "customer_group");
console.log(r.message.customer_group);

// Whole document, including child tables
const customer = await frappe.db.get_doc("Customer", "<CUSTOMER_NAME>");

// Single doctype value
const currency = await frappe.db.get_single_value("Global Defaults", "default_currency");

// Count
const open_orders = await frappe.db.count("Sales Order", { filters: { status: "To Deliver and Bill" } });

Check Whether a Record Exists

On the client, frappe.db.exists(doctype, name) only checks by name. To check by other fields, ask for the name with a filter:

const exists = await frappe.db.exists("Customer", "<CUSTOMER_NAME>");

const r = await frappe.db.get_value("Contact", { email_id: "jane@example.com" }, "name");
const contact_exists = Boolean(r.message?.name);

Restrict Link Field Options with set_query

frm.set_query controls the filters (or the server-side search method) behind a Link field's dropdown. Call it once in setup or onload; the function runs every time the dropdown opens, so it can read current form values.

Filter by Another Field

frappe.ui.form.on("Sales Order", {
    setup(frm) {
        frm.set_query("project", () => ({
            filters: { customer: frm.doc.customer, status: "Open" },
        }));
    },
});

Contacts Linked to the Selected Customer

Contacts and addresses link to customers through a Dynamic Link child table, which plain filters can't reach. Frappe ships search methods for exactly this (address_query in frappe.contacts.doctype.address.address works the same way for addresses):

frappe.ui.form.on("<DOCTYPE>", {
    setup(frm) {
        frm.set_query("contact_person", () => ({
            query: "frappe.contacts.doctype.contact.contact.contact_query",
            filters: { link_doctype: "Customer", link_name: frm.doc.customer },
        }));
    },
});

Link Fields Inside a Child Table

Pass the child table's fieldname as the second argument. This example, taken from Frappe's own Contact form, limits the Link Document Type column of the links table to doctypes that have a contact_html field:

frm.set_query("link_doctype", "links", () => ({
    query: "frappe.contacts.address_and_contact.filter_dynamic_link_doctypes",
    filters: { fieldtype: "HTML", fieldname: "contact_html" },
}));

The function also receives (doc, cdt, cdn), so you can filter on values from the current child row with locals[cdt][cdn].