Repository: frappe/frappe
Pull request: feat!: Added support for IS in frappe qb
Fixes #16869
Using https://github.com/dickerschutz/frappe/commit/53148c3f90487dde8537733d16961947d5363885

<!-- no-docs -->

Problem statement:
bug: "is" query operator no longer working
Just checked out the latest version of the develop branch. Every doctype throws an error if i try to filter by "is" "set"/"not set"

See traceback

### App Versions
```
{
	"dickerschutz": "0.0.1",
	"erpnext": "14.0.0-dev",
	"frappe": "14.0.0-dev"
}
```
### Route
```
List/Lead/List
```
### Trackeback
```
Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 67, in application
    response = frappe.api.handle()
  File "apps/frappe/frappe/api.py", line 54, in handle
    return frappe.handler.handle()
  File "apps/frappe/frappe/handler.py", line 38, in handle
    data = execute_cmd(cmd)
  File "apps/frappe/frappe/handler.py", line 76, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "apps/frappe/frappe/__init__.py", line 1503, in call
    return fn(*args, **newargs)
  File "apps/frappe/frappe/__init__.py", line 762, in wrapper_fn
    retval = fn(*args, **get_newargs(fn, kwargs))
  File "apps/frappe/frappe/desk/reportview.py", line 56, in get_count
    data = frappe.db.count(args["doctype"], args["filters"], distinct=distinct)
  File "apps/frappe/frappe/database/database.py", line 1028, in count
    query = self.query.get_sql(table=dt, filters=filters, fields=Count("*"), distinct=distinct)
  File "apps/frappe/frappe/database/query.py", line 317, in get_sql
    criterion = self.build_conditions(table, filters, **kwargs)
  File "apps/frappe/frappe/database/query.py", line 301, in build_conditions
    criterion = self.misc_query(table, filters, **kwargs)
  File "apps/frappe/frappe/database/query.py", line 231, in misc_query
    _operator = OPERATOR_MAP[f[-2]]
KeyError: 'is'
```

### Request Data
```
{
	"type": "POST",
	"args": {
		"doctype": "Lead",
		"filters": "[[\"Lead\",\"name\",\"is\",\"set\"],[\"Lead\",\"status\",\"=\",\"Open\"]]",
		"fields": "[]",
		"distinct": false
	},
	"headers": {},
	"error_handlers": {},
	"url": "/api/method/frappe.desk.reportview.get_count"
}
```
### Response Data
```
{
	"exception": "KeyError: 'is'"
}
```


Full traceback

```
15:51:01 web.1            | Traceback (most recent call last):
15:51:01 web.1            |   File "apps/frappe/frappe/app.py", line 67, in application
15:51:01 web.1            |     response = frappe.api.handle()
15:51:01 web.1            |   File "apps/frappe/frappe/api.py", line 54, in handle
15:51:01 web.1            |     return frappe.handler.handle()
15:51:01 web.1            |   File "apps/frappe/frappe/handler.py", line 38, in handle
15:51:01 web.1            |     data = execute_cmd(cmd)
15:51:01 web.1            |   File "apps/frappe/frappe/handler.py", line 76, in execute_cmd
15:51:01 web.1            |     return frappe.call(method, **frappe.form_dict)
15:51:01 web.1            |   File "apps/frappe/frappe/__init__.py", line 1503, in call
15:51:01 web.1            |     return fn(*args, **newargs)
15:51:01 web.1            |   File "apps/frappe/frappe/__init__.py", line 762, in wrapper_fn
15:51:01 web.1            |     retval = fn(*args, **get_newargs(fn, kwargs))
15:51:01 web.1            |   File "apps/frappe/frappe/desk/reportview.py", line 56, in get_count
15:51:01 web.1            |     data = frappe.db.count(args["doctype"], args["filters"], distinct=distinct)
15:51:01 web.1            |   File "apps/frappe/frappe/database/database.py", line 1028, in count
15:51:01 web.1            |     query = self.query.get_sql(table=dt, filters=filters, fields=Count("*"), distinct=distinct)
15:51:01 web.1            |   File "apps/frappe/frappe/database/query.py", line 317, in get_sql
15:51:01 web.1            |     criterion = self.build_conditions(table, filters, **kwargs)
15:51:01 web.1            |   File "apps/frappe/frappe/database/query.py", line 301, in build_conditions
15:51:01 web.1            |     criterion = self.misc_query(table, filters, **kwargs)
15:51:01 web.1            |   File "apps/frappe/frappe/database/query.py", line 231, in misc_query
15:51:01 web.1            |     _operator = OPERATOR_MAP[f[-2]]
15:51:01 web.1            | KeyError: 'is'
```

Hints from issue discussion:
Fix can be found here https://github.com/dickerschutz/frappe/commit/53148c3f90487dde8537733d16961947d5363885

Can't make a pull request. I need to order my commits per feature in a new fork in order to make descent pull requests here.

Commit message: test: updated tests

Patch to review:
diff --git a/frappe/database/query.py b/frappe/database/query.py
index f7393bfa54f3..701286c9894c 100644
--- a/frappe/database/query.py
+++ b/frappe/database/query.py
@@ -85,6 +85,11 @@ def func_between(key: Field, value: Union[List, Tuple]) -> frappe.qb:
 	return key[slice(*value)]
 
 
+def func_is(key, value):
+	"Wrapper for IS"
+	return key.isnotnull() if value.lower() == "set" else key.isnull()
+
+
 def make_function(key: Any, value: Union[int, str]):
 	"""returns fucntion query
 
@@ -135,6 +140,7 @@ def change_orderby(order: str):
 	"not like": not_like,
 	"regex": func_regex,
 	"between": func_between,
+	"is": func_is,
 }
 
 
diff --git a/frappe/tests/test_db.py b/frappe/tests/test_db.py
index 4d105ef28ed8..8ab70445ebea 100644
--- a/frappe/tests/test_db.py
+++ b/frappe/tests/test_db.py
@@ -841,3 +841,12 @@ def test_sequence_table_creation(self):
 			)
 
 		dt.delete(ignore_permissions=True)
+
+	def test_is(self):
+		user = frappe.qb.DocType("User")
+		self.assertIn(
+			"is not null", frappe.db.get_values(user, filters={user.name: ("is", "set")}, run=False).lower()
+		)
+		self.assertIn(
+			"is null", frappe.db.get_values(user, filters={user.name: ("is", "not set")}, run=False).lower()
+		)


Code review comments:
diff_hunk: @@ -85,6 +85,11 @@ def func_between(key: Field, value: Union[List, Tuple]) -> frappe.qb:
 	return key[slice(*value)]
 
 
+def func_is(key, value):
+	"Wrapper for IS"
+	return key.isnotnull() if value.lower() == "set" else key.isnull()
path: frappe/database/query.py
text: @user1:
Started getting attribute error without this 🤔 

```suggestion
	return Field(key).isnotnull() if value.lower() == "set" else Field(key).isnull()
```

#
Ref:

```python
In [6]: frappe.db.get_values("DocType", {"name": ("is", "set")})
-------------------------------------------------------------------
AttributeError                    Traceback (most recent call last)
~/Desktop/dev/killt/apps/frappe/frappe/commands/utils.py in <module>
----> 1 frappe.db.get_values("DocType", {"name": ("is", "set")})

~/Desktop/dev/killt/apps/frappe/frappe/database/database.py in get_values(self, doctype, filters, fieldname, ignore, as_dict, debug, order_by, update, cache, for_update, run, pluck, distinct, limit)
    509                                         if order_by:
    510                                                 order_by = "modified" if order_by == "KEEP_DEFAULT_ORDERING" else order_by
--> 511 					out = self._get_values_from_table(
    512                                                 fields=fields,
    513                                                 filters=filters,

~/Desktop/dev/killt/apps/frappe/frappe/database/database.py in _get_values_from_table(self, fields, filters, doctype, as_dict, debug, order_by, update, for_update, run, pluck, distinct, limit)
    718                                         field_objects.append(field)
    719
--> 720 		query = self.query.get_sql(
    721                         table=doctype,
    722                         filters=filters,

~/Desktop/dev/killt/apps/frappe/frappe/database/query.py in get_sql(self, table, fields, filters, **kwargs)
    302                 **kwargs,
    303 	):
--> 304                 criterion = self.build_conditions(table, filters, **kwargs)
    305                 if isinstance(fields, (list, tuple)):
    306                         query = criterion.select(*kwargs.get("field_objects", fields))

~/Desktop/dev/killt/apps/frappe/frappe/database/query.py in build_conditions(self, table, filters, **kwargs)
    291
    292                 else:
--> 293                         criterion = self.dict_query(filters=filters, table=table, **kwargs)
    294
    295                 return criterion

~/Desktop/dev/killt/apps/frappe/frappe/database/query.py in dict_query(self, table, filters, **kwargs)
    255                                 if isinstance(value[1], (list, tuple)) or value[0] in list(OPERATOR_MAP.keys())[-4:]:
    256                                         _operator = OPERATOR_MAP[value[0]]
--> 257                                         conditions = conditions.where(_operator(key, value[1]))
    258                                 else:
    259                                         _operator = OPERATOR_MAP[value[0]]

~/Desktop/dev/killt/apps/frappe/frappe/database/query.py in func_is(key, value)
     10 def func_is(key: Union[str, Field], value: str):
     11         "Wrapper for IS"
---> 12         return key.isnotnull() if value.lower() == "set" else key.isnull()
     13
     14 def like(key: str, value: str) -> frappe.qb:

AttributeError: 'str' object has no attribute 'isnotnull'
```


@author:
need to instantiate field objects for str inputs, took field objects only into account fixed [3296898](https://github.com/frappe/frappe/pull/16877/commits/3296898b4864d977cfb9b4bbdb5a68ca64cbb4c5)