Skip to main content

get_query in Frappe v15

What is get_query?

get_query is a dynamic query filter mechanism in Frappe that allows developers to control the available records in a Link field. It returns a query definition based on runtime conditions, user context, or field values.

It is commonly used to:

  • Restrict Link field options
  • Apply domain-specific filters
  • Create context-based selections
  • Implement user-controlled filtering logic
  • Prevent invalid cross-DocType links

How does get_query work in Frappe?

get_query can be declared:

  • Client-side (JavaScript) inside the Form script, or
  • Server-side (Python) using a whitelisted method

Both return a query filter configuration understood by the Frappe Desk UI.

When to use get_query?

Use get_query when:

  • You want to filter a Link field dynamically
  • The filter depends on document field values
  • Filtering logic requires Python logic
  • You need complex conditions that cannot be handled using static filters
  • You want to restrict selectable values based on user permissions

Declaring get_query on Link Field (Client-Side)

You can assign get_query to a Link field in the Form Script.

Example: Filter Item by Item Group

frappe.ui.form.on('Sales Order', {
refresh: function(frm) {
frm.set_query('item_code', 'items', function(doc, cdt, cdn) {
let row = locals[cdt][cdn];
return {
filters: {
'item_group': row.item_group
}
};
});
}
});

What is happening?

  • frm.set_query defines a query filter on the field item_code
  • The query uses the child table row context
  • Only items belonging to the same item_group are shown

Using get_query with filters Object

The simplest form returns filters:

cur_frm.set_query("customer", function() {
return {
filters: {
territory: "India"
}
};
});

This will fetch only customers where the territory is “India”.

Using Python get_query for Complex Logic

You can define get_query server-side when filtering logic requires:

  • Role checks
  • Permission logic
  • Database joins
  • Multi-field conditions

Step 1: Define get_query in DocType JSON

In the DocType JSON configuration, for the Link field:
"get_query": "erpnext.selling.doctype.sales_order.sales_order.get_customer_query"

Step 2: Implement Python Method

import frappe
@frappe.whitelist()
def get_customer_query(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""
SELECT name, customer_name
FROM `tabCustomer`
WHERE territory = 'India'
AND name like %(txt)s
""", {"txt": "%" + txt + "%"})

This method:

  • Is whitelisted
  • Accepts standard parameters
  • Uses parameterized SQL
  • Returns a valid result used by the form

Dynamic Filters Based on Parent Document

Frappe passes filters from the form.

Example: restrict contacts by selected customer:

JavaScript

frm.set_query("contact_person", function(doc) {
return {
query: "frappe.contacts.doctype.contact.contact.get_contacts_for_customer",
filters: {
customer: doc.customer
}
}
});

Python

@frappe.whitelist()
def get_contacts_for_customer(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""
SELECT name, first_name
FROM `tabContact`
WHERE customer = %(customer)s
AND name like %(txt)s
""", {"customer": filters.get("customer"), "txt": "%" + txt + "%"})

Using get_query with Custom controller

You can override get_query at DocType class level:

class SalesOrder(Document):
def get_customer_query(self):
return {
"filters": {
"customer_group": "Retail"
}
}

Then reference:

"get_query": "SalesOrder.get_customer_query"

Using get_query with Query Builder (Frappe v15)

You can use Query Builder instead of SQL.

from frappe.query_builder import DocType
import frappe
@frappe.whitelist()
def get_customer_query(doctype, txt, searchfield, start, page_len, filters):
Customer = DocType("Customer")
query = (
frappe.qb.from_(Customer)
.select(Customer.name, Customer.customer_name)
.where(Customer.territory == "India")
.where(Customer.name.like(f"%{txt}%"))
)
return query.run()

Using get_query inside Child Table Fields

Example: Limit items based on the main document currency:

frm.fields_dict.items.grid.get_field("item_code").get_query = function(doc, cdt, cdn){
return {
filters: {
"default_currency": doc.currency
}
}
}

Passing Context from Client to Server

Frappe automatically passes filters, and you can provide custom fields:
frm.set_query('item_code', function(doc) {
return {
query: 'path.to.method',
filters: {
price_list: doc.price_list,
customer: doc.customer
}
}
});

Python will receive it in filters.

Best Practices for get_query (Frappe v15)

  • Prefer JavaScript filters when the logic is simple
  • Use Python for SQL or advanced logic
  • Always use parameterized SQL
  • Never concatenate SQL strings manually
  • Avoid slow joins on Link filters
  • Return a valid tuple format for UI rendering
  • Keep the method whitelisted if used from client script
  • Optimize using Query Builder when possible
  • Use ignore_permissions=True only in controlled contexts

Troubleshooting get_query

Not Returning Expected Values

  • Check the returned format: should be list or tuple
  • Ensure field names are valid
  • Verify DocType permissions
  • Check if search text is passed correctly
  • Test SQL query separately

Custom get_query Not Triggering

  • Confirm field uses “Link” type
  • Ensure get_query path is resolvable
  • Check console/network tab for errors

Integration Patterns

get_query is most effective when used with:

  • REST API response rendering
  • Query Builder for safety
  • Dynamic form logic
  • Server-side whitelisted methods
  • ERPNext business rules (Selling, Buying)

Typical business use cases:

  • Filter Items by Warehouse
  • Filter Customers by Territory
  • Filter Employees by Department
  • Filter Accounts by Company
  • Filter Batches by Item Code
  • Filter Properties by Project

Target Audience Tags

  • ERPNext Implementers
  • Frappe App Developers
  • Python Backend Developers
  • UI/Desk Customization Engineers
  • Enterprise Consultants

Technical Prerequisites

  • Basic JavaScript
  • Basic Python
  • Understanding DocType fields
  • Familiarity with Link fields
  • Knowledge of Frappe client-server interaction

Cross-References

  • Query Builder API
  • Database API
  • Document API
  • Full Text Search
  • REST API
  • Client Scripting Guide
  • Python Server Scripts
Click to rate this post!
[Total: 0 Average: 0]

© 2025 | goERPNext | All rights reserved