Query Class

Query Builder (Fluent API)

---

The Query class provides a fluent, chainable interface for building and executing database queries in GeniXCMS. It improves code readability while automatically handling security via PDO prepared statements for all bound parameters.

---

🚀 Basic Initialization

The entry point for any operation is the table() static method.

// Targets the 'posts' table (without prefix)
$query = Query::table('posts');

---

🔍 Data Selection

select($columns)

Define the dataset you wish to retrieve. Accepts a string or an array.

Type	Example
All Columns	Query::table('posts')->get(); (Default)
Specific Column	Query::table('posts')->select('title')->get();
Array Selection	Query::table('posts')->select(['id', 'title'])->get();

---

🛠️ Filtering (Where Clauses)

where($column, $operator, $value)

Adds a standard WHERE constraint. If the operator is omitted, = is used by default.

// Equality check (shorthand)
$active = Query::table('posts')->where('status', '1')->get();

// Relational operators
$recent = Query::table('posts')->where('date', '>', '2024-01-01')->get();

// Pattern matching
$search = Query::table('posts')->where('title', 'LIKE', '%keyword%')->get();

orWhere($column, $operator, $value)

Chains an additional constraint using OR logic.

$posts = Query::table('posts')
    ->where('type', 'post')
    ->orWhere('type', 'page')
    ->get();

whereIn($column, array $values)

Filters results where a column's value matches any item in an array.

$selected = Query::table('posts')
    ->whereIn('id', [1, 5, 12, 20])
    ->get();

whereRaw($sql, array $bindings = [])

Appends a raw SQL clause for complex conditions not covered by the fluent API.

$results = Query::table('posts')
    ->whereRaw('YEAR(date) = ? AND MONTH(date) = ?', [2024, 3])
    ->get();

---

🔗 Table Joins

join($table, $first, $operator, $second, $type = 'INNER')

Fluent interface for combining related tables.

$results = Query::table('posts')
    ->select('posts.*, user.userid')
    ->join('user', 'posts.author', '=', 'user.userid')
    ->get();

// LEFT JOIN example
$results = Query::table('posts')
    ->join('posts_param', 'posts.id', '=', 'posts_param.post_id', 'LEFT')
    ->get();

---

🔢 Ordering, Grouping & Pagination

Method	Parameters	Description
orderBy	$column, $direction	Sort results by column (ASC/DESC).
orderByRaw	$expression	Sort using a raw SQL expression.
groupBy	$column	Group results by a column.
limit	$count, $offset	Restrict number of rows and set starting point.

// Get 10 latest posts, skipping the first 5
$paged = Query::table('posts')
    ->orderBy('date', 'DESC')
    ->limit(10, 5)
    ->get();

// Group by category with raw ordering
$grouped = Query::table('posts_param')
    ->select('value, COUNT(*) as total')
    ->where('param', 'tags')
    ->groupBy('value')
    ->orderByRaw('total DESC')
    ->get();

---

⚡ Execution Methods

• get(): Returns an array of objects containing all matched records.
• first(): Returns the first object found, or null if empty.
• count(): Returns the integer count of records matching the query.
• sum($column): Returns the float sum of a numeric column.

// Count published posts
$total = Query::table('posts')->where('status', '1')->count();

// Sum of all product prices
$revenue = Query::table('marketplace_orders')->sum('amount');

---

💾 Write Operations

insert(array $data)

Creates a new record.

Query::table('cat')->insert([
    'name'   => 'Technology',
    'slug'   => 'technology',
    'type'   => 'cat',
    'parent' => 0,
]);

update(array $data)

Modifies existing records matching the where criteria.

Query::table('posts')
    ->where('id', 5)
    ->update(['status' => '0']);

delete()

Deletes records matching the where criteria.

// Delete a specific tag
Query::table('cat')
    ->where('id', 7)
    ->where('type', 'tag')
    ->delete();

---