Need to add one more filter in set query

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

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))

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…

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)

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”)

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

Great @Sangram It works :+1: