Cockpit Documentation

ESQL provides seamless JSON integration for complex data structures:

Automatic Encoding (INSERT/UPDATE)

When passing arrays or objects in the data key of an insert() or update() call, ESQL automatically converts them to JSON strings for storage.

Automatic Decoding (SELECT)

When fetching data, ESQL automatically detects and decodes JSON strings back into PHP arrays or objects. You can control the decoding behavior using the jsonDecodeAssoc option in your query:

• jsonDecodeAssoc => true (default): Decodes JSON into associative arrays.
• jsonDecodeAssoc => false: Decodes JSON into stdClass objects.

// Example of automatic encoding during insert
$db->insert([
    'table' => 'users',
    'data' => [
        'name' => 'John',
        'settings' => ['theme' => 'dark'] // Auto-encoded to JSON
    ]
]);

// Example of automatic decoding during select
$user = $db->selectOne('users', [
    'conditions' => ['id' => 1],
    'jsonDecodeAssoc' => false // Decodes JSON as stdClass objects
]);

IndexHybrid is a unified search abstraction layer that provides a consistent interface across multiple search backends. It allows you to switch between backends (like IndexLite or Meilisearch) without changing your application code by providing a unified API for indexing, searching, and faceting.

Supported Backends

Core Workflow

1. Initialize the Manager: Create a Manager instance using a backend-specific protocol.
2. Create an Index: Define an index with specific searchable fields.
3. Add Documents: Populate the index with data.
4. Search: Use the unified search() method with various options.

use IndexHybrid\Manager;

// Initialize
$manager = new Manager('indexlite://storage/search.db');

// Create index
$manager->createIndex('products', ['title', 'description', 'category', 'price']);

// Get index and add data
$index = $manager->index('products');
$index->addDocument('prod1', [
    'title' => 'iPhone 15 Pro',
    'description' => 'Latest Apple smartphone',
    'category' => 'Electronics',
    'price' => 999
]);

// Search
$results = $index->search('phone', ['fuzzy' => true]);

IndexLite provides several fuzzy matching algorithms to handle typos and variations. You can enable fuzzy search by setting 'fuzzy' => true in the search options and optionally specifying an algorithm via 'fuzzy_algorithm'.

Available Algorithms:

• fts5: Standard SQLite FTS5 (default)
• levenshtein: Best for spelling errors
• jaro_winkler: Best for names and people
• trigram: Best for partial matches
• soundex: Best for pronunciation-based matching
• hybrid: Combined scoring for best overall results

Fuzzy Options:

• fuzzy_algorithm (string): The algorithm to use.
• fuzzy_threshold (int): The distance threshold (default: 2).
• fuzzy_min_score (int): Minimum score for the hybrid algorithm.

// Basic fuzzy search (uses hybrid algorithm)
$results = $index->search('iphon', ['fuzzy' => true]);

// Algorithm-specific search with threshold
$results = $index->search('macbok', [
    'fuzzy' => true,
    'fuzzy_algorithm' => 'levenshtein',
    'fuzzy_threshold' => 2
]);

// Hybrid algorithm with custom scoring
$results = $index->search('searh', [
    'fuzzy' => true,
    'fuzzy_algorithm' => 'hybrid',
    'fuzzy_min_score' => 70,
    'fuzzy_threshold' => 2
]);

ESQL features automatic JSON handling for complex data types.

Automatic Encoding (INSERT/UPDATE)

When passing an array as a value in insert() or update() calls, ESQL automatically encodes it into a JSON string for storage.

Automatic Decoding (SELECT)

When retrieving data, ESQL automatically decodes JSON columns back into PHP arrays.

Controlling JSON Behavior

• Global Configuration: You can disable automatic encoding or decoding when instantiating the Client using encodeJson and decodeJson options.
• Per-Query Control: Use the jsonDecodeAssoc option in select methods to determine if JSON should be decoded as associative arrays (default) or stdClass objects.

// Decode JSON as stdClass objects instead of associative arrays
$user = $db->selectOne('users', [
    'conditions' => ['id' => 1],
    'jsonDecodeAssoc' => false
]);

// Insert user with complex preferences (auto-encoded to JSON)
$db->insert([
    'table' => 'users',
    'data' => [
        'name' => 'John',
        'preferences' => [
            'ui' => ['theme' => 'dark', 'language' => 'en'],
            'notifications' => ['email' => ['newsletters' => true]]
        ]
    ]
]);

// Retrieve and work with the data (auto-decoded from JSON)
$user = $db->selectOne('users', ['conditions' => ['name' => 'John']]);
// $user['preferences']['ui']['theme'] === 'dark'

The aggregate() method allows you to process data through a series of pipeline stages.

Common Pipeline Stages

• $match: Filters documents.
• $group: Groups documents by a key, using accumulators like $sum, $avg, $min, $max, $push, etc.
• $sort: Sorts results.
• $project: Reshapes documents (includes/excludes fields).
• $addFields: Adds new fields to documents.
• $unwind: Deconstructs an array field into multiple documents.
• $lookup: Performs left outer joins to other collections.
• $out / $merge: Exports or merges results into a collection.
• $geoNear: (Must be first stage) Performs proximity searches.

$analytics = $collection->aggregate([
    ['$match' => ['status' => 'active']],
    ['$group' => [
        '_id' => '$department',
        'employee_count' => ['$sum' => 1],
        'avg_salary' => ['$avg' => '$salary']
    ]],
    ['$sort' => ['avg_salary' => -1]],
    ['$out' => 'department_analytics']
])->toArray();

To ensure high performance, leverage the automatic Query Optimizer by using top-level field queries and standard comparison operators.

Optimized (Fast):

• Top-level field queries (e.g., ['status' => 'active'])
• Comparison operators: $eq, $ne, $gt, $gte, $lt, $lte
• Set operators: $in, $nin
• Element operators: $exists, $type, $size
• Logical operators: $and, $or
• Array containment (scalar values)

Slower (Falls back to PHP processing):

• Regex patterns ($regex)
• Dot-notation for nested fields (meta.rating)
• Array traversal (users.name)

Best Practices:

1. Use Projections: Limit returned fields using find($criteria, ['field' => 1]).
2. Filter Early: In aggregation pipelines, place $match stages before $group or $sort.
3. Pagination: Use skip() and limit() to handle large datasets.
4. Chunking: For very large datasets, use a while loop with skip() and limit() to process data in batches to manage memory.

// Example of efficient pagination/chunking
$offset = 0;
$limit = 1000;
while (true) {
    $batch = $collection->find($criteria)
        ->skip($offset)
        ->limit($limit)
        ->toArray();
    if (empty($batch)) break;
    // Process batch...
    $offset += $limit;
}