🔧 Builder

The Builder provides a high-level, entity-oriented way to perform queries on your data models. It is built on top of the lower-level QueryBuilder and integrates deeply with Hector ORM entity management.

Accessing the builder

You can access the builder using the static Entity::query() method, or instantiate it directly:

use Hector\Orm\Query\Builder;

$builder = MyEntity::query();
// or
$builder = new Builder(MyEntity::class);

The builder internally uses hectororm/query: see QueryBuilder documentation. You can also use QueryBuilder independently of the ORM: see hectororm/query on GitHub or Packagist.

Finding entities

The builder provides several methods to retrieve entities. Here is a quick overview:

Find by primary key

$entity = MyEntity::find(1);

Returns the entity or null if not found.

Find all

Returns a collection of entities matching the given primary key(s):

$collection = MyEntity::findAll(1);
$collection = MyEntity::findAll(1, 2, 3);

Find or fail

Throws NotFoundException if not found:

use Hector\Orm\Exception\NotFoundException;

try {
    $entity = MyEntity::findOrFail(1);
} catch (NotFoundException $e) {
    // Handle exception
}

Find or new

Returns existing entity or creates a new one with default values:

$entity = MyEntity::findOrNew(1, ['foo' => 'value']);

Get by offset (non-PK access)

These methods retrieve entities by their position in the result set (zero-based offset), not by primary key. Useful when you need the Nth result of a query.

Get / get or fail / get or new

// Get first result (offset 0)
$entity = MyEntity::query()->get();

// Get third result (offset 2)
$entity = MyEntity::query()->where('active', true)->get(2);

// Throw NotFoundException if no result at offset
$entity = MyEntity::query()->getOrFail(0);

// Return new entity with default values if no result at offset
$entity = MyEntity::query()->getOrNew(0, ['status' => 'draft']);

Retrieving all entities

$collection = MyEntity::all();

Also available via the builder:

$collection = MyEntity::query()->all();

Chunking and yielding

Use chunk() for memory-friendly batch processing:

MyEntity::query()->chunk(100, function (Collection $entities) {
    foreach ($entities as $entity) {
        // Process each entity
    }
});

With lazy mode disabled (eager fetch per chunk):

MyEntity::query()->chunk(100, function (Collection $collection) {
    // Each chunk is fetched eagerly from DB
}, lazy: false);

Use yield() to iterate using a lazy generator. Entities are hydrated one by one as you iterate, minimizing memory usage:

foreach (MyEntity::query()->yield() as $entity) {
    // Each entity is loaded on-demand
}

Counting

$count = MyEntity::query()->count();

This will ignore any limit() that was previously applied.

Limiting and offsetting results

MyEntity::query()->limit(10)->offset(5)->all();

Ordering results

MyEntity::query()->orderBy('created_at', 'DESC')->all();
MyEntity::query()->orderBy('name')->all(); // ASC by default

Conditions

You can filter entities using a fluent API similar to the QueryBuilder.

Where / Having

MyEntity::query()->where('column', 'value')->orWhere('column', '<', 10);

Where In / Not In

MyEntity::query()->whereIn('column', ['foo', 'bar']);
MyEntity::query()->whereNotIn('column', ['foo', 'bar']);

Between / Not Between

MyEntity::query()->whereBetween('column', 1, 10);
MyEntity::query()->whereNotBetween('column', 1, 10);

Greater / Less

MyEntity::query()->whereGreaterThan('column', 5);
MyEntity::query()->whereLessThan('column', 100);

Exists / Not Exists

MyEntity::query()->whereExists($subQuery);
MyEntity::query()->whereNotExists($subQuery);

Where equals (smart inference)

MyEntity::query()->whereEquals([
    'status' => 'active',           // WHERE status = 'active'
    'category_id' => [1, 2, 3],     // AND category_id IN (1, 2, 3)
]);

Automatically uses = for scalar values and IN for arrays.

Grouped conditions

Pass a Closure to where() or having() to create grouped (parenthesized) conditions:

use Hector\Query\Statement\Conditions;

MyEntity::query()
    ->where(function (Conditions $conditions): void {
        $conditions->where('status', 'active');
        $conditions->orWhere('role', 'admin');
    })
    ->where('verified', true)
    ->all();
// WHERE ( status = ? OR role = ? ) AND verified = ?

Filtering through relationships

You can filter entities based on columns from related entities by using dot notation in your conditions. The ORM automatically resolves the relationship, generates the required LEFT JOIN and adds DISTINCT to avoid duplicate rows.

Single-level relationship

Use 'relationship.column' to filter through a direct relationship:

// Find all films where the language name is 'French'
Film::query()->where('language.name', 'French')->all();

This generates a LEFT JOIN on the language table and applies the condition on the joined table.

Multi-level relationships

You can chain relationships to filter through deeper levels using 'relation1.relation2.column':

// Find all staff members whose address is in a specific city
Staff::query()->where('address.city.city', 'Paris')->all();

This generates two LEFT JOIN clauses (one for address, one for city) and applies the condition on the final joined table.

Combining multiple conditions on relationships

All condition methods work with relationship dot notation: orWhere, whereIn, whereBetween, etc.

Staff::query()
    ->where('address.city.city', 'Paris')
    ->where('address.city.country_id', 1)
    ->all();

You can also use grouped conditions with closures:

Film::query()->where(function (Conditions $conditions): void {
    $conditions->where('language.name', 'French');
    $conditions->orWhere('language.name', 'Italian');
})->all();

Pagination

The Builder provides a paginate() method that returns paginated entity collections. It integrates with the Pagination component.

Basic usage

use Hector\Pagination\Request\OffsetPaginationRequest;

$request = new OffsetPaginationRequest(page: 2, perPage: 15);

$pagination = User::query()
    ->where('active', true)
    ->orderBy('created_at', 'DESC')
    ->paginate($request);

// Iterate over User entities
foreach ($pagination as $user) {
    echo $user->name;
}

// Pagination metadata
$pagination->getCurrentPage();  // 2
$pagination->hasMore();         // true/false

With total count

$pagination = User::query()
    ->where('active', true)
    ->paginate($request, withTotal: true);

$pagination->getTotal();       // 150
$pagination->getTotalPages();  // 10

Cursor pagination

For large datasets, cursor pagination is more efficient:

use Hector\Pagination\Request\CursorPaginationRequest;

$request = new CursorPaginationRequest(
    perPage: 20,
    position: ['id' => 100],
);

$pagination = Post::query()
    ->orderBy('id')
    ->paginate($request);

foreach ($pagination as $post) {
    // Process Post entities
}

$pagination->getNextPosition();     // ['id' => 120]
$pagination->getPreviousPosition(); // ['id' => 100]

Supported request types

Optimized pagination

When using JOINs (especially *ToMany relationships), a single row per entity can be multiplied into several rows. This causes LIMIT to return fewer entities than expected. Pass optimized: true to fix this:

$pagination = User::query()
    ->where('orders.status', 'paid')       // auto-joins "orders" (1-to-many)
    ->orderBy('created_at', 'DESC')
    ->paginate($request, optimized: true);

Under the hood, the paginator uses a single query with an INNER JOIN on a derived table:

SELECT main.*
FROM entity AS main
INNER JOIN (
    SELECT DISTINCT main.pk FROM entity AS main
    JOIN … WHERE … ORDER BY … LIMIT …
) AS pagination ON (main.pk = pagination.pk)
ORDER BY …

This works with all three pagination types (offset, cursor, range) and is fully compatible with withTotal: true:

$pagination = User::query()
    ->where('orders.status', 'paid')
    ->paginate($request, withTotal: true, optimized: true);

Chunk paginate

Use chunkPaginate() to iterate through all pages automatically. The callback receives two arguments — the page items as an ORM Collection and the PaginationInterface for metadata. Return false to stop early.

Because items are provided as a Collection, you can call load() directly in the callback to eager-load relations for the current page, avoiding N+1 queries:

use Hector\Orm\Collection\Collection;
use Hector\Pagination\PaginationInterface;
use Hector\Pagination\Request\CursorPaginationRequest;

Post::query()
    ->orderBy('id')
    ->chunkPaginate(
        new CursorPaginationRequest(perPage: 100),
        function (Collection $posts, PaginationInterface $pagination) {
            $posts->load(['author', 'tags']);

            foreach ($posts as $post) {
                // $post->author and $post->tags are already loaded
            }
        },
    );

If the builder has a limit() set, chunkPaginate() honors it as a global maximum across all pages — it adjusts the last page’s per-page size so that the total number of processed entities never exceeds the limit. This is consistent with Builder::chunk().

This is more efficient than chunk(lazy: false) for large datasets because each page starts where the previous one left off, avoiding the O(n²) cost of large LIMIT/OFFSET queries.

Like paginate(), chunkPaginate() accepts the optimized parameter. This is important when your query involves *ToMany JOINs: without it, a LIMIT 100 may return fewer than 100 entities because each entity with N related rows produces N result rows. The optimized mode first selects distinct primary keys, then loads the full entities, so the per-page count is always correct.

User::query()
    ->where('orders.status', 'paid')    // auto-joins "orders" (1-to-many)
    ->orderBy('id')
    ->chunkPaginate(
        new CursorPaginationRequest(perPage: 100),
        function (Collection $users, PaginationInterface $pagination) {
            // Each page contains exactly up to 100 User entities,
            // regardless of how many orders each user has.
        },
        optimized: true,
    );

Compatibility with QueryBuilder

All filtering, ordering, joining, and limiting operations are passed to the underlying QueryBuilder, which can be used directly for low-level control.

If you need full SQL flexibility or want to decouple from ORM, refer to the standalone QueryBuilder documentation.