> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ondb.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Query Builder

> Fluent query API reference

## Creating a Query Builder

```typescript theme={null}
const builder = client.queryBuilder();
```

## Methods

### collection

Set the collection to query.

```typescript theme={null}
builder.collection(name: string)
```

### find

Add complex find conditions using LogicalOperator.

```typescript theme={null}
builder.find(callback: (builder: ConditionBuilder) => LogicalOperator | LogicalOperator[])
```

When an array is returned, it is automatically wrapped in `LogicalOperator.And()`.

### whereField

Quick field condition.

```typescript theme={null}
builder.whereField(field: string)
```

Returns a `WhereClause` with all field condition operators.

### select

Select specific fields using a builder.

```typescript theme={null}
builder.select(callback: (selection: SelectionBuilder) => SelectionBuilder)
```

### selectFields

Quick field selection by name.

```typescript theme={null}
builder.selectFields(fields: string[])
```

### selectAll

Select all fields.

```typescript theme={null}
builder.selectAll()
```

### limit

Set result limit.

```typescript theme={null}
builder.limit(count: number)
```

### offset

Set result offset.

```typescript theme={null}
builder.offset(count: number)
```

### orderBy / sortBy

Set sort order. `sortBy` is the primary method; `orderBy` is an alias.

```typescript theme={null}
builder.orderBy(field: string, direction?: 'ASC' | 'DESC')
builder.sortBy(field: string, direction?: 'asc' | 'desc' | 'ASC' | 'DESC')
```

### take / skip

Aliases for `limit` and `offset`.

```typescript theme={null}
builder.take(count: number)   // Alias for limit()
builder.skip(count: number)   // Alias for offset()
```

### includeHistory

Include all historical versions of records. Default is `false` (returns only latest version).

```typescript theme={null}
builder.includeHistory(include?: boolean)
```

### groupBy

Group results by field. Returns a `GroupByQueryBuilder`.

```typescript theme={null}
builder.groupBy(field: string)
```

### joinOne

One-to-one join. Returns a single object or null.

```typescript theme={null}
builder.joinOne(alias: string, model: string)
```

Returns a `JoinBuilder`.

### joinMany

One-to-many join. Returns an array of objects.

```typescript theme={null}
builder.joinMany(alias: string, model: string)
```

Returns a `JoinBuilder`.

## Response Type

### QueryResponse

All `execute()` calls return a `QueryResponse<T>`:

```typescript theme={null}
interface QueryResponse<T = any> {
  records: T[];   // Array of matching records
  total?: number; // Total number of matching records (before pagination)
  error?: string; // Error message if query partially failed
}
```

When using pagination with `limit()` and `offset()`, `records` contains only the current page while `total` reflects the full count.

## Execution Methods

### execute

Execute query and return results.

```typescript theme={null}
const result = await builder.execute<T>();
// result: QueryResponse<T>
```

### executeUnique

Execute and return the single latest record (sorted by updatedAt/createdAt).

```typescript theme={null}
const record = await builder.executeUnique<T>();
// Returns T | null
```

### executeWithPayment

Execute a query with payment proof for paid reads.

```typescript theme={null}
const result = await builder.executeWithPayment<T>(quoteId, paymentProof, network?);
```

### getQueryRequest

Get the raw query request object without executing.

```typescript theme={null}
const request = builder.getQueryRequest();
```

### buildRawQuery

Inspect the raw query structure (useful for debugging).

```typescript theme={null}
const raw = builder.buildRawQuery();
```

### clone

Clone the query builder for reuse.

```typescript theme={null}
const cloned = builder.clone();
```

### isValid

Check if the query has required components.

```typescript theme={null}
const valid = builder.isValid();
```

## Aggregation Methods

### count

Count matching records.

```typescript theme={null}
const count = await builder.count();
```

### sumBy

Sum a numeric field.

```typescript theme={null}
const sum = await builder.sumBy(field: string);
```

### avgBy

Average a numeric field.

```typescript theme={null}
const avg = await builder.avgBy(field: string);
```

### minBy

Get minimum value.

```typescript theme={null}
const min = await builder.minBy<T>(field: string);
```

### maxBy

Get maximum value.

```typescript theme={null}
const max = await builder.maxBy<T>(field: string);
```

### distinctBy

Get distinct values.

```typescript theme={null}
const values = await builder.distinctBy<T>(field: string);
```

### countDistinct

Count distinct values.

```typescript theme={null}
const count = await builder.countDistinct(field: string);
```

### runAggregate

Run multiple aggregations in a single HTTP round-trip.

```typescript theme={null}
const stats = await builder.runAggregate({
  total:        { '$count': '*' },
  totalLikes:   { '$sum': 'likes' },
  avgLikes:     { '$avg': 'likes' },
  maxLikes:     { '$max': 'likes' },
  minLikes:     { '$min': 'likes' },
  uniqueAuthors: { '$countDistinct': 'author_id' }
});
// Returns: { total: 4, totalLikes: 203, avgLikes: 50.75, maxLikes: 67, minLikes: 38, uniqueAuthors: 2 }
```

## GroupByQueryBuilder

Returned by `.groupBy(field)`. Groups records by the specified field and applies an aggregation to each group. All methods return `Promise<Record<string, T>>` where keys are the distinct group values.

Supports nested field paths (e.g., `'user.country'`).

### count

Count records in each group.

```typescript theme={null}
count(): Promise<Record<string, number>>
```

### sumBy

Sum a numeric field within each group.

```typescript theme={null}
sumBy(field: string): Promise<Record<string, number>>
```

### avgBy

Calculate average of a numeric field within each group.

```typescript theme={null}
avgBy(field: string): Promise<Record<string, number>>
```

### maxBy

Find maximum value of a field within each group.

```typescript theme={null}
maxBy<T = any>(field: string): Promise<Record<string, T>>
```

### minBy

Find minimum value of a field within each group.

```typescript theme={null}
minBy<T = any>(field: string): Promise<Record<string, T>>
```

### run

Run multiple aggregations per group in a single HTTP round-trip.

```typescript theme={null}
run(spec: AggregateSpec): Promise<Record<string, any>[]>
```

```typescript theme={null}
const rows = await builder.groupBy('category').run({
  total:    { '$count': '*' },
  revenue:  { '$sum': 'amount' },
  avgOrder: { '$avg': 'amount' }
});
// Returns: [{ category: 'electronics', total: 12, revenue: 5000, avgOrder: 416.67 }, ...]
```

### Examples

```typescript theme={null}
const salesByCategory = await builder.groupBy('category').sumBy('amount');
// { "electronics": 5000, "clothing": 3000 }

const countByStatus = await builder.groupBy('status').count();
// { "active": 42, "inactive": 8 }

const avgByRegion = await builder.groupBy('user.country').avgBy('orderTotal');
// { "US": 89.50, "UK": 72.30, "DE": 95.10 }

const highestByDept = await builder.groupBy('department').maxBy('salary');
// { "engineering": 250000, "marketing": 180000 }
```

## WhereClause / Field Condition Operators

### Comparison

```typescript theme={null}
.equals(value)
.notEquals(value)
.greaterThan(value)
.greaterThanOrEqual(value)
.lessThan(value)
.lessThanOrEqual(value)
.between(min, max)
```

### String

```typescript theme={null}
.contains(value)
.startsWith(value)
.endsWith(value)
.regExpMatches(pattern)
.includesCaseInsensitive(value)
.startsWithCaseInsensitive(value)
.endsWithCaseInsensitive(value)
```

### Array / Set

```typescript theme={null}
.in(values)
.notIn(values)
```

### Boolean

```typescript theme={null}
.isTrue()
.isFalse()
```

### Existence

```typescript theme={null}
.isNull()
.isNotNull()
.exists()
.notExists()
```

### Network / Security

```typescript theme={null}
.isLocalIp()
.isExternalIp()
.inCountry(countryCode)
.cidr(cidrRange)          // CIDR range check (string or string[])
```

### Special

```typescript theme={null}
.b64(value)              // Base64 matching
.inDataset(values)       // Case-sensitive membership check (string[])
.keywords(keywords)      // Case-insensitive substring match against multiple keywords (string[])
```

### Date Filtering

There are no date-specific operators. Use comparison operators on ISO 8601 date strings:

```typescript theme={null}
// Records created after a specific date
builder.whereField('createdAt').greaterThanOrEqual('2024-10-01T00:00:00Z')

// Records within a date range (use find() for multiple conditions on the same field)
builder.find(b => [
  b.field('createdAt').greaterThanOrEqual('2024-10-01'),
  b.field('createdAt').lessThanOrEqual('2024-12-31'),
])

// Records created this month
const startOfMonth = new Date(new Date().getFullYear(), new Date().getMonth(), 1).toISOString();
builder.whereField('createdAt').greaterThanOrEqual(startOfMonth)
```

## JoinBuilder

### onField

Set join condition using a field. Returns a `JoinWhereClause`.

```typescript theme={null}
joinBuilder.onField(field: string)
```

JoinWhereClause operators: `.equals(value)`, `.in(values)`, `.greaterThan(value)`, `.lessThan(value)`, `.isNull()`, `.isNotNull()`

Use `'$data.fieldname'` to reference parent fields in join conditions.

### on

Add complex filter conditions for the joined collection.

```typescript theme={null}
joinBuilder.on(callback: (builder: ConditionBuilder) => LogicalOperator | LogicalOperator[])
```

### selectFields

Select fields from joined collection.

```typescript theme={null}
joinBuilder.selectFields(fields: string[])
```

### selectAll

Select all fields from joined collection.

```typescript theme={null}
joinBuilder.selectAll()
```

### Nested JOINs

JoinBuilder supports nested joins before calling `.build()`.

```typescript theme={null}
builder
  .joinMany('tweets', 'tweets')
    .onField('address').equals('$data.address')
    .selectAll()
    .joinOne('profile', 'profiles')
      .onField('user_id').equals('$data.author')
      .selectFields(['bio', 'avatar'])
      .build()
    .build()
  .execute();
```

### build

Finalize the join and return to the parent builder.

```typescript theme={null}
joinBuilder.build()
```

## LogicalOperator

```typescript theme={null}
import { LogicalOperator } from '@ondb/sdk';

LogicalOperator.And([...conditions])
LogicalOperator.Or([...conditions])
LogicalOperator.Not([...conditions])
LogicalOperator.Condition(condition)

// Chaining methods
condition.and(...moreConditions)
condition.or(...moreConditions)
condition.not(...moreConditions)
```

## SelectionBuilder

```typescript theme={null}
import { SelectionBuilder } from '@ondb/sdk';

new SelectionBuilder()
  .field('name')
  .fields(['id', 'email'])
  .nested('profile', nested => nested.field('bio'))
  .build();

SelectionBuilder.all();  // Select all
```