I just tried my hand at query building, so let me share an example of the same query in three different styles:
1. frappe.get_list
company = "My Company"
frappe.get_list("Account",
fields=[
"account_number as 'Konto'",
"account_name as 'Kontenbeschriftung'",
"'de-DE' as 'Sprach-ID'"
],
filters={
"company": company,
"is_group": 0,
"account_number": ("is", "set")
}
)
2. frappe.db.sql
company = "My Company"
frappe.db.sql("""
SELECT
account_number as 'Konto',
LEFT(account_name, 40) as 'Kontenbeschriftung',
'de-DE' as 'Sprach-ID'
FROM `tabAccount`
WHERE company = %(company)s
AND is_group = 0
AND account_number != ''
""", {"company": company}, as_dict=1)
3. frappe.qb
from pypika.functions import LiteralValue
company = "My Company"
account = frappe.qb.DocType("Account")
frappe.qb.from_(account).select(
account.account_number.as_("Konto"),
account.account_name.as_("Kontenbeschriftung"),
LiteralValue("'de-DE'").as_("Sprach-ID")
).where(
(account.company == company)
& (account.is_group == 0)
& (account.account_number != '')
).run(as_dict=True)
In this simple example I personally prefer frappe.get_list. What do you think?
Let me know if I made and error or if the queries are not equivalent.
As you’ve framed it here, I agree. I find get_list cleaner and more intuitive than the frappe.qb command. But, I never really saw the query builder as a substitute for get_list. Under many normal circumstances, get_list does the job, and I intend to keep using get_list for all that stuff.
This looks exciting to me primarily for two use cases:
Procedurally composed queries, especially for building more interactive reports
Complex queries, especially those featuring multiple table joins.
For those, get_list is limited. Likewise, for me, the comparison is not between (3) and (1) but between (3) and (2). I don’t see the query builder changing the way we do normal document transactions necessarily, but I think it has huge potential for things like data analytics.
customer = frappe.qb.DocType('Customer')
and not: customer = frappe.get_doc('DocType', 'Customer') ?
I’d be interested to learn if there are plans to change the reporting. Script reports could do with improvements, especially defining the filters and columns, which from day 1 I invented some utilities to avoid copying the column info which is already in the database metadata.
I hope frappe.db.sql will remain for those who don’t want to learn another technology syntax.
frappe.get_doc('DocType', 'Customer') returns a doctype object whereas frappe.qb.DocType method returns the name of the SQL table the doctype uses as a string. The two methods have very different roles. The query builder is still very low level in most regards, hewing closer to the underlying SQL than the ORM abstraction.
Agreed. The query builder uses frappe.db.sql on the backend, so it would be a strong choice to remove it.
Yes I figured that, but it would make more sense if they hid the technicalities in the qb code and made it as familiar to current users as possible. Potentially having two completely different python customer objects isn’t good and all the information is available from the document.
customer_doc.qb.select... could be an interesting architecture for example.
Hmm…very interesting thought. I’m not sure, though. To my mind, at least half of the use case for frappe.qb involves table joins, and mixing object notation with parameters for tables just doesn’t feel that natural to me. Perhaps I’m too wedded to SQL, though. I might be getting old!
In the end, I think it’s designed this way because query builder is a relatively thin wrapper on PyPika. I’m certainly open to the idea that there are better ways out there, but mostly I’m just glad that Frappe didn’t try to reinvent the wheel on this one.
Sorry, my sentence there was not at all clear! By “strong choice”, I don’t mean “good choice” but rather “choice with significant structural implications”. It would not be the path of least resistance to drop frappe.db.sql, but that definitely doesn’t mean it couldn’t happen.
