Query Builder

Introduction

JCC Express MVC provides a fluent query builder through DB.table(...) and DB.query() (jcc-express-mvc/lib/Jcc-eloquent/lib/Builder.ts).

Use it for composable SQL queries without writing full raw SQL for every operation.

Starting queries

Select columns:

Read methods

Where clauses

Other common filters:

• whereNot(...), orWhereNot(...)
• whereNotNull(...)
• whereIn(...)
• whereBetween(...), whereNotBetween(...)
• whereDate(...), whereMonth(...), whereDay(...), whereYear(...), whereTime(...)
• whereLike(...), orWhereLike(...)
• whereRaw(...), orWhereRaw(...)
• whereColumn(...), orWhereColumn(...), whereNotColumn(...)
• whereJson(...), orWhereJson(...)
• whereExists(...), orWhereExists(...)

Ordering, grouping, and having

Also available:

• latest(column?), older(column?)
• orderByRaw(...)
• groupByRaw(...)
• havingIn(...), havingNotIn(...)
• havingNull(...), havingNotNull(...)
• havingBetween(...), havingNotBetween(...)
• havingRaw(...)

Joins

Join variants:

• innerJoin(...)
• leftJoin(...)
• rightJoin(...)
• leftOuterJoin(...)
• rightOuterJoin(...)
• fullOuterJoin(...)
• joinRaw(...)
• subquery joins: joinSub(...), leftJoinSub(...), rightJoinSub(...), crossJoinSub(...)

Write methods

Counters and JSON updates:

Aggregates and pagination

Pagination returns a page payload (data + metadata) from builder internals.

Raw SQL and transactions

Utility methods

• clone() -> duplicate a query
• toSQL() / toSqlCode() -> inspect generated SQL
• offset(...), limit(...)
• distinct(...)
• pluck(...)
• union(...), unionAll(...)
• truncate()

Summary

• Use DB.table(...) for most query-builder workflows.
• Chain filters/joins/groups, then finalize with get, first, insert, update, delete, etc.
• Use runQuery / raw for advanced SQL and DB.transaction(...) for atomic operations.