README

Performance-first Query Builder & Micro-ORM for the MonkeysLegion framework.

Architecture

QueryBuilder (Fluent API)
  → typed Clause VOs (zero string work)
    → QueryCompiler (stateless, cached)
      → GrammarInterface (MySQL/MariaDB, PostgreSQL, SQLite)
        → ConnectionManagerInterface (read/write routing)

EntityRepository (single class)
  → IdentityMap (same row → same object)
    → UnitOfWork (batched writes)
      → EntityHydrator (cached reflection)

Key Features

Installation

composer require monkeyscloud/monkeyslegion-query:2.x-dev

Quick Start

Query Builder

use MonkeysLegion\Query\Query\QueryBuilder;

// Inject ConnectionManagerInterface via DI
$qb = new QueryBuilder($connectionManager);

// SELECT
$users = $qb->from('users')
    ->select(['id', 'name', 'email'])
    ->where('status', '=', 'active')
    ->where('age', '>', 18)
    ->orderByDesc('created_at')
    ->limit(25)
    ->get();

// First row
$user = $qb->from('users')
    ->where('email', '=', 'alice@example.com')
    ->first();

// Aggregates
$count = $qb->from('orders')->where('status', '=', 'pending')->count();
$total = $qb->from('orders')->sum('amount');

// INSERT
$id = $qb->from('users')->insert([
    'name'  => 'Alice',
    'email' => 'alice@example.com',
]);

// UPDATE
$qb->from('users')
    ->where('id', '=', $id)
    ->update(['status' => 'verified']);

// DELETE
$qb->from('sessions')
    ->where('expired_at', '<', date('Y-m-d'))
    ->delete();

Joins

// Simple join
$qb->from('users', 'u')
    ->leftJoinOn('orders', 'u.id', '=', 'orders.user_id', 'o')
    ->select(['u.name', 'o.total'])
    ->get();

// Complex join with callback
$qb->from('users', 'u')
    ->join('orders', fn($j) => $j
        ->on('u.id', '=', 'o.user_id')
        ->where('o.status', '=', 'completed'),
        alias: 'o',
    )
    ->get();

Where Clauses

$qb->from('users')
    ->where('status', '=', 'active')       // standard
    ->orWhere('role', '=', 'admin')         // OR
    ->whereIn('id', [1, 2, 3])             // IN
    ->whereNotIn('status', ['banned'])     // NOT IN
    ->whereBetween('age', 18, 65)          // BETWEEN
    ->whereNull('deleted_at')              // IS NULL
    ->whereNotNull('email')                // IS NOT NULL
    ->get();

Repository

use MonkeysLegion\Query\Repository\EntityRepository;

class UserRepository extends EntityRepository
{
    protected string $table = 'users';
    protected string $entityClass = User::class;
}

// Find
$user = $repo->find(1);
$user = $repo->findOrFail(1);

// Batch loading (single WHERE IN query)
$users = $repo->findByIds([1, 2, 3]);

// Criteria-based
$admins = $repo->findBy(['role' => 'admin'], ['name' => 'ASC'], limit: 10);
$alice  = $repo->findOneBy(['email' => 'alice@example.com']);

// Unit of Work
$user = new User();
$user->name = 'Bob';
$repo->persist($user);
$repo->flush();  // INSERT in transaction

// Cursor pagination (constant memory)
$page = $repo->cursorPaginate(cursor: null, perPage: 25);
// $page = ['data' => [...], 'nextCursor' => 26, 'hasMore' => true]

Common Table Expressions

use MonkeysLegion\Query\Query\CteBuilder;

$cte = new CteBuilder();
$cte->add('active_users', 'SELECT * FROM users WHERE status = ?', ['active']);

// Recursive CTE (tree structures)
$cte->add(
    name: 'category_tree',
    sql: 'SELECT id, parent_id FROM categories WHERE id = ? '
       . 'UNION ALL '
       . 'SELECT c.id, c.parent_id FROM categories c '
       . 'JOIN category_tree ct ON c.parent_id = ct.id',
    bindings: [$rootId],
    recursive: true,
);

Vector Search

use MonkeysLegion\Query\Query\VectorSearch;

// Nearest neighbors using pgvector (PostgreSQL)
$expr = VectorSearch::distance('embedding', $queryVector, DatabaseDriver::PostgreSQL);
// Produces: embedding <-> '[1.0,2.0,3.0]'

// Cosine similarity
$expr = VectorSearch::distance('embedding', $vector, DatabaseDriver::PostgreSQL, 'cosine');
// Produces: embedding <=> '[1.0,2.0,3.0]'

Performance

Database Compatibility

Requirements

• PHP 8.4+
• monkeyscloud/monkeyslegion-database v2
• monkeyscloud/monkeyslegion-entity v1+
• PDO extension

License

MIT © MonkeysCloud