refactor: better ordering of query result
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index d88782b..e234eec 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -8,9 +8,10 @@
import frappe
from frappe import qb, scrub
from frappe.desk.reportview import get_filters_cond, get_match_cond
-from frappe.query_builder import Criterion
-from frappe.query_builder.functions import Concat, Sum
+from frappe.query_builder import Criterion, CustomFunction
+from frappe.query_builder.functions import Concat, Locate, Sum
from frappe.utils import nowdate, today, unique
+from pypika import Order
import erpnext
from erpnext.stock.get_item_details import _get_item_tax_template
@@ -348,6 +349,8 @@
proj = qb.DocType("Project")
qb_filter_and_conditions = []
qb_filter_or_conditions = []
+ ifelse = CustomFunction("IF", ["condition", "then", "else"])
+
if filters and filters.get("customer"):
qb_filter_and_conditions.append(proj.customer == filters.get("customer"))
@@ -359,17 +362,29 @@
for x in fields:
q = q.select(proj[x])
- # ignore 'customer' and 'status' on searchfields as they must be exactly matched
+ # don't consider 'customer' and 'status' fields for pattern search, as they must be exactly matched
searchfields = [
x for x in frappe.get_meta(doctype).get_search_fields() if x not in ["customer", "status"]
]
+
+ # pattern search
if txt:
for x in searchfields:
qb_filter_or_conditions.append(proj[x].like(f"%{txt}%"))
q = q.where(Criterion.all(qb_filter_and_conditions)).where(Criterion.any(qb_filter_or_conditions))
+
+ # ordering
+ if txt:
+ # project_name containing search string 'txt' will be given higher precedence
+ q = q.orderby(ifelse(Locate(txt, proj.project_name) > 0, Locate(txt, proj.project_name), 99999))
+ q = q.orderby(proj.idx, order=Order.desc).orderby(proj.name)
+
if page_len:
q = q.limit(page_len)
+
+ if start:
+ q = q.offset(start)
return q.run()