Need to add one more filter in set query

Frappe Framework Customization
1

Hi everyone,I am trying to filter the ‘stp_no’ based on two conditions i am done with one condition and i need to add one more condition with this filter can anyone help me?

In js i wrote set query like,

setup: function(frm) {
	me.frm.set_query("stp_no",function(doc) {
		return {
			query: "for.qc.doctype.quality_test.quality_test.stp_query",
			filters: {
				'item': doc.item_code,
				'inspection type': doc.inspection_type
			}
		}
	});
}

In py i worte like,

@frappe.whitelist()
def stp_query(doctype, txt, searchfield, start, page_len, filters):
	cond = ""
	if filters.get('item_code'):
		cond =  ' item = "' + filters['item_code'] + '"'
		return frappe.db.sql("""select name  from `tabSTP` 
			where  {cond}""".format(cond=cond))

i need to add second condition ‘inspection_type’ along with ‘item_code’ based on these two condition i want to filter the ‘name’ of the ‘STP’ in ‘stp_no’ field. TIA

2

try this,

@frappe.whitelist()
def stp_query(doctype, txt, searchfield, start, page_len, filters):
	cond = "1=1"
	if filters.get('item'):
		cond += "and item = '{}'".format(filters.get('item'))
	if filters.get('inspection_type'):
		cond += "and inspection_type = '{}'".format(filters.get('inspection_type'))
	return frappe.db.sql("""select name  from `tabSTP` 
			where  {cond}""".format(cond=cond))
3

hi @Sangram, i tried this code but now it return all the item name which has different inspection type ‘STP’ name… it doesn’t look into the condition…

4

debug the query you will get the reason.

e.g return frappe.db.sql("""select name  from `tabSTP` 
			where  {cond}""".format(cond=cond), debug=True)
5

i am getting the following sql error @Sangram,

— query explain —
error in query explain
select name from tabSTP
where 1=1and item = 'SF-002’and inspection_type = ‘In Process’
Traceback (most recent call last):
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/app.py”, line 62, in application
response = frappe.handler.handle()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/handler.py”, line 22, in handle
data = execute_cmd(cmd)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/handler.py”, line 53, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/init.py”, line 939, in call
return fn(*args, **newargs)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/desk/search.py”, line 51, in search_link
search_widget(doctype, txt, query, searchfield=searchfield, page_length=page_length, filters=filters)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/desk/search.py”, line 75, in search_widget
searchfield, start, page_length, filters, as_dict=as_dict)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/init.py”, line 939, in call
return fn(*args, **newargs)
File “/home/ubuntu/frappe-bench/apps/formulation/formulation/qc/doctype/quality_test/quality_test.py”, line 524, in stp_query
where {cond}“”“.format(cond=cond), debug=True)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/database.py”, line 176, in sql
self._cursor.execute(query)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 170, in execute
result = self._query(query)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 328, in _query
conn.query(q)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 516, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 727, in _read_query_result
result.read()
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 1066, in read
first_packet = self.connection._read_packet()
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 683, in _read_packet
packet.check_error()
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/protocol.py”, line 220, in check_error
err.raise_mysql_exception(self._data)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
raise errorclass(errno, errval)
ProgrammingError: (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'item = ‘SF-002’and inspection_type = ‘In Process’’ at line 2”)

6

add space before and

@frappe.whitelist()
def stp_query(doctype, txt, searchfield, start, page_len, filters):
	cond = "1=1"
	if filters.get('item'):
		cond += " and item = '{}'".format(filters.get('item'))
	if filters.get('inspection_type'):
		cond += " and inspection_type = '{}'".format(filters.get('inspection_type'))
	return frappe.db.sql("""select name  from `tabSTP` 
			where  {cond}""".format(cond=cond))
1 Like
7

Great @Sangram It works :+1: