frappe-syntax-query-builder

Name: frappe-syntax-query-builder
Author: Impertio-Studio

151stars49forks

How to install frappe-syntax-query-builder?

impertio-studio/frappe_claude_skill_package/frappe-syntax-query-builder

$npx -y skills add Impertio-Studio/Frappe_Claude_Skill_Package --skill frappe-syntax-query-builder

Installs into the current project.

Summary

Use when building database queries with frappe.qb in Frappe v14-v16. Covers the PyPika-based query builder: SELECT, INSERT, UPDATE, DELETE, joins, aggregation, subqueries, cross-DB compatibility (MariaDB/PostgreSQL), and migration from raw SQL. Prevents SQL injection and DB-speci

Files · 1

View on GitHub

SKILL.md

1	# Frappe Query Builder (frappe.qb)
2
3	## Quick Reference
4
5	```python
6	from frappe.query_builder import DocType, Field
7	from frappe.query_builder.functions import Count, Sum, IfNull
8	from frappe.query_builder.custom import ConstantColumn, GROUP_CONCAT
9	from frappe.query_builder.terms import SubQuery
10	from frappe.query_builder.utils import ImportMapper, db_type_is
11	from pypika.terms import Case, ValueWrapper
12	from pypika import CustomFunction, Order
13	```
14
15	\| Action \| Pattern \|
16	\|--------\|---------\|
17	\| SELECT \| `frappe.qb.from_("DocType").select("field1", "field2")` \|
18	\| WHERE \| `.where(Field("status") == "Open")` \|
19	\| ORDER \| `.orderby("creation", order=Order.desc)` \|
20	\| LIMIT \| `.limit(10).offset(0)` \|
21	\| JOIN \| `.left_join(dt2).on(dt2.name == dt1.parent)` \|
22	\| COUNT \| `.select(Count("*"))` \|
23	\| INSERT \| `frappe.qb.into("DocType").columns("f1", "f2").insert("v1", "v2")` \|
24	\| UPDATE \| `frappe.qb.update("DocType").set("field", "value").where(...)` \|
25	\| DELETE \| `frappe.qb.from_("DocType").delete().where(...)` \|
26	\| RUN \| `.run()` (tuples) / `.run(as_dict=True)` (dicts) \|
27
28	---
29
30	## Decision Tree
31
32	```
33	Need to query the database?
34	│
35	├─ Simple get/list → frappe.db.get_value(), frappe.get_all()
36	│ (See frappe-core-database skill)
37	│
38	├─ Complex query with joins/aggregates/subqueries → frappe.qb ✓
39	│
40	├─ Very complex SQL not expressible in qb → frappe.db.sql()
41	│ (ALWAYS use parameterized values: frappe.db.sql(query, values))
42	│
43	└─ Need cross-DB compatibility → frappe.qb + ImportMapper ✓
44
45	Using frappe.qb?
46	│
47	├─ Table reference → DocType("Sales Order") — NEVER use Table()
48	├─ Field reference → dt.field_name or Field("field_name")
49	├─ Execute → .run() for tuples, .run(as_dict=True) for dicts
50	├─ DB-specific function → ImportMapper({db_type_is.MARIADB: X, db_type_is.POSTGRES: Y})
51	└─ Get SQL string → query.get_sql() — NEVER pass to frappe.db.sql()
52	```
53
54	---
55
56	## Core Patterns
57
58	### SELECT with DocType
59
60	```python
61	# ALWAYS use DocType() for table references — adds "tab" prefix
62	so = frappe.qb.DocType("Sales Order")
63	soi = frappe.qb.DocType("Sales Order Item")
64
65	orders = (
66	frappe.qb.from_(so)
67	.select(so.name, so.customer, so.grand_total)
68	.where(so.status == "To Deliver and Bill")
69	.where(so.docstatus == 1)
70	.orderby(so.creation, order=Order.desc)
71	.limit(20)
72	.run(as_dict=True)
73	)
74	```
75
76	### JOIN
77
78	```python
79	result = (
80	frappe.qb.from_(so)
81	.left_join(soi).on(soi.parent == so.name)
82	.select(so.name, so.customer, soi.item_code, soi.qty)
83	.where(so.docstatus == 1)
84	.where(soi.item_code.like("ITEM-%"))
85	.run(as_dict=True)
86	)
87	```
88
89	### Aggregation
90
91	```python
92	from frappe.query_builder.functions import Count, Sum
93
94	gl = frappe.qb.DocType("GL Entry")
95	result = (
96	frappe.qb.from_(gl)
97	.select(gl.account, Sum(gl.debit).as_("total_debit"), Count("*").as_("entries"))
98	.where(gl.docstatus == 1)
99	.groupby(gl.account)
100	.run(as_dict=True)
101	)
102
103	# Shortcut aggregation methods
104	total = frappe.qb.sum("GL Entry", "debit", filters={"account": "Sales"})
105	max_qty = frappe.qb.max("Stock Ledger Entry", "actual_qty", filters={"item_code": "ITEM-001"})
106	```
107
108	### INSERT / UPDATE / DELETE
109
110	```python
111	# INSERT
112	frappe.qb.into("Activity Log").columns("user", "action").insert("admin", "login").run()
113
114	# UPDATE
115	customer = frappe.qb.DocType("Customer")
116	(frappe.qb.update(customer)
117	.set(customer.status, "Active")
118	.where(customer.name == "CUST-001")
119	.run())
120
121	# DELETE
122	frappe.qb.from_("Error Log").delete().where(Field("creation") < "2024-01-01").run()
123	```
124
125	---
126
127	## Filtering
128
129	```python
130	dt = frappe.qb.DocType("Sales Order")
131
132	# Equality
133	.where(dt.status == "Open")
134
135	# OR (pipe operator)
136	.where((dt.status == "Open") \| (dt.status == "Draft"))
137
138	# AND (chain .where() calls)
139	.where(dt.status == "Open")
140	.where(dt.docstatus == 1)
141
142	# LIKE
143	.where(dt.customer.like("CUST-%"))
144
145	# IN
146	.where(dt.status.isin(["Open", "Draft"]))
147
148	# BETWEEN (bracket syntax)
149	.where(dt.creation[start_date:end_date])
150
151	# NULL checks
152	.where(dt.email.isnotnull())
153	.where(dt.phone.isnull())
154
155	# Comparison
156	.where(dt.grand_total > 1000)
157	.where(dt.grand_total >= 500)
158	.where(dt.status != "Cancelled")
159	```
160
161	---
162
163	## Cross-DB Compatibility
164
165	```python
166	from frappe.query_builder.utils import ImportMapper, db_type_is
167	from frappe.query_builder.custom import GROUP_CONCAT, STRING_AGG
168
169	# ImportMapper

People who liked this also viewed

6 picks

Type

frappe-syntax-query-builder

151stars49forks

View on GitHub

How to install frappe-syntax-query-builder?

impertio-studio/frappe_claude_skill_package/frappe-syntax-query-builder

$npx -y skills add Impertio-Studio/Frappe_Claude_Skill_Package --skill frappe-syntax-query-builder

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Frappe Query Builder (frappe.qb)
2
3	## Quick Reference
4
5	```python
6	from frappe.query_builder import DocType, Field
7	from frappe.query_builder.functions import Count, Sum, IfNull
8	from frappe.query_builder.custom import ConstantColumn, GROUP_CONCAT
9	from frappe.query_builder.terms import SubQuery
10	from frappe.query_builder.utils import ImportMapper, db_type_is
11	from pypika.terms import Case, ValueWrapper
12	from pypika import CustomFunction, Order
13	```
14
15	\| Action \| Pattern \|
16	\|--------\|---------\|
17	\| SELECT \| `frappe.qb.from_("DocType").select("field1", "field2")` \|
18	\| WHERE \| `.where(Field("status") == "Open")` \|
19	\| ORDER \| `.orderby("creation", order=Order.desc)` \|
20	\| LIMIT \| `.limit(10).offset(0)` \|
21	\| JOIN \| `.left_join(dt2).on(dt2.name == dt1.parent)` \|
22	\| COUNT \| `.select(Count("*"))` \|
23	\| INSERT \| `frappe.qb.into("DocType").columns("f1", "f2").insert("v1", "v2")` \|
24	\| UPDATE \| `frappe.qb.update("DocType").set("field", "value").where(...)` \|
25	\| DELETE \| `frappe.qb.from_("DocType").delete().where(...)` \|
26	\| RUN \| `.run()` (tuples) / `.run(as_dict=True)` (dicts) \|
27
28	---
29
30	## Decision Tree
31
32	```
33	Need to query the database?
34	│
35	├─ Simple get/list → frappe.db.get_value(), frappe.get_all()
36	│ (See frappe-core-database skill)
37	│
38	├─ Complex query with joins/aggregates/subqueries → frappe.qb ✓
39	│
40	├─ Very complex SQL not expressible in qb → frappe.db.sql()
41	│ (ALWAYS use parameterized values: frappe.db.sql(query, values))
42	│
43	└─ Need cross-DB compatibility → frappe.qb + ImportMapper ✓
44
45	Using frappe.qb?
46	│
47	├─ Table reference → DocType("Sales Order") — NEVER use Table()
48	├─ Field reference → dt.field_name or Field("field_name")
49	├─ Execute → .run() for tuples, .run(as_dict=True) for dicts
50	├─ DB-specific function → ImportMapper({db_type_is.MARIADB: X, db_type_is.POSTGRES: Y})
51	└─ Get SQL string → query.get_sql() — NEVER pass to frappe.db.sql()
52	```
53
54	---
55
56	## Core Patterns
57
58	### SELECT with DocType
59
60	```python
61	# ALWAYS use DocType() for table references — adds "tab" prefix
62	so = frappe.qb.DocType("Sales Order")
63	soi = frappe.qb.DocType("Sales Order Item")
64
65	orders = (
66	frappe.qb.from_(so)
67	.select(so.name, so.customer, so.grand_total)
68	.where(so.status == "To Deliver and Bill")
69	.where(so.docstatus == 1)
70	.orderby(so.creation, order=Order.desc)
71	.limit(20)
72	.run(as_dict=True)
73	)
74	```
75
76	### JOIN
77
78	```python
79	result = (
80	frappe.qb.from_(so)
81	.left_join(soi).on(soi.parent == so.name)
82	.select(so.name, so.customer, soi.item_code, soi.qty)
83	.where(so.docstatus == 1)
84	.where(soi.item_code.like("ITEM-%"))
85	.run(as_dict=True)
86	)
87	```
88
89	### Aggregation
90
91	```python
92	from frappe.query_builder.functions import Count, Sum
93
94	gl = frappe.qb.DocType("GL Entry")
95	result = (
96	frappe.qb.from_(gl)
97	.select(gl.account, Sum(gl.debit).as_("total_debit"), Count("*").as_("entries"))
98	.where(gl.docstatus == 1)
99	.groupby(gl.account)
100	.run(as_dict=True)
101	)
102
103	# Shortcut aggregation methods
104	total = frappe.qb.sum("GL Entry", "debit", filters={"account": "Sales"})
105	max_qty = frappe.qb.max("Stock Ledger Entry", "actual_qty", filters={"item_code": "ITEM-001"})
106	```
107
108	### INSERT / UPDATE / DELETE
109
110	```python
111	# INSERT
112	frappe.qb.into("Activity Log").columns("user", "action").insert("admin", "login").run()
113
114	# UPDATE
115	customer = frappe.qb.DocType("Customer")
116	(frappe.qb.update(customer)
117	.set(customer.status, "Active")
118	.where(customer.name == "CUST-001")
119	.run())
120
121	# DELETE
122	frappe.qb.from_("Error Log").delete().where(Field("creation") < "2024-01-01").run()
123	```
124
125	---
126
127	## Filtering
128
129	```python
130	dt = frappe.qb.DocType("Sales Order")
131
132	# Equality
133	.where(dt.status == "Open")
134
135	# OR (pipe operator)
136	.where((dt.status == "Open") \| (dt.status == "Draft"))
137
138	# AND (chain .where() calls)
139	.where(dt.status == "Open")
140	.where(dt.docstatus == 1)
141
142	# LIKE
143	.where(dt.customer.like("CUST-%"))
144
145	# IN
146	.where(dt.status.isin(["Open", "Draft"]))
147
148	# BETWEEN (bracket syntax)
149	.where(dt.creation[start_date:end_date])
150
151	# NULL checks
152	.where(dt.email.isnotnull())
153	.where(dt.phone.isnull())
154
155	# Comparison
156	.where(dt.grand_total > 1000)
157	.where(dt.grand_total >= 500)
158	.where(dt.status != "Cancelled")
159	```
160
161	---
162
163	## Cross-DB Compatibility
164
165	```python
166	from frappe.query_builder.utils import ImportMapper, db_type_is
167	from frappe.query_builder.custom import GROUP_CONCAT, STRING_AGG
168
169	# ImportMapper

People who liked this also viewed

6 picks

Type