Using IF or CASE with query builder in safe_exec context

1

Hi all,

Has anyone figured how to use IF or CASE functions in the frappe query builder? I see it’s possible to import the operation direction from pypika, but I’d like to be able to use this feature in a script report where imports aren’t possible. Any suggestions would be very welcome!

(Specifically, I’m trying to pivot a table. This could be done with subqueries, but pypika seems to have a significant bug with subqueries and where clauses.)

2

Any thoughts or suggestions here?

Alternately, has anyone had any success customizing the safe_exec environment via an app?

3

instead of
from frappe.query_builder import Case

in normal py file

use
Case = frappe.qb.terms.Case

in server script

4

Wonderful, thank you so much @szufisher. This is exactly the answer I was hoping for. I’ll try it out as soon as I get back to the office.