[Announcement] Introducing Frappe Query Builder

Community
, ,
22

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_all

company = "My Company"

frappe.get_all("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_all. What do you think?
Let me know if I made and error or if the queries are not equivalent.

Edit: I’ve changed get_list to get_all because it’s closer to the SQL queries (i.e. no permissions applied).

4 Likes
23

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:

  1. Procedurally composed queries, especially for building more interactive reports
  2. 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.

1 Like
24

I agree. I started by converting (2) into (3), then noticed that the query is simple enough to use get_list (1).

25

Bit late to the party but a few comments:

  1. Why would you use:

customer = frappe.qb.DocType('Customer')
and not:
customer = frappe.get_doc('DocType', 'Customer') ?

  1. 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.

  2. I hope frappe.db.sql will remain for those who don’t want to learn another technology syntax.

1 Like
26

Good questions!

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.

27

Sorry should have been clearer:

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.

Am assuming you meant ‘would not’ :slight_smile:

1 Like
28

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! :slight_smile:

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.