Query Builder & OQL

Overview

BitCode provides a comprehensive query builder that translates to both SQL (via GORM) and MongoDB (via official driver) with full parity. It supports 3 query interfaces:

Programmatic Go API — Fluent builder pattern
JSON DSL — For JSON definitions (process steps, API, views)
OQL (Object Query Language) — String-based query language with 3 syntax styles

Query Builder (Go API)

Basic WHERE

q := persistence.NewQuery().
    Where("status", "=", "active").
    WhereGt("age", 18).
    WhereLike("email", "%@gmail.com").
    WhereIn("city", []string{"Jakarta", "Bandung"}).
    WhereBetween("created_at", "2024-01-01", "2024-12-31").
    WhereNull("deleted_at")

OR Conditions

q := persistence.NewQuery().
    Where("status", "=", "active").
    OrWhere("status", "=", "pending")

Grouped Conditions

// WHERE active = true AND (city = 'Jakarta' OR city = 'Bandung')
q := persistence.NewQuery().
    Where("active", "=", true).
    WhereGroup(func(sub *persistence.Query) *persistence.Query {
        return sub.Where("city", "=", "Jakarta").OrWhere("city", "=", "Bandung")
    })

NOT

q := persistence.NewQuery().
    WhereNot(func(sub *persistence.Query) *persistence.Query {
        return sub.Where("status", "=", "deleted")
    })

JOINs

q := persistence.NewQuery().
    InnerJoin("companies", "contacts.company_id", "companies.id").
    LeftJoin("addresses", "contacts.id", "addresses.contact_id").
    RightJoin("departments", "contacts.dept_id", "departments.id").
    CrossJoin("settings").
    FullJoin("profiles", "contacts.id", "profiles.contact_id")

Aggregates

q := persistence.NewQuery().
    SetGroupBy("department").
    SelectCount("*", "total").
    SelectSum("salary", "total_salary").
    SelectAvg("age", "avg_age").
    SelectMin("salary", "min_salary").
    SelectMax("salary", "max_salary").
    SelectCountDistinct("city", "unique_cities").
    HavingCondition("COUNT", "*", ">", 5)

Eager Loading (WITH / Preload)

q := persistence.NewQuery().
    WithRelation("company").
    WithRelation("tags").
    WithRelationConditions("orders", []persistence.Condition{
        {Field: "status", Operator: "=", Value: "active"},
    })

UNION

q1 := persistence.NewQuery().Where("type", "=", "A")
q2 := persistence.NewQuery().Where("type", "=", "B")
q := q1.Union(q2)       // UNION
q := q1.UnionAll(q2)    // UNION ALL

Subqueries

sub := persistence.NewQuery().SetSelect("id").Where("active", "=", true)
q := persistence.NewQuery().WhereInSubQuery("company_id", sub)
q := persistence.NewQuery().WhereExistsQuery(sub)

Locking

q := persistence.NewQuery().LockForUpdate()
q := persistence.NewQuery().LockForShare()

Soft Delete Scopes

q := persistence.NewQuery().WithTrashed()    // include deleted
q := persistence.NewQuery().OnlyTrashed()    // only deleted

Scopes (Reusable)

activeScope := func(q *persistence.Query) *persistence.Query {
    return q.Where("active", "=", true)
}
q := persistence.NewQuery().Scope(activeScope)

Raw Expressions

q := persistence.NewQuery().
    WhereRawExpr("age > ? AND age < ?", 18, 65).
    SelectRawExpr("COUNT(*) as total").
    HavingRawExpr("COUNT(*) > ?", 5)

Repository Methods

Method	Description
`FindAll(ctx, query, page, pageSize)`	Paginated query
`FindAllActive(ctx, query, page, pageSize)`	Active-only query
`FindAllWithTrashed(ctx, query, page, pageSize)`	Include soft-deleted
`FindAllOnlyTrashed(ctx, query, page, pageSize)`	Only soft-deleted
`Count(ctx, query)`	Count matching
`Sum(ctx, field, query)`	Sum field
`Avg(ctx, field, query)`	Average field
`Min(ctx, field, query)`	Minimum field
`Max(ctx, field, query)`	Maximum field
`Pluck(ctx, field, query)`	Extract single column
`Exists(ctx, query)`	Check existence
`Aggregate(ctx, query)`	Grouped aggregate results
`Chunk(ctx, query, size, fn)`	Batch processing
`Increment(ctx, id, field, value)`	Increment field
`Decrement(ctx, id, field, value)`	Decrement field
`Transaction(ctx, fn)`	Transactional execution
`RawQuery(ctx, sql, values...)`	Raw SQL query
`RawExec(ctx, sql, values...)`	Raw SQL execute

Model Process Built-in Functions

All accessible via models.{name}.{operation}:

Operation	Args
`Get` / `Find`	`id`
`GetAll` / `FindAll`	`wheres`, `order`, `page`, `page_size`, `oql`
`Paginate`	same as FindAll
`Create`	`data`
`Update`	`id`, `data`
`Delete`	`id`
`Upsert`	`data`, `unique`
`Count` / `CountActive`	`wheres`, `oql`
`Sum` / `SumActive`	`field`, `wheres`, `oql`
`Avg`	`field`, `wheres`, `oql`
`Min`	`field`, `wheres`, `oql`
`Max`	`field`, `wheres`, `oql`
`Pluck`	`field`, `wheres`, `oql`
`Exists`	`wheres`, `oql`
`Aggregate`	`wheres`, `group_by`, `aggregates`, `oql`
`WithTrashed`	`wheres`, `page`, `page_size`, `oql`
`OnlyTrashed`	`wheres`, `page`, `page_size`, `oql`
`Increment`	`id`, `field`, `value`
`Decrement`	`id`, `field`, `value`

Dynamic Finders

Convention-based query methods parsed from operation name. Available via models.{name}.{operation}:

Patterns

Pattern	Example	SQL
`FindBy{Field}`	`FindByEmail`	`WHERE email = ? LIMIT 1`
`FindAllBy{Field}`	`FindAllByStatus`	`WHERE status = ?`
`FindBy{Field}And{Field}`	`FindByStatusAndCity`	`WHERE status = ? AND city = ?`
`FindBy{Field}Or{Field}`	`FindByEmailOrPhone`	`WHERE email = ? OR phone = ?`
`CountBy{Field}`	`CountByStatus`	`COUNT WHERE status = ?`
`ExistsBy{Field}`	`ExistsByEmail`	`EXISTS WHERE email = ?`
`DeleteBy{Field}`	`DeleteByStatus`	`DELETE WHERE status = ?`
`SumBy{AggField}{Field}`	`SumByAmountStatus`	`SUM(amount) WHERE status = ?`
`AvgBy{AggField}{Field}`	`AvgByAgeStatus`	`AVG(age) WHERE status = ?`
`MinBy{AggField}{Field}`	`MinByPriceCategory`	`MIN(price) WHERE category = ?`
`MaxBy{AggField}{Field}`	`MaxBySalaryDepartment`	`MAX(salary) WHERE department = ?`
`PluckBy{AggField}{Field}`	`PluckByEmailStatus`	`SELECT email WHERE status = ?`

Operator Suffixes

Suffix	Operator	Example
(none)	`=`	`FindByStatus`
`Gt`	`>`	`FindAllByAgeGt`
`Gte`	`>=`	`FindAllByAgeGte`
`Lt`	`<`	`FindAllByPriceLt`
`Lte`	`<=`	`FindAllByPriceLte`
`Not`	`!=`	`FindAllByStatusNot`
`Like`	`LIKE`	`FindAllByNameLike`
`In`	`IN`	`FindAllByCityIn`
`NotIn`	`NOT IN`	`FindAllByStatusNotIn`
`Between`	`BETWEEN`	`FindAllByAgeBetween`
`IsNull`	`IS NULL`	`FindAllByDeletedAtIsNull`
`IsNotNull`	`IS NOT NULL`	`FindAllByEmailIsNotNull`

OrderBy

Append OrderBy{Field}Asc or OrderBy{Field}Desc:

FindAllByStatusOrderByNameAsc
FindAllByActiveOrderByCreatedAtDesc
FindAllByStatusOrderByNameAscAndCreatedAtDesc

Usage in JSON

{
  "process": "models.contacts.FindAllByStatusOrderByNameAsc",
  "args": { "status": "active" }
}

{
  "process": "models.orders.SumByAmountStatus",
  "args": { "status": "confirmed" }
}

Module-Qualified Model Names

When multiple modules define models with the same name (e.g., CRM and HRM both have contact), use module-qualified names:

models.crm.contact.FindAll        // CRM contacts
models.hrm.contact.FindAll        // HRM contacts
models.crm.contact.FindByEmail    // Dynamic finder on CRM contact

If a model name is unique across all modules, the short form works:

models.order.FindAll              // Only one 'order' model exists

If a model name is ambiguous (exists in multiple modules) and you use the short form, you get a clear error:

model "contact" is ambiguous — exists in modules: crm, hrm.
Use qualified name: models.crm.contact.FindAll or models.hrm.contact.FindAll

Table Name Resolution

All relation loading (WITH/preload), JOINs, and Many2Many operations automatically resolve model names to actual table names using the module's table prefix configuration:

// module.json
{ "name": "crm", "table": { "prefix": "crm" } }

// model: contact → table: crm_contact
// WITH company → resolves to crm_company (not "company")
// JOIN companies → resolves to crm_companies (not "companies")

OQL (Object Query Language)

Three syntax styles, auto-detected by ParseOQL():

Style A: SQL-like (JPQL/HQL)

SELECT name, email FROM contacts
  LEFT JOIN companies ON contacts.company_id = companies.id
  WHERE status = 'active' AND (city = 'Jakarta' OR city = 'Bandung')
  ORDER BY name ASC
  LIMIT 10
  WITH company, tags

Style B: Simplified DSL

contacts[status='active', city IN ('Jakarta','Bandung')] ORDER BY name WITH company LIMIT 10

Style C: Dot-notation

contacts.where(status.eq('active')).where(city.in('Jakarta','Bandung')).orderBy('name').with('company').limit(10)

Using OQL in JSON Definitions

Process step:

{
  "type": "query",
  "model": "contacts",
  "oql": "contacts[status='active'] ORDER BY name WITH company",
  "into": "active_contacts"
}

Model process call:

{
  "process": "models.contacts.FindAll",
  "args": {
    "oql": "SELECT * FROM contacts WHERE status = 'active' WITH company",
    "page": 1,
    "page_size": 20
  }
}

API query parameter:

GET /api/contacts?oql=contacts[status='active']&page=1&page_size=20

JSON DSL

Full query as JSON:

{
  "wheres": [
    {"field": "status", "op": "=", "value": "active"}
  ],
  "where_groups": [
    {
      "connector": "OR",
      "conditions": [
        {"field": "city", "op": "=", "value": "Jakarta"},
        {"field": "city", "op": "=", "value": "Bandung"}
      ]
    }
  ],
  "joins": [
    {"type": "LEFT", "table": "companies", "local_key": "contacts.company_id", "foreign_key": "companies.id"}
  ],
  "select": ["name", "email"],
  "order": [{"field": "name", "direction": "asc"}],
  "group_by": ["department"],
  "having": [{"aggregate": "COUNT", "field": "*", "op": ">", "value": 5}],
  "distinct": true,
  "with": ["company", {"relation": "orders", "conditions": [{"field": "status", "op": "=", "value": "active"}]}],
  "limit": 10,
  "offset": 20,
  "lock": "for_update",
  "soft_delete_scope": "with_trashed"
}

Supported Operators

Operator	SQL	MongoDB
`=`	`field = ?`	`{field: value}`
`!=`	`field != ?`	`{field: {$ne: value}}`
`>`	`field > ?`	`{field: {$gt: value}}`
`<`	`field < ?`	`{field: {$lt: value}}`
`>=`	`field >= ?`	`{field: {$gte: value}}`
`<=`	`field <= ?`	`{field: {$lte: value}}`
`like`	`field LIKE ?`	`{field: {$regex: pattern}}`
`not_like`	`field NOT LIKE ?`	`{field: {$not: {$regex}}}`
`in`	`field IN (?)`	`{field: {$in: [...]}}`
`not_in`	`field NOT IN (?)`	`{field: {$nin: [...]}}`
`between`	`field BETWEEN ? AND ?`	`{field: {$gte: a, $lte: b}}`
`not_between`	`field NOT BETWEEN ? AND ?`	`{$or: [{$lt: a}, {$gt: b}]}`
`is_null`	`field IS NULL`	`{field: {$eq: null}}`
`is_not_null`	`field IS NOT NULL`	`{field: {$ne: null}}`
`column:op`	`field1 op field2`	N/A (SQL only)

SQL/MongoDB Parity

Both drivers support:

All operators above
OR/AND/NOT condition groups
Ordering and pagination
Count, Sum, Avg, Min, Max
Pluck, Exists
Grouped aggregates
Soft delete scopes
Transactions
Increment/Decrement
Chunk processing

SQL-only features:

JOINs (MongoDB uses $lookup in Aggregate pipeline)
HAVING
DISTINCT
Subqueries
UNION
Raw SQL
Locking (FOR UPDATE/FOR SHARE)
Column comparison (whereColumn)

Navigation Menu

Repository navigation

query-builder.md

query-builder.md

Query Builder & OQL

Overview

Query Builder (Go API)

Basic WHERE

OR Conditions

Grouped Conditions

NOT

JOINs

Aggregates

Eager Loading (WITH / Preload)

UNION

Subqueries

Locking

Soft Delete Scopes

Scopes (Reusable)

Raw Expressions

Repository Methods

Model Process Built-in Functions

Dynamic Finders

Patterns

Operator Suffixes

OrderBy

Usage in JSON

Module-Qualified Model Names

Table Name Resolution

OQL (Object Query Language)

Style A: SQL-like (JPQL/HQL)

Style B: Simplified DSL

Style C: Dot-notation

Using OQL in JSON Definitions

JSON DSL

Supported Operators

SQL/MongoDB Parity

FilesExpand file tree

query-builder.md

Latest commit

History

query-builder.md

File metadata and controls

Query Builder & OQL

Overview

Query Builder (Go API)

Basic WHERE

OR Conditions

Grouped Conditions

NOT

JOINs

Aggregates

Eager Loading (WITH / Preload)

UNION

Subqueries

Locking

Soft Delete Scopes

Scopes (Reusable)

Raw Expressions

Repository Methods

Model Process Built-in Functions

Dynamic Finders

Patterns

Operator Suffixes

OrderBy

Usage in JSON

Module-Qualified Model Names

Table Name Resolution

OQL (Object Query Language)

Style A: SQL-like (JPQL/HQL)

Style B: Simplified DSL

Style C: Dot-notation

Using OQL in JSON Definitions

JSON DSL

Supported Operators

SQL/MongoDB Parity