Skip to main content

ZTeraDB Query Builder Guide

The ZTeraDBQuery class helps you build database queries without writing SQL.

🎯 What is ZTeraDBQuery?

It is a chainable query builder that lets you perform:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • Filtering
  • Sorting
  • Pagination
  • Related field lookups (joins)

Example:

$query = (new ZTeraDBQuery("user"))->select();

🧠 How Query Building Works

Start Query
   ↓
Choose Type (select/insert/update/delete)
   ↓
Add Fields (optional)
   ↓
Apply Filters
   ↓
Run the Query

🏗 Creating a Query

$query = new ZTeraDBQuery("schemaName");

schemaName → table/schema in your ZTeraDB instance
Example: "user", "product", "order"

🔥 Query Types

1️⃣ SELECT​

$query = (new ZTeraDBQuery("user"))->select();

2️⃣ INSERT​

$query = (new ZTeraDBQuery("user"))
  ->insert()
  ->fields([
    "email" => "test@site.com",
    "status" => true
  ]);

3️⃣ UPDATE​

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

4️⃣ DELETE​

$query = (new ZTeraDBQuery("user"))
  ->delete()
  ->filter(["id" => 5]);

🏷 fields() — Selecting, Inserting, Updating

SELECT Specific Fields​

$query->select()->fields([
  "email" => 1,
  "status" => 1
]);

INSERT New Row​

$query->insert()->fields([
  "name" => "John",
  "email" => "john@test.com",
  "status" => true
]);

UPDATE Existing Row​

$query->update()->fields([
  "status" => false
]);

🎯 Basic Filters (filter)

Use .filter(["field" => value]) for simple equality checks:

$query->filter(["status" => true]);
$query->filter(["id" => 10]);
$query->filter(["email" => "abc@test.com"]);

Equivalent to:

WHERE status = true

🎛 Advanced Filters (filterCondition)

Advanced filters allow:

  • Math operations
  • AND / OR conditions
  • String filters
  • Comparisons (>, <, >=, <=)

Example:

$query->filterCondition(
  ZTEQUAL(
    ZTMUL(["price", "quantity"]),
    200
  )
);

Equivalent SQL:

WHERE price * quantity = 200

🔗 relatedFields() — Joins Made Easy

Fetch data from related schemas.

Example:

$userFilter = (new ZTeraDBQuery("user"))
  ->select()
  ->fields(["email" => 1])
  ->filter(["status" => true]);

$query = (new ZTeraDBQuery("order"))
  ->select()
  ->relatedFields([
    "user" => $userFilter
  ]);

📚 Sorting

$query->sort(["price" => 1]);    // ascending
$query->sort(["price" => -1]);   // descending

Equivalent:

  • 1 → ASC
  • -1 → DESC

📏 limit(start, end) — Pagination

$query->limit(0, 10); // first 10 rows

🔢 count()

Returns number of matching records instead of row data.

$query->count();

🧪 Full Example

$query = (new ZTeraDBQuery("product"))
  ->select()
  ->fields(["name" => 1, "price" => 1])
  ->filter(["status" => "A"])
  ->sort(["price" => 1])
  ->limit(0, 20);

🛑 Common Mistakes

❌ Using filterCondition for simple equalities
✔ Use .filter() unless you need complex logic

❌ Forgetting .fields() with insert/update
✔ Always set fields before inserts/updates

❌ Wrong sort values
✔ Use only 1 or -1

❌ Using SELECT without schemaName
✔ Always pass valid schema to constructor

🎉 You’re Ready for Filtering Logic!

Continue to:
👉 filter-condition.md