# Building Queries with Scribe

This document is a usage guide focused on **how to write `Scribe` in production code**, ApexEloquent's query builder. For the full API signatures, see [API Reference: Scribe](/apex-stem/docs/apex-eloquent-api-scribe). For an index of the other ApexEloquent topics, see the [ApexEloquent Guide](/apex-stem/docs/apex-eloquent-guide).

## What Scribe Is

`Scribe` is a query builder that assembles SOQL through typed method chains. Starting from `Scribe.of(Account.class)` and stacking `field`, `whereEqual`, `orderBy`, and friends, you end up with a complete SOQL string.

```apex
Scribe accountScribe = Scribe.of(Account.class)
  .field('Id')
  .field('Name')
  .field('Industry')
  .whereEqual('Industry', 'Technology')
  .orderBy('Name', 'ASC')
  .take(10);
// → SELECT id, name, industry FROM Account WHERE Industry = 'Technology' ORDER BY Name ASC LIMIT 10

List<IEntry> accounts = new Eloquent().get(accountScribe);
```

Three key points:

- **Immutable**: each method returns a new `Scribe` instance. You can branch midway to derive condition-varied queries without altering the original `Scribe`.
- **Query execution is delegated to `IEloquent`**: when you hand a finished `Scribe` to `IEloquent.get(scribe)`, the SOQL gets issued and the data is fetched. This **separation of query construction from execution** is the heart of ApexEloquent — laid out in detail in [Query Delegation Pattern](/apex-stem/docs/query-delegation-pattern).
- **Mockable via DI**: `IEloquent` is an interface, with `Eloquent` (issues real SOQL) in production and `MockEloquent` (no DB; returns the injected `IEntry` list as-is) in tests, swapped via the Layered Constructor Pattern. The Usecase-side code depends on a field of type `IEloquent`, so the calling shape stays identical in production and tests — only tests get to run logic without touching the DB. Details in [Data Access, DML, IEntry, and Mock](/apex-stem/docs/apex-eloquent-data-access).

Field names are passed as **strings** rather than `SObjectField` (e.g. `'Id'` / `'Industry__c'`). This frees the builder from Apex's type system and lets queries be assembled dynamically at runtime.

## Choosing What to SELECT

### Single Field vs Multiple Fields

Add one at a time with `field(String fieldName)`, or hand over many at once via `fields(List<String>)`.

```apex
Scribe scribe = Scribe.of(Opportunity.class)
  .field('Id')
  .field('Name')
  .field('StageName');
// → SELECT id, name, stagename FROM Opportunity
```

`fields(List<String>)` also accepts a pre-declared `List<String>` directly.

```apex
List<String> opportunityFields = new List<String>{
  'Id',
  'Name',
  'StageName',
  'CloseDate',
  'Amount'
};
Scribe scribe = Scribe.of(Opportunity.class)
  .fields(opportunityFields)
  .whereEqual('StageName', 'Prospecting');
// → SELECT id, name, stagename, closedate, amount FROM Opportunity WHERE StageName = 'Prospecting'
```

### Pulling in Parent Fields with parentField

Pass `Scribe.asParent('AccountId').field(...)` to `parentField`, and the parent object's fields land in the SELECT clause.

```apex
Scribe scribe = Scribe.of(Opportunity.class)
  .field('Id')
  .field('Name')
  .parentField(Scribe.asParent('AccountId').field('Name').field('Industry'))
  .whereEqual('StageName', 'Closed Won');
// → SELECT id, name, Account.name, Account.industry FROM Opportunity WHERE StageName = 'Closed Won'
```

Child subqueries (`withChildren`) and many-to-many (`through`) are covered in [Parent Fields, Child Subqueries, and Many-to-Many](/apex-stem/docs/apex-eloquent-relations).

### allFields for Selecting Everything

`allFields()` selects every accessible field on the target object. In production, explicit fields are safer, but it's handy for building test data or for investigation.

## Filtering with WHERE

### Basic WHERE

Equality (`whereEqual` / `whereNotEqual`), comparison (`whereGreaterThan` family / `whereLessThan` family), pattern (`whereLike` / `whereNotLike`), list (`whereIn` / `whereNotIn`), multi-select (`whereIncludes` / `whereExcludes`), and null check (`whereNull` / `whereNotNull`) are all available. For signatures and behavior, see [API Reference: Scribe](/apex-stem/docs/apex-eloquent-api-scribe).

```apex
Scribe scribe = Scribe.of(Opportunity.class)
  .field('Id')
  .whereEqual('StageName', 'Prospecting')
  .whereGreaterThan('Amount', 1000)
  .whereIn('OwnerId', ownerIds);  // Set<Id> can be passed as-is
// → SELECT id FROM Opportunity WHERE StageName = 'Prospecting' AND Amount > 1000 AND OwnerId IN (...)
```

Successive `whereXxx` calls are joined by **AND** by default.

### Mixing AND and OR

To slip in an OR, insert `orCondition()` **before the next where**.

```apex
Scribe scribe = Scribe.of(Account.class)
  .field('Id')
  .whereEqual('Name', 'A')
  .orCondition()
  .whereEqual('Name', 'B')
  .orCondition()
  .whereEqual('Name', 'C');
// → SELECT id FROM Account WHERE Name = 'A' OR Name = 'B' OR Name = 'C'
```

**Once you switch to OR, every subsequent where must also be joined by OR**. You can't switch back to AND midway. When you want to mix OR and AND, **put the AND conditions first and the OR conditions at the end**, or use `whereGroup` + `Scribe.asGroup()` to wrap one side in parentheses.

```apex
Scribe scribe = Scribe.of(Account.class)
  .field('Id')
  .whereGroup(
    Scribe.asGroup()
      .whereEqual('Industry', 'Tech')
      .whereEqual('Name', 'X')
  )
  .orCondition()
  .whereEqual('BillingCity', 'Tokyo');
// → SELECT id FROM Account WHERE (Industry = 'Tech' AND Name = 'X') OR BillingCity = 'Tokyo'
```

Note: when `whereIn` receives an **empty list**, the SOQL converts it to `Id = null` (a condition guaranteed to be false). This is by design — "if the filter target has 0 items, the result is also 0" — so you don't need an `isEmpty()` check on the caller side.

⚠️ `whereNotIn` goes the other way: given an empty collection it **drops the condition entirely** (no filtering — the all-rows side). Writing `whereIn` while meaning "don't filter when empty" gives you zero rows, so when you want to state which side you mean, use `ignoreWhen` (below).

### Subquery IN

The second argument of `whereIn` can take **another `Scribe`**. The two-step SOQL pattern of "first fetch IDs, then use them in the next query" collapses into one.

```apex
// Take only opportunities tied to "accounts the current user follows"
Scribe followedAccountIds = Scribe.of(AccountShare.class)
  .field('AccountId')
  .whereEqual('UserOrGroupId', UserInfo.getUserId());

Scribe oppScribe = Scribe.of(Opportunity.class)
  .field('Id')
  .field('Name')
  .whereIn('AccountId', followedAccountIds);
// → SELECT id, name FROM Opportunity WHERE AccountId IN (SELECT accountid FROM AccountShare WHERE UserOrGroupId = '...')
```

This is essentially SOQL's `IN (SELECT ...)` syntax made native. `whereNotIn` also accepts a `Scribe` the same way.

### whereLike Auto-Escapes Against SQL Injection

Single quotes (`'`) inside the `pattern` argument to `whereLike(field, pattern)` are auto-escaped. You can pass user input through and not introduce a SOQL injection.

```apex
String userInput = "O'Brien";  // a suspicious-looking input
Scribe scribe = Scribe.of(Contact.class)
  .field('Id')
  .whereLike('LastName', '%' + userInput + '%');
// → ...WHERE LastName LIKE '%O\'Brien%'
```

## Sorting, LIMIT, FOR UPDATE

`orderBy` / `take` / `offset` / `forUpdate` handle sorting, limits, and row locking.

```apex
Scribe scribe = Scribe.of(Opportunity.class)
  .field('Id')
  .field('CloseDate')
  .whereEqual('StageName', 'Prospecting')
  .orderBy('CloseDate', 'DESC')
  .take(20);
// → SELECT id, closedate FROM Opportunity WHERE StageName = 'Prospecting' ORDER BY CloseDate DESC LIMIT 20
```

Combining `forUpdate` with `orderBy` / `offset` throws an exception; `offset` is capped at 2000. SOQL's constraints get mirrored as build-time checks. Details in [API Reference: Scribe](/apex-stem/docs/apex-eloquent-api-scribe).

## Aggregate Queries

For cases like "aggregate child-record counts per parent Id", use aggregate queries — they keep Apex SOQL row counts and heap usage in check.

```apex
Scribe eventScribe = Scribe.of(Event.class)
  .field('WhatId')               // GROUP BY fields must also appear in SELECT
  .count('Id', 'eventCount')     // COUNT with alias
  .whereIn('WhatId', opportunityIds)
  .groupByField('WhatId');
// → SELECT whatid, COUNT(Id) eventCount FROM Event WHERE WhatId IN (...) GROUP BY WhatId

List<IEntry> aggregateEntries = new Eloquent().get(eventScribe);

for (IEntry aggregateEntry : aggregateEntries) {
  Id whatId = (Id) aggregateEntry.get('WhatId');
  Integer cnt = ((Decimal) aggregateEntry.get('eventCount')).intValue();
}
```

The six aggregate functions are `count` / `countDistinct` / `sum` / `average` / `max` / `min`. GROUP BY uses `groupByField` / `groupByFields` / `groupByParent`; HAVING is assembled with `havingCondition(Scribe.asHaving()...)`. For signatures, see [API Reference: Scribe](/apex-stem/docs/apex-eloquent-api-scribe).

**Aliases are required**. Each aggregate function takes `count(field, alias)` — the `alias` cannot be omitted — and you retrieve the value via `aggregateEntry.get('alias')`. Salesforce's standard `AggregateResult` defaults to field names like `expr0` / `expr1` / ... (in declaration order), which is a classic beginner trap; ApexEloquent enforces aliases at the method signature level to dodge that pitfall.

### Aggregating and Grouping by Parent Fields

When you want to aggregate or GROUP BY a **parent object's field**, you **cannot write `field('Account.Industry')` directly**. Everything parent-related has to go through `Scribe.asParent(...)`.

Example: aggregate per Product × Opportunity — total line-item sum per product, plus the maximum amount on the opportunity each line item belongs to.

```apex
Scribe scribe = Scribe.of(OpportunityLineItem.class)
  .field('Product2Id')
  .sum('TotalPrice', 'totalPrice')
  .parentField(
    Scribe.asParent('OpportunityId')
      .field('Id')
      .max('Amount', 'maxAmount')
  )
  .groupByField('Product2Id')
  .groupByParent(
    Scribe.asParent('OpportunityId').groupByField('Id')
  );
// → SELECT product2id, SUM(TotalPrice) totalPrice, Opportunity.id, MAX(Opportunity.Amount) maxAmount FROM OpportunityLineItem GROUP BY Product2Id, Opportunity.Id
```

Points:
- Parent SELECT fields and aggregate functions are bundled inside `parentField(Scribe.asParent('OpportunityId').field(...).max(...))`
- GROUP BY on a parent field is expressed as `groupByParent(Scribe.asParent('OpportunityId').groupByField('Id'))`
- A HAVING clause referring to a parent-derived alias (`maxAmount`) uses the same alias: `Scribe.asHaving().whereGreaterThan('maxAmount', 1000)`

### Notes on Aggregate Queries

- **GROUP BY fields must also appear in SELECT** (don't forget `field('Product2Id')`)
- **Aggregate results come back as `Decimal`**. To put them into an `Integer`, cast like `((Decimal) aggregateEntry.get('alias')).intValue()`
- The retrieval API stays as the normal `get(scribe)`. **Whether the query is aggregate or normal is auto-detected by the framework**
- Reusing the same alias across multiple aggregate functions throws (don't duplicate `total` between `sum('A','total')` and `max('B','total')`)
- Combining child subqueries (`withChildren`) with aggregate functions is forbidden by SOQL constraints

## Building Queries Dynamically

Queries like search filters — "only add the condition when there is input" — come out as **a single chain with no branching**, thanks to `ignoreWhen`.

```apex
Scribe scribe = Scribe.of(Opportunity.class)
  .field('Id')
  .field('Name')
  .whereEqual('Industry', industry).ignoreWhen(industry == null)
  .whereIn('StageName', stages).ignoreWhen(stages.isEmpty())
  .whereGreaterThan('CloseDate', closeAfter).ignoreWhen(closeAfter == null);

List<IEntry> opps = this.fetchEloquent.get(scribe);
// When all three are provided:
// → SELECT id, name FROM Opportunity WHERE Industry = 'Technology' AND StageName IN (...) AND CloseDate > 2026-01-01
// When none are:
// → SELECT id, name FROM Opportunity
```

`ignoreWhen(true)` **retracts the immediately preceding condition**. It also sidesteps the trap where an empty `whereIn` collapses to zero rows (see [API Reference: Scribe](/apex-stem/docs/apex-eloquent-api-scribe)).

> ⚠️ **The `null` case needs v3.5.0 or later.** The **12 methods that reject null** — `whereGreaterThan`, `whereIn` and friends — used to throw the moment you chained them, so a following `ignoreWhen` was never reached. From v3.5.0 the error is **deferred to build time**, which lets `ignoreWhen` retract it. The `closeAfter == null` line above is exactly this case.

### Stacking with if

On versions without `ignoreWhen`, you stack conditions with branches. `Scribe` is immutable, so **reassignment** (`scribe = scribe.whereXxx(...)`) is required.

```apex
Scribe scribe = Scribe.of(Opportunity.class)
  .field('Id')
  .field('Name');

if (industry != null) {
  scribe = scribe.whereEqual('Industry', industry);
}
if (stages != null && !stages.isEmpty()) {
  scribe = scribe.whereIn('StageName', stages);
}
```

This still works, but as conditions pile up the *shape of the query* gets scattered across branches and stops being readable. Prefer `ignoreWhen` for new code.

### Deriving queries

Since each method returns a **new `Scribe` instance**, you can derive from a single query without changing the original — for example, building both a count query and a list query against the same set of opportunities.

```apex
Scribe baseScribe = Scribe.of(Opportunity.class)
  .whereEqual('StageName', 'Prospecting')
  .whereGreaterThan('Amount', 1000);

// Just the count
Scribe countScribe = baseScribe.count('Id', 'cnt');
// → SELECT COUNT(Id) cnt FROM Opportunity WHERE StageName = 'Prospecting' AND Amount > 1000

// The list itself
Scribe listScribe = baseScribe
  .field('Id')
  .field('Name')
  .orderBy('CloseDate', 'ASC')
  .take(50);
// → SELECT id, name FROM Opportunity WHERE StageName = 'Prospecting' AND Amount > 1000 ORDER BY CloseDate ASC LIMIT 50
```

`baseScribe` stays unchanged and is reusable as the source for both derivations.

## Read Next

- [Data Access, DML, IEntry, and Mock](/apex-stem/docs/apex-eloquent-data-access): execute the assembled `Scribe` with `IEloquent` and work with `IEntry`
- [Parent Fields, Child Subqueries, and Many-to-Many](/apex-stem/docs/apex-eloquent-relations): queries using relations, and mocking parent-child structures with `MockEntry`
- [ApexEloquent Guide](/apex-stem/docs/apex-eloquent-guide): back to the ApexEloquent Guide index