Skip to main content

QQueryBuilder

@quik/database

@quik/database / QQueryBuilder

Class: QQueryBuilder<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:94

Builder class for creating SQL queries based on find options.

This class provides a fluent interface for building complex SQL queries with support for:

  1. Joining related tables based on entity relationships
  2. Building complex where clauses with various operators and conditions
  3. Adding sorting, pagination, and other query modifiers
  4. Converting between entity properties and database column names

Example​

// Basic query with conditions
const query = QQueryBuilder.build(
  { where: { status: 'active' } },
  userEntity
);

// Advanced query with relations and sorting
const query = QQueryBuilder.build(
  {
    where: { status: 'active' },
    relations: ['profile', 'orders'],
    sort: [{ field: 'createdAt', direction: 'desc' }],
    limit: 10
  },
  userEntity,
  knexInstance
);

// Execute the query
const results = await query;

See​

QRepository For higher-level database operations using this builder

Extends​

  • QObject

Type Parameters​

TModel​

TModel extends QModel = QModel

Constructors​

Constructor​

new QQueryBuilder<TModel>(findOptions, entity, queryBuilder?): QQueryBuilder<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:117

Creates a new query builder.

Parameters​

findOptions​

QFindOptions<TModel>

Options for the find operation

entity​

TModel

The entity model being queried

queryBuilder?​

QueryBuilder<TModel>

Optional existing query builder to extend

Returns​

QQueryBuilder<TModel>

Overrides​

QObject.constructor

Accessors​

entity​

Get Signature​

get protected entity(): TModel

Defined in: database/src/repository/QQueryBuilder.ts:149

Gets the entity model being queried.

Returns​

TModel

findOptions​

Get Signature​

get protected findOptions(): QFindOptions<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:156

Gets the find options.

Returns​

QFindOptions<TModel>

logger​

Get Signature​

get protected logger(): IQLogger

Defined in: core/src/QObject.ts:15

The logger getter for the object.

Returns​

IQLogger

Inherited from​

QObject.logger

name​

Get Signature​

get name(): string

Defined in: core/src/QObject.ts:8

Returns​

string

Inherited from​

QObject.name

queryBuilder​

Get Signature​

get protected queryBuilder(): QueryBuilder<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:133

Gets the underlying query builder.

Returns​

QueryBuilder<TModel>

Set Signature​

set protected queryBuilder(value): void

Defined in: database/src/repository/QQueryBuilder.ts:142

Sets the underlying query builder.

Parameters​
value​

QueryBuilder<TModel>

New query builder

Returns​

void

Methods​

applyCountAggregate()​

protected applyCountAggregate(countDefinition, alias): QueryBuilder<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:471

Applies the count aggregate to the current query builder.

Parameters​

countDefinition​

QCountAggregateOption<TModel>

Count configuration

alias​

string

Alias for the aggregate column

Returns​

QueryBuilder<TModel>

buildGroupedCountQuery()​

protected buildGroupedCountQuery(findOptions): QueryBuilder<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:380

Builds a grouped count query and automatically groups by selected fields.

Parameters​

findOptions​

QGroupedCountOptions<TModel>

Grouped count options

Returns​

QueryBuilder<TModel>

buildGroupedSelectExpression()​

protected buildGroupedSelectExpression(selectOption): object

Defined in: database/src/repository/QQueryBuilder.ts:433

Resolves a grouped select definition into SQL select and group-by expressions.

Parameters​

selectOption​

QGroupSelectOption<TModel>

Grouped select definition

Returns​

object

alias​

alias: string

groupBy​

groupBy: string | Raw<any>

select​

select: string | Raw<any>

buildQuery()​

protected buildQuery(): QueryBuilder<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:344

Builds the complete query based on find options.

This method orchestrates the query building process by:

  1. Setting up column mappings between entity properties and database fields
  2. Adding relation joins if specified in find options
  3. Applying where conditions for filtering
  4. Adding sorting directives
  5. Applying pagination (limit/offset) if specified
  6. Handling count queries if requested

The query is built in a specific order to ensure correct SQL generation:

  • First, column mappings are created for the base entity
  • Next, relation joins are added (if specified)
  • Then, where conditions are applied
  • Followed by sorting directives
  • Finally, pagination and count options are applied

Returns​

QueryBuilder<TModel>

Throws​

Error If relation specified doesn't exist on the model

Example​

// Create a query builder with complete options
const builder = new QQueryBuilder(
  {
    where: { isActive: true },
    relations: ['profile'],
    sort: [{ field: 'createdAt', direction: 'desc' }],
    limit: 10,
    offset: 20
  },
  userEntity
);

// Build and execute the query
const query = builder.buildQuery();
const results = await query;

// Count query example
const countBuilder = new QQueryBuilder(
  {
    where: { isActive: true },
    count: true
  },
  userEntity
);

const countResult = await countBuilder.buildQuery();
console.log('Total count:', countResult[0].count);

See​

buildRelation()​

protected buildRelation(relation, model, modelRelations, prefix?, levelRelations?, relations?, select?): void

Defined in: database/src/repository/QQueryBuilder.ts:643

Builds a single relation join.

This method creates SQL join clauses for a specific relation by:

  1. Validating that the relation exists on the model
  2. Resolving the related entity class
  3. Creating appropriate table aliases for the join
  4. Adding columns from the related table to the query
  5. Creating the appropriate join clause based on relation type (one-to-many, many-to-many)
  6. Recursively processing nested relations if specified

Parameters​

relation​

string

Relation name to build

model​

TModel

Parent model

modelRelations​

string[]

Available relations on the model

prefix?​

string

Optional prefix for table aliases

levelRelations?​

string[] = []

Relations at the current level

relations?​

QFindRelations

Optional nested relations

select?​

string[]

Optional nested relation selects

Returns​

void

Throws​

Error If the specified relation doesn't exist on the model

Throws​

Error If the relation entity cannot be resolved

Example​

// Build a single relation join for 'profile' relation
builder.buildRelation(
  'profile',
  userModel,
  ['profile', 'orders'],
  undefined,
  []
);
// This creates a join between users and profiles tables

See​

  • buildRelations For handling multiple relations
  • EntityStore For entity class resolution

buildRelations()​

protected buildRelations(model, relations, prefix?): void

Defined in: database/src/repository/QQueryBuilder.ts:566

Builds relation joins for a model.

This method handles the complex task of joining related tables by:

  1. Identifying the relations defined on the model
  2. Processing both simple string relations and complex nested relation objects
  3. Creating appropriate SQL joins with proper table aliases
  4. Recursively handling nested relations

Relations can be specified in multiple formats:

  • Simple string format: ['profile', 'orders']
  • Nested relation format: [{ 'orders': ['items', 'payments'] }]
  • Mixed format: ['profile', { 'orders': ['items'] }]

Parameters​

model​

TModel

Model to build relations for

relations​

QFindRelations

Relations to include

prefix?​

string

Optional prefix for table aliases

Returns​

void

Throws​

Error If a specified relation doesn't exist on the model

Example​

// Build simple relations for a user model
builder.buildRelations(
  userModel,
  ['profile', 'orders']
);
// This creates joins for profile and orders

// Build nested relations
builder.buildRelations(
  userModel,
  ['profile', { 'orders': ['items', 'payments'] }]
);
// This creates joins for profile, orders, order items, and order payments

// Multiple levels of nesting
builder.buildRelations(
  userModel,
  [{ 'orders': [{ 'items': ['product'] }] }]
);
// This creates joins from user to orders to items to products

See​

buildSortClause()​

protected buildSortClause(): void

Defined in: database/src/repository/QQueryBuilder.ts:1054

Builds sort clauses based on find options.

This method adds ORDER BY clauses to the query by:

  1. Iterating through all sort conditions in the find options
  2. Adding each field and direction to the query builder
  3. Supporting multiple sort fields with different directions

Sort options are specified as an array of objects, each with:

  • field: The field name to sort by
  • direction: Optional sort direction ('asc' or 'desc', defaults to 'asc')

The order of sort conditions in the array determines their precedence in the SQL ORDER BY clause.

Returns​

void

Example​

// Sort by a single field ascending (default)
builder.findOptions = {
  sort: [{ field: 'name' }]
};
builder.buildSortClause();
// Produces: ORDER BY name ASC

// Sort by a single field descending
builder.findOptions = {
  sort: [{ field: 'createdAt', direction: 'desc' }]
};
builder.buildSortClause();
// Produces: ORDER BY createdAt DESC

// Sort by multiple fields with different directions
builder.findOptions = {
  sort: [
    { field: 'status', direction: 'asc' },
    { field: 'createdAt', direction: 'desc' },
    { field: 'name', direction: 'asc' }
  ]
};
builder.buildSortClause();
// Produces: ORDER BY status ASC, createdAt DESC, name ASC

See​

buildWhereClause()​

protected buildWhereClause(): void

Defined in: database/src/repository/QQueryBuilder.ts:781

Builds where clauses based on find options.

This method handles the complexity of SQL where conditions by:

  1. Detecting the type of where condition (array, builder, single condition, or object)
  2. Delegating to the appropriate handler method based on condition type
  3. Applying the conditions to the query builder

The method supports various condition formats:

  • Arrays of conditions (combined with AND)
  • QWhereBuilder instances for complex nested conditions
  • Single condition objects with field, operator, and value
  • Plain objects for simple equality conditions

Returns​

void

Example​

// Simple object where clause (field equality)
builder.findOptions = { where: { status: 'active', role: 'user' } };
builder.buildWhereClause();
// Produces: WHERE status = 'active' AND role = 'user'

// Array of condition objects
builder.findOptions = {
  where: [
    { field: 'status', operator: 'eq', value: 'active' },
    { field: 'createdAt', operator: 'gt', value: '2023-01-01' }
  ]
};
builder.buildWhereClause();
// Produces: WHERE status = 'active' AND createdAt > '2023-01-01'

// Single condition object
builder.findOptions = {
  where: { field: 'email', operator: 'like', value: '%@example.com' }
};
builder.buildWhereClause();
// Produces: WHERE email LIKE '%@example.com'

// Complex where clause with QWhereBuilder
const whereBuilder = QWhereBuilder.New()
  .Equal('status', 'active')
  .Or(
    QWhereBuilder.New()
      .Equal('role', 'admin')
      .Equal('department', 'IT')
  );

builder.findOptions = { where: whereBuilder };
builder.buildWhereClause();
// Produces: WHERE status = 'active' OR (role = 'admin' AND department = 'IT')

See​

convertOperator()​

protected convertOperator(operator): QWhereOperator

Defined in: database/src/repository/QQueryBuilder.ts:514

Converts string-based operators to symbol-based operators.

This utility method translates human-readable operator strings into their SQL symbol equivalents for use in database queries. It handles:

  • Comparison operators like 'lt', 'gt', 'eq'
  • Falling back to the original operator if no conversion exists

The string-based operators provide a more readable alternative in code:

  • 'lt' for less than (<)
  • 'lte' for less than or equal (<=)
  • 'gt' for greater than (>)
  • 'gte' for greater than or equal (>=)
  • 'eq' for equals (=)
  • 'ne' for not equals (<>)

Parameters​

operator​

QWhereComparisonOperatorString

Operator to convert

Returns​

QWhereOperator

Example​

// Convert string operators to symbols
const ltSymbol = builder.convertOperator('lt');  // Returns '<'
const gteSymbol = builder.convertOperator('gte'); // Returns '>='
const eqSymbol = builder.convertOperator('eq');  // Returns '='

// Operator without conversion is returned as-is
const likeOp = builder.convertOperator('like'); // Returns 'like'
const inOp = builder.convertOperator('in');     // Returns 'in'

See​

endSpan()​

protected endSpan(span): void

Defined in: core/src/QObject.ts:35

End a span returned by startSpan.

Parameters​

span​

TelemetrySpan

The span to end.

Returns​

void

Inherited from​

QObject.endSpan

initializeColumns()​

protected initializeColumns(): void

Defined in: database/src/repository/QQueryBuilder.ts:418

Initializes the field-to-column mapping for the base entity.

Returns​

void

normalizeRelationValue()​

protected normalizeRelationValue(value?): object

Defined in: database/src/repository/QQueryBuilder.ts:588

Normalizes a relation declaration into explicit nested relations and selected fields.

Parameters​

value?​

QRelationValue

Relation configuration value

Returns​

object

relations?​

optional relations?: QFindRelations

select?​

optional select?: string[]

recordSpanError()​

protected recordSpanError(span, err): void

Defined in: core/src/QObject.ts:45

Record an exception on an active span.

Parameters​

span​

TelemetrySpan

The span to record the error on.

err​

unknown

The error to record.

Returns​

void

Inherited from​

QObject.recordSpanError

resolveWhereField()​

protected resolveWhereField(field): string

Defined in: database/src/repository/QQueryBuilder.ts:816

Parameters​

field​

string

Returns​

string

setSpanAttribute()​

protected setSpanAttribute(span, key, value): void

Defined in: core/src/QObject.ts:64

Set a single attribute on an active span.

Parameters​

span​

TelemetrySpan

The span to update.

key​

string

Attribute key.

value​

string | number | boolean

Attribute value.

Returns​

void

Inherited from​

QObject.setSpanAttribute

startSpan()​

protected startSpan(spanName, options?): TelemetrySpan

Defined in: core/src/QObject.ts:26

Start a telemetry span for the given operation name. Returns undefined when no telemetry provider is active.

Parameters​

spanName​

string

Name of the span.

options?​

TelemetrySpanOptions

Optional span attributes and kind.

Returns​

TelemetrySpan

Inherited from​

QObject.startSpan

transformWhereBuilder()​

protected transformWhereBuilder(queryBuilder, whereBuilder, clause?): QueryBuilder<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:975

Transforms a where builder into a Knex query clause.

This method handles complex nested where conditions by:

  1. Iterating through all conditions in the where builder
  2. Determining the appropriate logical operator (AND/OR) between conditions
  3. Handling both simple conditions and nested condition groups
  4. Recursively processing nested where builders
  5. Building a complete where clause structure with proper nesting

Parameters​

queryBuilder​

QueryBuilder<TModel>

Query builder to modify

whereBuilder​

QWhereBuilder

Where builder containing conditions to apply

clause?​

"where" | "having"

Returns​

QueryBuilder<TModel>

Example​

// Create a complex where builder with nested conditions
const whereBuilder = new QWhereBuilder()
  .Equal('status', 'active')
  .Or(
    new QWhereBuilder()
      .Equal('role', 'admin')
      .Equal('department', 'IT')
  );

// Transform it into a Knex query
const query = builder.transformWhereBuilder(knexQuery, whereBuilder);
// Produces: WHERE status = 'active' OR (role = 'admin' AND department = 'IT')

See​

transformWhereCondition()​

protected transformWhereCondition(queryBuilder, whereCondition, linker?, clause?): QueryBuilder<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:894

Transforms a where condition into a Knex query clause.

This method converts abstract condition objects into SQL where clauses by:

  1. Determining the appropriate SQL function to call based on the condition type
  2. Handling special operators like 'is null', 'like', 'in', and 'between'
  3. Converting string-based operators to their symbol equivalents
  4. Applying negation if specified in the condition
  5. Properly formatting field names and values for the database

Special operators are handled differently:

  • 'is null': Uses special SQL syntax
  • 'like': Used for pattern matching with % wildcards
  • 'in': Checks if value is in a list of options
  • 'between': Checks if value is within a range

Parameters​

queryBuilder​

QueryBuilder<TModel>

Query builder to modify

whereCondition​

QWhereCondition

Condition to apply

linker?​

"or" | "and"

Optional logical operator to link with previous conditions

clause?​

"where" | "having"

Returns​

QueryBuilder<TModel>

Throws​

Error If an invalid operator is used (will default to '=')

Example​

// Transform a simple equality condition
const newBuilder = builder.transformWhereCondition(
  knexQuery,
  { field: 'status', operator: '=', value: 'active' }
);
// Produces: WHERE status = 'active'

// Transform a string operator condition
const newBuilder = builder.transformWhereCondition(
  knexQuery,
  { field: 'age', operator: 'gte', value: 18 }
);
// Produces: WHERE age >= 18

// Special operator: 'is null'
const newBuilder = builder.transformWhereCondition(
  knexQuery,
  { field: 'deletedAt', operator: 'is null' }
);
// Produces: WHERE deletedAt IS NULL

// Special operator: 'in' with array
const newBuilder = builder.transformWhereCondition(
  knexQuery,
  { field: 'status', operator: 'in', value: ['active', 'pending'] }
);
// Produces: WHERE status IN ('active', 'pending')

// Negated condition with 'and' linker
const newBuilder = builder.transformWhereCondition(
  knexQuery,
  { field: 'role', operator: 'in', value: ['admin', 'moderator'], isNegated: true },
  'and'
);
// Produces: AND role NOT IN ('admin', 'moderator')

See​

  • convertOperator For converting string operators to symbols
  • OPERATOR_NEGATION For handling negated conditions
  • QWhereCondition For the structure of condition objects

applyHaving()​

static applyHaving<TModel>(queryBuilder, havingBuilder, entity): QueryBuilder<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:253

Applies a QWhereBuilder's conditions as a HAVING clause, using the same field resolution and operator handling as applyWhere. Typically used after a GROUP BY to filter on aggregate results.

Type Parameters​

TModel​

TModel extends QModel

Parameters​

queryBuilder​

QueryBuilder<TModel>

Query builder to apply the conditions onto.

havingBuilder​

QWhereBuilder

Conditions to apply.

entity​

TModel

Entity used to resolve unqualified field names; only its entityName is used.

Returns​

QueryBuilder<TModel>

applyWhere()​

static applyWhere<TModel>(queryBuilder, whereBuilder, entity): QueryBuilder<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:236

Applies a QWhereBuilder's conditions directly onto an existing Knex query builder, without any of build()'s other side effects (relation joins, soft-delete filtering, forced column selection).

Field names should be fully qualified as table.column (e.g. ProjectTeam.projectId) when the query spans more than one table, such as with a manually joined query — unqualified field names fall back to entity's own table. This is the mechanism QJoinQueryBuilder uses to support .where() on arbitrary joins.

Type Parameters​

TModel​

TModel extends QModel

Parameters​

queryBuilder​

QueryBuilder<TModel>

Query builder to apply the conditions onto.

whereBuilder​

QWhereBuilder

Conditions to apply.

entity​

TModel

Entity used to resolve unqualified field names; only its entityName is used.

Returns​

QueryBuilder<TModel>

Example​

const query = QQueryBuilder.applyWhere(
  knex('ProjectTeamMember').join('ProjectTeam', 'ProjectTeam.id', 'ProjectTeamMember.projectTeamId'),
  QWhereBuilder.New().Equal('ProjectTeam.projectId', projectId),
  new ProjectTeamMember()
);

build()​

static build<TModel>(findOptions, entity, queryBuilder?): QueryBuilder<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:206

Static factory method to build a query.

This method simplifies query creation by:

  1. Creating a new query builder instance
  2. Applying all find options (where, relations, sort, etc.)
  3. Returning the fully configured Knex query builder

Type Parameters​

TModel​

TModel extends QModel

Parameters​

findOptions​

QFindOptions<TModel>

Options for filtering, relations, sorting, and pagination

entity​

TModel

The entity model being queried

queryBuilder?​

QueryBuilder<TModel>

Optional existing query builder to extend

Returns​

QueryBuilder<TModel>

Throws​

Error If relation specified doesn't exist on the model

Example​

// Simple query to find active users
const query = QQueryBuilder.build(
  { where: { status: 'active' } },
  new UserModel()
);

// Query with complex where condition using QWhereBuilder
const whereBuilder = QWhereBuilder.New()
  .Equal('status', 'active')
  .And(QWhereBuilder.New().GreaterThan('lastLogin', '2023-01-01'));

const query = QQueryBuilder.build(
  { where: whereBuilder },
  new UserModel()
);

// Query with relations and extending existing builder
const query = QQueryBuilder.build(
  { relations: ['profile', 'orders'] },
  new UserModel(),
  knex('users').where('isDeleted', false)
);

// Execute the query
const users = await query;

See​

buildGroupedCount()​

static buildGroupedCount<TModel>(findOptions, entity, queryBuilder?): QueryBuilder<TModel>

Defined in: database/src/repository/QQueryBuilder.ts:269

Builds a grouped count query with automatic group-by clauses for selected fields.

Type Parameters​

TModel​

TModel extends QModel

Parameters​

findOptions​

QGroupedCountOptions<TModel>

Grouped count options

entity​

TModel

The entity model being queried

queryBuilder?​

QueryBuilder<TModel>

Optional existing query builder to extend

Returns​

QueryBuilder<TModel>