[optimize] item queries (#13480)
diff --git a/erpnext/controllers/queries.py b/erpnext/controllers/queries.py
index 139586f..15a93bd 100644
--- a/erpnext/controllers/queries.py
+++ b/erpnext/controllers/queries.py
@@ -152,6 +152,11 @@
def item_query(doctype, txt, searchfield, start, page_len, filters, as_dict=False):
conditions = []
+ description_cond = ''
+ if frappe.db.count('Item', cache=True) < 50000:
+ # scan description only if items are less than 50000
+ description_cond = 'or tabItem.description LIKE %(txt)s'
+
return frappe.db.sql("""select tabItem.name, tabItem.item_group,
if(length(tabItem.item_name) > 40,
concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name,
@@ -166,7 +171,7 @@
or tabItem.item_group LIKE %(txt)s
or tabItem.item_name LIKE %(txt)s
or tabItem.barcode LIKE %(txt)s
- or tabItem.description LIKE %(txt)s)
+ {description_cond})
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
@@ -176,7 +181,8 @@
limit %(start)s, %(page_len)s """.format(
key=searchfield,
fcond=get_filters_cond(doctype, filters, conditions).replace('%', '%%'),
- mcond=get_match_cond(doctype).replace('%', '%%')),
+ mcond=get_match_cond(doctype).replace('%', '%%'),
+ description_cond = description_cond),
{
"today": nowdate(),
"txt": "%%%s%%" % txt,
diff --git a/erpnext/stock/report/stock_ledger/stock_ledger.py b/erpnext/stock/report/stock_ledger/stock_ledger.py
index 305702e..6b88c52 100644
--- a/erpnext/stock/report/stock_ledger/stock_ledger.py
+++ b/erpnext/stock/report/stock_ledger/stock_ledger.py
@@ -7,8 +7,9 @@
def execute(filters=None):
columns = get_columns()
- sl_entries = get_stock_ledger_entries(filters)
- item_details = get_item_details(filters)
+ item_conditions = get_item_conditions(filters)
+ item_details = get_item_details(filters, item_conditions)
+ sl_entries = get_stock_ledger_entries(filters, item_conditions, item_details)
opening_row = get_opening_balance(filters, columns)
data = []
@@ -52,7 +53,12 @@
return columns
-def get_stock_ledger_entries(filters):
+def get_stock_ledger_entries(filters, item_conditions, item_details):
+ item_conditions_sql = ''
+ if item_conditions:
+ items = ['"' + frappe.db.escape(i) + '"' for i in item_details.keys()]
+ if items:
+ item_conditions_sql = 'and sle.item_code in ({})'.format(', '.join(items))
return frappe.db.sql("""select concat_ws(" ", posting_date, posting_time) as date,
item_code, warehouse, actual_qty, qty_after_transaction, incoming_rate, valuation_rate,
stock_value, voucher_type, voucher_no, batch_no, serial_no, company, project
@@ -60,14 +66,18 @@
where company = %(company)s and
posting_date between %(from_date)s and %(to_date)s
{sle_conditions}
+ {item_conditions_sql}
order by posting_date asc, posting_time asc, name asc"""\
- .format(sle_conditions=get_sle_conditions(filters)), filters, as_dict=1)
+ .format(
+ sle_conditions=get_sle_conditions(filters),
+ item_conditions_sql = item_conditions_sql
+ ), filters, as_dict=1, debug=1)
-def get_item_details(filters):
+def get_item_details(filters, item_conditions):
item_details = {}
for item in frappe.db.sql("""select name, item_name, description, item_group,
brand, stock_uom from `tabItem` item {item_conditions}"""\
- .format(item_conditions=get_item_conditions(filters)), filters, as_dict=1):
+ .format(item_conditions=item_conditions), filters, as_dict=1):
item_details.setdefault(item.name, item)
return item_details
@@ -85,10 +95,6 @@
def get_sle_conditions(filters):
conditions = []
- item_conditions=get_item_conditions(filters)
- if item_conditions:
- conditions.append("""sle.item_code in (select item.name from tabItem item
- {item_conditions})""".format(item_conditions=item_conditions))
if filters.get("warehouse"):
warehouse_condition = get_warehouse_condition(filters.get("warehouse"))
if warehouse_condition: