[Skip to content](#main)

[→](/)

[![Avunu](/brand/avunu-logo.svg)![Avunu](/brand/avunu-logo-light.svg)](/)[Services](/services/)[Consulting](/services/consulting/)[ERP](/services/erp/)[Odoo](/services/erp/odoo/)[ERPNext](/services/erp/erpnext/)[Custom software](/services/custom-software/)[Devices & IoT](/services/devices-iot/)[Websites](/services/websites/)[Infrastructure](/services/infrastructure/)[Partners](/partners/)

[Work](/work/)[Open source](/open-source/)[Knowledge base](/kb/)[About](/about/)

[About Avunu](/about/)[Team](/about/team/)[Insights](/insights/)[Contact](/contact/)

[Client portal](https://erp.avunu.net/projects)[Start a project](/start/)

- [Services](/services/)[Consulting](/services/consulting/)
- [ERP](/services/erp/)
- [Odoo](/services/erp/odoo/)
- [ERPNext](/services/erp/erpnext/)
- [Custom software](/services/custom-software/)
- [Devices & IoT](/services/devices-iot/)
- [Websites](/services/websites/)
- [Infrastructure](/services/infrastructure/)
- [Partners](/partners/)

[Work](/work/)[Open source](/open-source/)[Knowledge base](/kb/)[About](/about/)

- [About Avunu](/about/)
- [Team](/about/team/)
- [Insights](/insights/)
- [Contact](/contact/)

[Client portal](https://erp.avunu.net/projects)[Start a project](/start/)

[Knowledge base](/kb/)[Frappe](/kb/frappe/)Query Builder & PyPika Extensions

Search the knowledge basePress/

ESC

Jump to

[Browse the knowledge base](/kb/)[Frappe](/kb/frappe/)[ERPNext](/kb/erpnext/)[Odoo](/kb/odoo/)[WordPress](/kb/wordpress/)[NixOS](/kb/nixos/)[Email](/kb/email/)[Linux](/kb/linux/)[Databases](/kb/databases/)[Containers](/kb/containers/)[Proxmox](/kb/proxmox/)[Git & Dev Tools](/kb/git-and-dev-tools/)

↵select↑↓navigateescclose

Search the knowledge base

[Skip to the article](#kb-article)[Frappe](/kb/frappe/)20 articles

- [Overview](/kb/frappe/)
- [Bench Operations](/kb/frappe/bench-operations/)
- [Builder Page Scripts](/kb/frappe/builder-page-scripts/)
- [Client-Side Recipes](/kb/frappe/client-side-recipes/)
- [Common Query Patterns](/kb/frappe/common-query-patterns/)
- [Custom Field Controllers](/kb/frappe/custom-field-controllers/)
- [Custom Pages](/kb/frappe/custom-pages/)
- [Custom Views](/kb/frappe/custom-views/)
- [Customizations API](/kb/frappe/customizations-api/)
- [Customizing Grid Columns for Child Tables](/kb/frappe/customizing-grid-columns-child-tables/)
- [Doctype States & Status Fields](/kb/frappe/doctype-states-status-fields/)
- [Grandchild Table Fields](/kb/frappe/grandchild-table-fields/)
- [Portal Views](/kb/frappe/portal-views/)
- [Project Structure & Hooks](/kb/frappe/project-structure-hooks/)
- [Query Builder & PyPika Extensions](/kb/frappe/query-builder-pypika/)
- [Quick Entry Forms](/kb/frappe/quick-entry-forms/)
- [Script Reports](/kb/frappe/script-reports/)
- [Testing on Live Databases](/kb/frappe/testing-live-databases/)
- [Type Annotations & Controller Overrides](/kb/frappe/type-annotations-controller-overrides/)
- [Vite & TypeScript Compilation](/kb/frappe/vite-typescript-compilation/)
- [Web Forms](/kb/frappe/web-forms/)

Other sections[ERPNext](/kb/erpnext/)[Odoo](/kb/odoo/)[WordPress](/kb/wordpress/)[NixOS](/kb/nixos/)[Email](/kb/email/)[Linux](/kb/linux/)[Databases](/kb/databases/)[Containers](/kb/containers/)[Proxmox](/kb/proxmox/)[Git & Dev Tools](/kb/git-and-dev-tools/)

In Frappe: 20 articles

[Overview](/kb/frappe/)[Bench Operations](/kb/frappe/bench-operations/)[Builder Page Scripts](/kb/frappe/builder-page-scripts/)[Client-Side Recipes](/kb/frappe/client-side-recipes/)[Common Query Patterns](/kb/frappe/common-query-patterns/)[Custom Field Controllers](/kb/frappe/custom-field-controllers/)[Custom Pages](/kb/frappe/custom-pages/)[Custom Views](/kb/frappe/custom-views/)[Customizations API](/kb/frappe/customizations-api/)[Customizing Grid Columns for Child Tables](/kb/frappe/customizing-grid-columns-child-tables/)[Doctype States & Status Fields](/kb/frappe/doctype-states-status-fields/)[Grandchild Table Fields](/kb/frappe/grandchild-table-fields/)[Portal Views](/kb/frappe/portal-views/)[Project Structure & Hooks](/kb/frappe/project-structure-hooks/)[Query Builder & PyPika Extensions](/kb/frappe/query-builder-pypika/)[Quick Entry Forms](/kb/frappe/quick-entry-forms/)[Script Reports](/kb/frappe/script-reports/)[Testing on Live Databases](/kb/frappe/testing-live-databases/)[Type Annotations & Controller Overrides](/kb/frappe/type-annotations-controller-overrides/)[Vite & TypeScript Compilation](/kb/frappe/vite-typescript-compilation/)[Web Forms](/kb/frappe/web-forms/)

Other sections[ERPNext](/kb/erpnext/)[Odoo](/kb/odoo/)[WordPress](/kb/wordpress/)[NixOS](/kb/nixos/)[Email](/kb/email/)[Linux](/kb/linux/)[Databases](/kb/databases/)[Containers](/kb/containers/)[Proxmox](/kb/proxmox/)[Git & Dev Tools](/kb/git-and-dev-tools/)

FrappeQuery Builder & PyPika Extensions

Advanced Frappe Query Builder usage with PyPika, including custom functions, JSON aggregation, and subqueries.

UpdatedMar 13, 2026

Tagsfrappequery-builderpypikasqlpython

Reading time5 min

On this page[Basic Query Builder Usage](#basic-query-builder-usage)[Table Aliasing](#table-aliasing)[Subquery JOINs (Derived Tables)](#subquery-joins-derived-tables)[Literal Values with ValueWrapper](#literal-values-with-valuewrapper)[Computed Expressions in SELECT](#computed-expressions-in-select)[Custom PyPika Functions Module](#custom-pypika-functions-module)[JSON Aggregate Functions](#json-aggregate-functions)[Correlated Subquery as a Custom Term](#correlated-subquery-as-a-custom-term)[External SQL Files](#external-sql-files)[Parameterized Optional Filters in SQL](#parameterized-optional-filters-in-sql)[JSON-Returning SQL](#json-returning-sql)

Frappe's Query Builder is built on PyPika. While the standard API covers basic queries, advanced use cases require direct PyPika manipulation and custom function extensions.

## Basic Query Builder Usage

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> frappe</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">from</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> frappe.query_builder </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> DocType</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">from</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> frappe.query_builder.functions </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> Coalesce, Sum</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">from</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> pypika.functions </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> NullIf</span>

<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">Item </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> DocType(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"Item"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">Bin </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> DocType(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"Bin"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>

<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">query </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    frappe.qb.from_(Item)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .inner_join(Bin).on(Bin.item_code </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">==</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> Item.name)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .select(</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        Item.name,</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        Coalesce(Sum(Bin.actual_qty), </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">0</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"total_qty"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">),</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    )</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .where(Item.disabled </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">==</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">0</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .groupby(Item.name)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .orderby(Item.name)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">result </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> query.run(</span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">as_dict</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">True</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
```

## Table Aliasing

Use `.as_()` on DocType references for self-joins or clarity:

```
<span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> DocType(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"Item Group"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>

<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">query </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    frappe.qb.from_(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"ig"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">))</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .select(</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"ig"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).name.as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"ig_name"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">),</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"ig"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).item_group_name,</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    )</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .orderby(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"ig"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).lft)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
```

## Subquery JOINs (Derived Tables)

Build subqueries and join them as derived tables:

```
<span style="--shiki-dark:#8B949E;--shiki-light:#6E7781"># Build subquery</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">stock_used_sq </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    frappe.qb.from_(StockLedgerEntry)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .inner_join(StockEntry)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .on(</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        (StockEntry.name </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">==</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> StockLedgerEntry.voucher_no)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">&</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (StockEntry.stock_entry_type </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">==</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"Manufacture"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    )</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .select(</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        StockLedgerEntry.item_code,</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        Sum(StockLedgerEntry.actual_qty </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">*</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">-</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">1</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"stock_used"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">),</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    )</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .where(StockLedgerEntry.actual_qty </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E"><</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">0</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .groupby(StockLedgerEntry.item_code)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>

<span style="--shiki-dark:#8B949E;--shiki-light:#6E7781"># Alias and join as derived table</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">StockUsed </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> stock_used_sq.as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"stock_used_sq"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>

<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">query </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    frappe.qb.from_(Items)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .left_join(StockUsed).on(StockUsed.item_code </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">==</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> Items.name)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .select(</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        Items.name,</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        Coalesce(StockUsed.stock_used, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">0</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"stock_used"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">),</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    )</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
```

## Literal Values with `ValueWrapper`

Insert literal values into SELECT for client-side-only columns:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">from</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> pypika.terms </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> ValueWrapper</span>

<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">query </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    frappe.qb.from_(Items)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .select(</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        Items.name,</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        ValueWrapper(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">0</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"reorder_qty"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">),       </span><span style="--shiki-dark:#8B949E;--shiki-light:#6E7781"># Editable on client only</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        ValueWrapper(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">0</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"projected_stock"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">),    </span><span style="--shiki-dark:#8B949E;--shiki-light:#6E7781"># Computed on client only</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    )</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
```

## Computed Expressions in SELECT

Inline arithmetic in column selection:

```
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">query </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    frappe.qb.from_(Bins)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .select(</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        (</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">            Coalesce(Sum(Bins.actual_qty), </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">0</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">            </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">-</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> Coalesce(Sum(Bins.reserved_qty), </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">0</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">            </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">+</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> Coalesce(OrderedStock.ordered_stock, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">0</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        ).as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"available_stock"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">),</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    )</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
```

## Custom PyPika Functions Module

Create a utility module for custom SQL functions not available in PyPika:

```
<span style="--shiki-dark:#8B949E;--shiki-light:#6E7781"># myapp/utils/json_functions.py</span>

<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">from</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> typing </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> Any</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">from</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> frappe.query_builder.functions </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> AggregateFunction</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">from</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> frappe.query_builder.terms </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> ValueWrapper</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">from</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> pypika.terms </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> Function, Term</span>

<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">class</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">_Fn</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">:</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"""Callable wrapper that creates a Function term with any number of arguments.</span>
<span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">    </span>
<span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">    PyPika's CustomFunction drops ALL positional arguments when `params`</span>
<span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">    is not provided. This wrapper always passes them through.</span>
<span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">    """</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">def</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">__init__</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(self, name: </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">str</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">):</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.name </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> name</span>

<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">def</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">__call__</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(self, </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">*</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">args: Any, </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">**</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">kwargs: Any) -> Function:</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">return</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> Function(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.name, </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">*</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">args, </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">alias</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">kwargs.get(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"alias"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">))</span>

<span style="--shiki-dark:#8B949E;--shiki-light:#6E7781"># JSON creation functions (MariaDB/MySQL)</span>
<span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">JSON_OBJECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> _Fn(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"JSON_OBJECT"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">JSON_ARRAY</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> _Fn(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"JSON_ARRAY"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">JSON_MERGE_PATCH</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> _Fn(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"JSON_MERGE_PATCH"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">JSON_EXTRACT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> _Fn(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"JSON_EXTRACT"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">JSON_UNQUOTE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> _Fn(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"JSON_UNQUOTE"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">JSON_KEYS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> _Fn(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"JSON_KEYS"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>

<span style="--shiki-dark:#8B949E;--shiki-light:#6E7781"># SQL utility functions</span>
<span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IFNULL</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> _Fn(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"IFNULL"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">CONCAT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> _Fn(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"CONCAT"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
```

### JSON Aggregate Functions

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">class</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">JSON_OBJECTAGG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">AggregateFunction</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">):</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"""Aggregates key-value pairs into a JSON object."""</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">def</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">__init__</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(self, key, value, alias</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">None</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">):</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">super</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">().</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">__init__</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"JSON_OBJECTAGG"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, key, value, </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">alias</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">alias)</span>

<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">class</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">JSON_ARRAYAGG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">AggregateFunction</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">):</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"""JSON_ARRAYAGG with optional DISTINCT and ORDER BY support.</span>
<span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">    </span>
<span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">    MariaDB syntax: JSON_ARRAYAGG([DISTINCT] expr [ORDER BY ...])</span>
<span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">    """</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">def</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">__init__</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(self, term, distinct</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">False</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, orderby</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">None</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, alias</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">None</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">):</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">super</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">().</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">__init__</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"JSON_ARRAYAGG"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">alias</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">alias)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._term </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> term </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">if</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">isinstance</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(term, Term) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">else</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> ValueWrapper(term)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._distinct </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> distinct</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._orderby </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> orderby</span>

<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">def</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#D2A8FF;--shiki-light:#8250DF">get_function_sql</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(self, </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">**</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">kwargs: Any) -> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">str</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">:</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        term_sql </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._term.get_sql(</span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">with_alias</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">False</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">**</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">kwargs)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        distinct_sql </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"DISTINCT "</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">if</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._distinct </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">else</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">""</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        order_sql </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">""</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">if</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._orderby </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">is</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">not</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">None</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">:</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">            order_terms </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">                </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._orderby </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">if</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">isinstance</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._orderby, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">list</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">else</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> [</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._orderby]</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">            )</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">            order_parts </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> [</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">                t.get_sql(</span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">with_alias</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">False</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">**</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">kwargs) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">if</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">isinstance</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(t, Term) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">else</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">str</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(t)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">                </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">for</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> t </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">in</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> order_terms</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">            ]</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">            order_sql </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">f</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">" ORDER BY </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">{</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">', '</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.join(order_parts)</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">}</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">return</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">f</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"JSON_ARRAYAGG(</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">{</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">distinct_sql</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">}{</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">term_sql</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">}{</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">order_sql</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">}</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">)"</span>
```

Usage:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">from</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> myapp.utils.json_functions </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">JSON_OBJECTAGG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">JSON_ARRAYAGG</span>

<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">result </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    frappe.qb.from_(Item)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .left_join(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IVA</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).on(Item.name </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">==</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IVA</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.parent)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .select(</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        Item.item_code,</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        JSON_OBJECTAGG(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IVA</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.attribute, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IVA</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.attribute_value).as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"attributes"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">),</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    )</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .where(Item.variant_of </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">==</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> template_item)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .groupby(Item.item_code)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).run(</span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">as_dict</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">True</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
```

### Correlated Subquery as a Custom Term

For complex SQL that PyPika can't express natively, subclass `Term` and emit raw SQL:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">class</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">NESTED_SET_DEPTH</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">Term</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">):</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"""Calculate depth of a node in a nested set model by counting ancestors.</span>
<span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">    </span>
<span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">    Generates: (SELECT COUNT(*) FROM `tabX` a </span>
<span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">                WHERE a.lft < `alias`.lft AND a.rgt > `alias`.rgt</span>
<span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">                [AND a.lft >= root_lft AND a.rgt <= root_rgt])</span>
<span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">    """</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">def</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">__init__</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(self, table, outer_alias, root_lft</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">None</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, root_rgt</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">None</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">):</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">super</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">().</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">__init__</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">()</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._table </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> table</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._outer_alias </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> outer_alias</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._root_lft </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> root_lft</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._root_rgt </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> root_rgt</span>

<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">def</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#D2A8FF;--shiki-light:#8250DF">get_sql</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(self, with_alias</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">False</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">**</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">kwargs):</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        table_name </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">f</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"`tab</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">{</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._table</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">}</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">`"</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        outer_ref </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">f</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"`</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">{</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._outer_alias</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">}</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">`"</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        sql </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">            </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">f</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"(SELECT COUNT(*) FROM </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">{</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">table_name</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">}</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069"> a "</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">            </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">f</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"WHERE a.lft < </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">{</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">outer_ref</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">}</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">.lft AND a.rgt > </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">{</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">outer_ref</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">}</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">.rgt"</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        )</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">if</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._root_lft </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">is</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">not</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">None</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">and</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._root_rgt </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">is</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">not</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">None</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">:</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">            sql </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">+=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">f</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">" AND a.lft >= </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">{</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._root_lft</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">}</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069"> AND a.rgt <= </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">{</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">._root_rgt</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">}</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        sql </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">+=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">")"</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">if</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> with_alias </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">and</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.alias:</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">            sql </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">+=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">f</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">" `</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">{</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">self</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.alias</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">}</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">`"</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">return</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> sql</span>
```

Usage in a query:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">from</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> myapp.utils.json_functions </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">NESTED_SET_DEPTH</span>

<span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> DocType(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"Item Group"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">indent </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> NESTED_SET_DEPTH(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"Item Group"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"ig"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>

<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">query </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    frappe.qb.from_(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"ig"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">))</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .select(</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"ig"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).name,</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        indent.as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"ig_indent"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">),  </span><span style="--shiki-dark:#8B949E;--shiki-light:#6E7781"># Computed depth column</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    )</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    .orderby(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.as_(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"ig"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).lft)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
```

For subtree queries (depth relative to a subtree root):

```
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">root </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> frappe.qb.from_(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).select(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.lft, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.rgt).where(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">IG</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.name </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">==</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"Timber"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).run(</span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">as_dict</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">True</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)[</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">0</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">]</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">indent </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> NESTED_SET_DEPTH(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"Item Group"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"ig"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">root_lft</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">root[</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"lft"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">], </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">root_rgt</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">root[</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"rgt"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">])</span>
```

## External SQL Files

For complex queries, store SQL in separate files:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">from</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> pathlib </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> Path</span>

<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">query </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (Path(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">__file__</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).parent </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">/</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"my_report.sql"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">).read_text()</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">result </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> frappe.db.sql(query, {</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"param"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">: value}, </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">as_dict</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">True</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
```

### Parameterized Optional Filters in SQL

Use this pattern for filters that may or may not be provided:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">WHERE</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    (</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">p</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">name</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> %(project)s </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">OR</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> %(project)s </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">IS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">NULL</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">OR</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> %(project)s </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">''</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AND</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">p</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">status</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> %(</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">status</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)s </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">OR</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> %(</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">status</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)s </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">IS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">NULL</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">OR</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> %(</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">status</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)s </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">''</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AND</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        %(date_start)s </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">IS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">NULL</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">OR</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> %(date_end)s </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">IS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">NULL</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">        </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">OR</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">p</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">actual_end_date</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">BETWEEN</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> %(date_start)s </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AND</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> %(date_end)s</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    )</span>
```

Pass `None` for unused filters in the parameter dictionary.

## JSON-Returning SQL

MariaDB can return structured JSON in a single result cell:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">SELECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">JSON_OBJECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'attributes'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, (</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">SELECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> JSON_ARRAYAGG(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">JSON_OBJECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'name'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">a</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">name</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'values'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">a</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">values</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">))</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">                   </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">FROM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> attributes a),</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">    </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'variants'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, (</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">SELECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> JSON_ARRAYAGG(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">JSON_OBJECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'code'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">v</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">code</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'attrs'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">v</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">attrs</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">))</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">                 </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">FROM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> variants v)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> result</span>
```

Parse in Python:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">import</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> json</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">raw </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> frappe.db.sql(query, params, </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">pluck</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">"result"</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">result </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> json.loads(raw[</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">0</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">] </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">if</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> raw[</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">0</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">] </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">else</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> [])</span>
```

Need help with Frappe?

Our notes can take you a long way, but sometimes you'd rather have someone who's done it before.

[ERPNext implementation](/services/erp/erpnext/)

[← PreviousProject Structure & Hooks](/kb/frappe/project-structure-hooks/)[Next →Quick Entry Forms](/kb/frappe/quick-entry-forms/)

CC0This article is in the public domain ([CC0 1.0](https://creativecommons.org/publicdomain/zero/1.0/)), code samples included. Use it however helps you.

On this page

- [Basic Query Builder Usage](#basic-query-builder-usage)
- [Table Aliasing](#table-aliasing)
- [Subquery JOINs (Derived Tables)](#subquery-joins-derived-tables)
- [Literal Values with ValueWrapper](#literal-values-with-valuewrapper)
- [Computed Expressions in SELECT](#computed-expressions-in-select)
- [Custom PyPika Functions Module](#custom-pypika-functions-module)
- [JSON Aggregate Functions](#json-aggregate-functions)
- [Correlated Subquery as a Custom Term](#correlated-subquery-as-a-custom-term)
- [External SQL Files](#external-sql-files)
- [Parameterized Optional Filters in SQL](#parameterized-optional-filters-in-sql)
- [JSON-Returning SQL](#json-returning-sql)

[Back to top↑](#top)

Services

- [ERP implementation](/services/erp/)
- [Odoo](/services/erp/odoo/)
- [ERPNext](/services/erp/erpnext/)
- [Custom software](/services/custom-software/)
- [Devices & IoT](/services/devices-iot/)
- [Websites](/services/websites/)
- [Infrastructure](/services/infrastructure/)

Resources

- [Our work](/work/)
- [Open source](/open-source/)
- [Knowledge base](/kb/)
- [Insights](/insights/)

Company

- [About](/about/)
- [Team](/about/team/)
- [Partners](/partners/)
- [Contact](/contact/)

Clients

- [Project portal](https://erp.avunu.net/projects)
- [Invoices](https://erp.avunu.net/)
- [Support](https://erp.avunu.net/)

[![Avunu](/brand/avunu-logo-light.svg)](/)[\<av-icon data-jx-static>\<svg viewBox="0 0 24 24" width="20" height="20" fill="currentColor" stroke="none" stroke-width="1.6" stroke-linecap="round" stroke-linejoin="round" aria-hidden="true" focusable="false">\<path d="M20.400 20.500h-3.600v-5.600c0-1.300 0-3-1.800-3s-2.100 1.400-2.100 2.900v5.700H9.400V9h3.400v1.600c.5-.9 1.600-1.900 3.400-1.900 3.600 0 4.300 2.400 4.300 5.500v6.300zM5.300 7.400a2.100 2.100 0 1 1 0-4.100 2.100 2.100 0 0 1 0 4.100zm1.800 13.100H3.600V9h3.500v11.500zM22.200 0H1.800C.8 0 0 .8 0 1.700v20.600c0 .9.800 1.700 1.800 1.700h20.400c1 0 1.800-.8 1.800-1.700V1.700C24 .8 23.200 0 22.200 0z">\</path>\</svg>\</av-icon>](https://www.linkedin.com/company/avunu)[\<av-icon data-jx-static>\<svg viewBox="0 0 24 24" width="20" height="20" fill="currentColor" stroke="none" stroke-width="1.6" stroke-linecap="round" stroke-linejoin="round" aria-hidden="true" focusable="false">\<path d="M12 .3a12 12 0 0 0-3.8 23.4c.6.1.8-.3.8-.6v-2c-3.3.7-4-1.6-4-1.6-.6-1.4-1.4-1.8-1.4-1.8-1-.7.1-.7.1-.7 1.2.1 1.8 1.2 1.8 1.2 1 1.8 2.8 1.3 3.5 1 .1-.8.4-1.3.7-1.6-2.7-.3-5.5-1.3-5.5-5.9 0-1.3.5-2.4 1.2-3.2-.1-.3-.5-1.5.1-3.2 0 0 1-.3 3.3 1.2a11.500 11.500 0 0 1 6 0c2.300-1.500 3.300-1.200 3.300-1.200.6 1.700.2 2.900.1 3.200.8.8 1.200 1.900 1.200 3.200 0 4.600-2.800 5.600-5.500 5.900.4.400.8 1.100.8 2.200v3.300c0 .3.2.7.8.6A12 12 0 0 0 12 .3">\</path>\</svg>\</av-icon>](https://github.com/Avunu)[Client portal](https://erp.avunu.net/projects)[Start a project](/start/)

Avunu LLC · 948 East Philadelphia Street, York, PA 17403 · (717) 814-8540Knowledge base articles are in the public domain[Privacy](/privacy/)[Terms](/terms/)
