Using frappe.db.count() with "IS NULL" filter

Frappe Framework Customization
1

Gentlemen,
I need to execute this Query:
SELECT COUNT(*) from tabSales Invoice WHERE datevkonto IS NULL;

Using frappe.db.count() I tried these approaches which weren’t successful in creating the desired query:
frappe.db.count(“Sales Invoice”, filters={“datevkonto” : None}, debug=True)
frappe.db.count(“Sales Invoice”, filters={“datevkonto” : “”}, debug=True)
frappe.db.count(“Sales Invoice”, filters={“datevkonto” : “is null”}, debug=True)
frappe.db.count(“Sales Invoice”, filters={“datevkonto” : [“is”, “null”]}, debug=True)

select count(*)
                                from `tabSales Invoice` where `datevkonto` = NULL
Execution time: 0.0 sec
select count(*)
                                from `tabSales Invoice` where `datevkonto` = ''
Execution time: 0.0 sec
select count(*)
                                from `tabSales Invoice` where `datevkonto` = 'is null'
Execution time: 0.0 sec
select count(*)
                                from `tabSales Invoice` where `datevkonto` = 'null'
Execution time: 0.0 sec

Surely I could use frappe.db.sql() to execute my query, but I would like to know if there is a way to use frappe.db.count() for this.

By the way, I’m using frappe 13.22.1.

2

@thkr_ituc when I filter something from the filter on the UI I use a space to fetch data with empty field . so try a space .

frappe.db.count("Sales Invoice", filters={"datevkonto" :" "}
3

Thank you for your contribution, but this does not work. The column “datevkonto” contains NULL-Values which are not selected with your proposal:

print(frappe.db.count("Sales Invoice", filters={"datevkonto" :" "}, debug=True))

Result:

select count(*)
                                from `tabSales Invoice` where `datevkonto` = ' '
4

@thkr_ituc oh , I guess you should use sql then