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 = 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 = ZTeraDBQuery("schemaName")

schemaName → table/schema in your ZTeraDB instance

---

🔥 Query Types

1️⃣ SELECT

query = ZTeraDBQuery("user").select()

2️⃣ INSERT

query = (
    ZTeraDBQuery("user")
    .insert()
    .fields({
        "email": "test@site.com",
        "status": True
    })
)

3️⃣ UPDATE

query = (
    ZTeraDBQuery("user")
    .update()
    .fields({"status": False})
    .filter({"id": 1})
)

4️⃣ DELETE

query = (
    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)

query.filter({"status": True})
query.filter({"id": 10})
query.filter({"email": "abc@test.com"})

Equivalent SQL:

WHERE status = true

---

🎛 Advanced Filters (filterCondition)

Advanced filters allow:

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

from zteradb.filter_condition import ZTEQUAL, ZTMUL

query.filterCondition(
    ZTEQUAL(
        ZTMUL(["price", "quantity"]),
        200
    )
)

Equivalent SQL:

WHERE price * quantity = 200

---

🔗 relatedFields() — Joins Made Easy

Fetch data from related schemas.

userFilter = (
    ZTeraDBQuery("user")
    .select()
    .fields({"email": 1})
    .filter({"status": True})
)

query = (
    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)

---

🔢 count()

Returns number of matching records instead of row data.

query.count()

---

🧪 Full Example

query = (
    ZTeraDBQuery("product")
    .select()
    .fields({"name": 1, "price": 1})
    .filter({"status": "A"})
    .sort({"price": 1})
    .limit(0, 20)
)

---

🛑 Common Mistakes

❌ Using filterCondition for simple equality
✔ Use .filter() unless advanced logic is needed

❌ Not calling .fields() for insert/update
✔ Always define fields for INSERT and UPDATE

❌ Wrong sort values
✔ Only use 1 or -1

❌ Missing schemaName
✔ Always pass a valid schema name to constructor

---

🎉 You’re Ready for Filtering Logic!

Continue to:
👉 filter-condition.md