Skip to main content

🍳 Query Cookbook

This reference guide provides production-ready code blocks for standard CRUD operations, complex mathematical evaluations, sorting configurations, and pagination pipelines using the ZTeraDB Query Builder.

📌 Table of Contents​

⚙️ Initial Core Setup​

Every example below assumes an active, pre-configured ZTeraDBConnection lifecycle instance initialized via your global configuration layer:

use ZTeraDB\Config\ZTeraDBConfig;
use ZTeraDB\Connection\ZTeraDBConnection;
use ZTeraDB\Query\ZTeraDBQuery;

// Initialize connection via localized runtime environments
$config = new ZTeraDBConfig(json_decode(getenv('ZTERADB_CONFIG'), true));
$db = new ZTeraDBConnection($config, getenv('ZTERADB_HOST'), (int)getenv('ZTERADB_PORT'));

1️⃣ Mutation Operators (INSERT)​

Insert a Single Record​

Builds structured data blocks mapping parameters explicitly to target database storage engines.

$query = (new ZTeraDBQuery('user'))
    ->insert()
    ->fields([
        'email'    => 'john@example.com',
        'password' => 'hashed_pw',
        'status'   => true
    ]);

$result = $db->run($query);

echo 'Generated Primary Key ID: ' . $result['last_insert_id'] . PHP_EOL;

Equivalent SQL

INSERT INTO "user" (email, password, status)
VALUES ('john@example.com', 'hashed_pw', TRUE);

2️⃣ Retrieval Operators (SELECT)​

Select All Table Records​

$query = (new ZTeraDBQuery('user'))->select();
$users = $db->run($query);

Equivalent SQL

SELECT * FROM "user";

Select with Basic Scalar Filtering​

For simple exact-match lookups, pass your payload parameters directly to the .filter() helper method.

$query = (new ZTeraDBQuery('user'))
    ->select()
    ->filter(['status' => true]);

$users = $db->run($query);

Equivalent SQL

SELECT * FROM "user" WHERE status = TRUE;

Explicit Column Selection​

Reduce wire overhead payloads by picking explicitly specified column arrays.

$query = (new ZTeraDBQuery('user'))
    ->select()
    ->fields(['email' => 1]); // Set field map bit-flags to 1 to inclusion-select

$users = $db->run($query);

Equivalent SQL

SELECT email FROM "user";

Row Window Offsetting (Pagination Boundary)​

$query = (new ZTeraDBQuery('user'))
    ->select()
    ->limit(0, 10); // API Mapping: limit(int $offset, int $count)

$users = $db->run($query);

Equivalent SQL

SELECT * FROM "user" LIMIT 10 OFFSET 0;

3️⃣ Advanced Functional Filters​

For complex conditions that extend beyond standard associative key-value loops, inject pre-compiled filter trees directly into .filterCondition().

Inline Mathematical Validations​

// Compiles structural calculation boundary rules
$condition = ZTGT([
    ZTMUL(['price', 'quantity']),
    500
]);

$query = (new ZTeraDBQuery('product'))
    ->select()
    ->filterCondition($condition);

$rows = $db->run($query);

Equivalent SQL

SELECT * FROM product WHERE (price * quantity) > 500;

Case-Insensitive Fuzzy Text Matching​

// Utilizing case-insensitive string matcher helpers
$condition = ZTICONTAINS('name', 'john');

$query = (new ZTeraDBQuery('user'))
    ->select()
    ->filterCondition($condition);

$rows = $db->run($query);

Equivalent SQL

SELECT * FROM "user" WHERE LOWER(name) LIKE '%john%';

4️⃣ Update & Delete Mutations​

Conditional Record Updates​

$query = (new ZTeraDBQuery('user'))
    ->update()
    ->fields(['status' => false])
    ->filter(['id' => 1]);

$result = $db->run($query);

echo $result['is_updated'] ? 'Update Success' : 'No Changes Made';

Equivalent SQL

UPDATE "user" SET status = FALSE WHERE id = 1;

Target Record Hard Erasures​

$query = (new ZTeraDBQuery('product'))
    ->delete()
    ->filter(['id' => 'PRODUCT_ID']);

$result = $db->run($query);

Equivalent SQL

DELETE FROM product WHERE id = 'PRODUCT_ID';

5️⃣ Relational Join Operations​

Execute structured data links across foreign key references using nested subquery representations via .relatedFields().

$userFilter = (new ZTeraDBQuery('user'))
    ->select()
    ->filter(['status' => true]);

$query = (new ZTeraDBQuery('order'))
    ->select()
    ->relatedFields([
        'user' => $userFilter // Maps target collection bindings implicitly 
    ]);

$rows = $db->run($query);

Equivalent SQL

-- Conceptual Engine Join Mapping
SELECT o.*, u.*
FROM "order" o
JOIN "user" u ON o.user_id = u.id
WHERE u.status = TRUE;

6️⃣ Result Set Modifiers​

Ascending Sorting Sequence​

$query = (new ZTeraDBQuery('product'))
    ->select()
    ->sort(['price' => 1]); // 1 signifies Ascending sorting direction

Equivalent SQL

SELECT * FROM product ORDER BY price ASC;

Multi-Key Compound Sorting​

$query = (new ZTeraDBQuery('product'))
    ->select()
    ->sort([
        'price'    => 1,  // Ascending
        'quantity' => -1  // -1 signifies Descending sorting direction
    ]);

Equivalent SQL

SELECT * FROM product ORDER BY price ASC, quantity DESC;

Table Matrix Record Counting​

$query = (new ZTeraDBQuery('product'))->count();
$result = $db->run($query);

echo 'Active Table Row Count: ' . $result['count'];

Equivalent SQL

SELECT COUNT(*) AS count FROM product;

🏆 Unified Master Blueprint Example​

The comprehensive blueprint below combines explicit field lookups, complex mathematical operators, exact-match flags, multi-index sorting arrangements, and strict window pagination limitations into a singular processing chain.

<?php

use ZTeraDB\Query\ZTeraDBQuery;

// 1. Build complex condition logic tree
$mathCondition = ZTGT(['quantity', 10]);

// 2. Aggregate the composite processing sequence
$query = (new ZTeraDBQuery('product'))
    ->select()
    ->fields([
        'name'     => 1, 
        'price'    => 1, 
        'quantity' => 1
    ])
    ->filterCondition($mathCondition)
    ->filter(['status' => 'A'])
    ->sort(['price' => 1])
    ->limit(0, 20);

// 3. Execute query statement via storage connection driver
$products = $db->run($query);

Compiled Pipeline Target Output

SELECT name, price, quantity
FROM product
WHERE quantity > 10
  AND status = 'A'
ORDER BY price ASC
LIMIT 20 OFFSET 0;

🎉 Next Steps​