model / query-builder
MySQL query builder
Installs: 3 506
Dependents: 1
Suggesters: 0
Security: 0
Stars: 0
Watchers: 1
Forks: 0
Open Issues: 0
pkg:composer/model/query-builder
Requires
- ext-pdo: *
- model/db-parser: ^0.2.0
- dev-master
- v0.6.6
- v0.6.5
- v0.6.4
- v0.6.3
- v0.6.2
- v0.6.1
- v0.6.0
- v0.5.8
- v0.5.7
- v0.5.6
- v0.5.5
- v0.5.4
- v0.5.3
- v0.5.2
- v0.5.1
- v0.5.0
- v0.4.30
- v0.4.29
- v0.4.28
- v0.4.27
- v0.4.26
- v0.4.25
- v0.4.24
- v0.4.23
- v0.4.22
- v0.4.21
- v0.4.20
- v0.4.19
- v0.4.18
- v0.4.17
- v0.4.16
- v0.4.15
- v0.4.14
- v0.4.13
- v0.4.12
- v0.4.11
- v0.4.10
- v0.4.9
- v0.4.8
- v0.4.7
- v0.4.6
- v0.4.5
- v0.4.4
- v0.4.3
- v0.4.2
- v0.4.1
- v0.4.0
- v0.3.2
- v0.3.1
- v0.3.0
- v0.2.10
- v0.2.9
- v0.2.8
- v0.2.7
- v0.2.6
- v0.2.5
- v0.2.4
- v0.2.3
- v0.2.2
- v0.2.1
- v0.2.0
- v0.1.11
- v0.1.10
- v0.1.9
- v0.1.8
- v0.1.7
- v0.1.6
- v0.1.5
- v0.1.4
- v0.1.3
- v0.1.2
- v0.1.1
- v0.1.0
This package is auto-updated.
Last update: 2025-10-06 16:30:27 UTC
README
A powerful SQL query builder for PHP that provides a fluent interface for constructing database queries with automatic validation against your database schema.
Features
- Type-safe query building with automatic validation against database schema
- Support for all major SQL operations (SELECT, INSERT, UPDATE, DELETE)
- Complex WHERE clauses with nested conditions and multiple operators
- JOIN support with automatic foreign key detection
- Aggregation functions (MIN, MAX, SUM, AVG, COUNT, COUNT DISTINCT)
- Special data types handling (POINT, JSON, DATE, DATETIME)
- Data validation to prevent invalid queries
- UNION queries support
Basic Usage
use Model\QueryBuilder\QueryBuilder; use Model\DbParser\Parser; // Initialize with a Parser instance $parser = new Parser($pdo); $qb = new QueryBuilder($parser);
INSERT Queries
Simple Insert
// Insert a single row $query = $qb->insert('users', [ 'name' => 'John Doe', 'email' => 'john@example.com', 'age' => 30, ]); // INSERT INTO `users`(`name`,`email`,`age`) VALUES('John Doe','john@example.com',30)
Bulk Insert
// Insert multiple rows $query = $qb->insert('users', [ ['name' => 'John', 'email' => 'john@example.com'], ['name' => 'Jane', 'email' => 'jane@example.com'], ['name' => 'Bob', 'email' => 'bob@example.com'], ]);
Insert Options
$query = $qb->insert('users', $data, [ 'replace' => true, // Use REPLACE instead of INSERT 'validate_data' => true, // Validate data against schema (default: true) 'cast_null' => false, // Cast null values to appropriate defaults (default: false) ]);
Empty Insert
// Insert empty row (all default values) $query = $qb->insert('users', []); // INSERT INTO `users`() VALUES()
UPDATE Queries
Simple Update
// Update by ID $query = $qb->update('users', 5, [ 'name' => 'Jane Doe', 'age' => 31, ]); // UPDATE `users` SET `users`.`name`='Jane Doe',`users`.`age`=31 WHERE `users`.`id`=5
Update with WHERE Clause
$query = $qb->update('users', ['active' => 1], [ 'status' => 'verified', ]);
Update with Joins
$query = $qb->update('users', ['role' => 'admin'], ['verified' => 1], [ 'alias' => 'u', 'joins' => [ 'profiles' => [ 'on' => 'user_id', 'fields' => [], ], ], ] );
Update Options
$query = $qb->update('users', $where, $data, [ 'alias' => 'u', // Table alias 'joins' => [], // Array of joins 'operator' => 'AND', // WHERE operator (AND/OR) 'validate_where' => true, // Validate WHERE clause 'validate_data' => true, // Validate data 'cast_null' => false, // Cast null values ]);
DELETE Queries
Simple Delete
// Delete by ID $query = $qb->delete('users', 5); // DELETE `users` FROM `users` WHERE `users`.`id`=5
Delete with WHERE Clause
$query = $qb->delete('users', [ 'active' => 0, 'created_at' => ['<', '2020-01-01'], ]);
Delete with Joins
$query = $qb->delete('users', ['verified' => 0], [ 'alias' => 'u', 'joins' => [ 'profiles' => [ 'where' => ['completed' => 0], ], ], ]);
SELECT Queries
Simple Select
// Select all columns $query = $qb->select('users'); // SELECT `users`.*,ST_AsText(`users`.`location`) AS `location` FROM `users` // Select by ID $query = $qb->select('users', 5); // SELECT `users`.* FROM `users` WHERE `users`.`id`=5
Select Specific Fields
$query = $qb->select('users', [], [ 'fields' => ['id', 'name', 'email'], ]);
Select with Aliases
$query = $qb->select('users', [], [ 'fields' => [ 'id' => 'user_id', 'name' => 'full_name', 'email', ], ]); // SELECT `users`.`id` AS `user_id`,`users`.`name` AS `full_name`,`users`.`email` FROM `users`
Select with Raw Fields
$query = $qb->select('users', [], [ 'fields' => ['COUNT(*) as total', 'MAX(age) as max_age'], 'raw_fields' => true, ]);
Aggregation Functions
$query = $qb->select('orders', [], [ 'fields' => ['user_id'], 'count' => ['id' => 'total_orders'], 'sum' => ['amount' => 'total_amount'], 'avg' => ['amount' => 'avg_amount'], 'min' => ['amount' => 'min_amount'], 'max' => ['amount' => 'max_amount'], 'count_distinct' => ['product_id' => 'unique_products'], 'group_by' => ['user_id'], ]);
GROUP BY and HAVING
$query = $qb->select('orders', [], [ 'fields' => ['user_id'], 'count' => ['id' => 'total'], 'group_by' => ['user_id'], 'having' => ['total > 5'], ]);
ORDER BY
// Simple order $query = $qb->select('users', [], [ 'order_by' => 'created_at DESC', ]); // Multiple fields $query = $qb->select('users', [], [ 'order_by' => [ ['name', 'ASC'], ['created_at', 'DESC'], ], ]);
LIMIT and OFFSET
$query = $qb->select('users', [], [ 'limit' => 10, 'offset' => 20, ]); // SELECT ... FROM `users` LIMIT 20,10
Joins
Simple Join (Auto-detected FK)
$query = $qb->select('users', [], [ 'joins' => [ 'profiles', // Will auto-detect FK relationship ], ]);
Join with Specific Fields
$query = $qb->select('users', [], [ 'joins' => [ 'profiles' => ['bio', 'avatar'], ], ]);
Join with Custom ON Clause
$query = $qb->select('users', [], [ 'joins' => [ 'profiles' => [ 'on' => ['id' => 'user_id'], 'fields' => ['bio', 'avatar'], ], ], ]);
Join with Alias
$query = $qb->select('users', [], [ 'alias' => 'u', 'joins' => [ [ 'table' => 'profiles', 'alias' => 'p', 'on' => ['u.id' => 'p.user_id'], 'fields' => ['bio' => 'user_bio'], ], ], ]);
Join with WHERE Condition
$query = $qb->select('users', [], [ 'joins' => [ 'profiles' => [ 'where' => ['verified' => 1], 'fields' => ['bio'], ], ], ]);
Join Types
$query = $qb->select('users', [], [ 'joins' => [ [ 'table' => 'profiles', 'type' => 'LEFT', // INNER (default), LEFT, RIGHT, etc. 'fields' => ['bio'], ], ], ]);
Select Options
$query = $qb->select('users', $where, [ 'alias' => null, // Table alias 'joins' => [], // Array of joins 'fields' => null, // Fields to select (null = all) 'min' => [], // MIN aggregations 'max' => [], // MAX aggregations 'sum' => [], // SUM aggregations 'avg' => [], // AVG aggregations 'count' => [], // COUNT aggregations 'count_distinct' => [], // COUNT DISTINCT aggregations 'raw_fields' => false, // Use raw field names 'group_by' => null, // GROUP BY clause 'having' => [], // HAVING clause 'order_by' => null, // ORDER BY clause 'limit' => null, // LIMIT 'offset' => null, // OFFSET 'operator' => 'AND', // WHERE operator 'validate_where' => true, // Validate WHERE clause ]);
WHERE Clauses
Simple Conditions
// Key-value pairs (uses = operator) $where = [ 'active' => 1, 'status' => 'verified', ]; // Specify by ID $where = 5; // Equivalent to ['id' => 5]
Comparison Operators
$where = [ 'age' => ['>', 18], 'created_at' => ['<', '2023-01-01'], 'status' => ['!=', 'deleted'], 'rating' => ['>=', 4.5], ];
NULL Comparisons
$where = [ 'deleted_at' => null, // IS NULL 'email' => ['!=', null], // IS NOT NULL ];
BETWEEN
$where = [ 'age' => ['BETWEEN', [18, 65]], 'created_at' => ['BETWEEN', ['2023-01-01', '2023-12-31']], ];
IN and NOT IN
$where = [ 'status' => ['IN', ['active', 'pending', 'verified']], 'role' => ['NOT IN', ['banned', 'deleted']], ]; // Empty array handling $where = [ 'status' => ['IN', []], // Results in (1=2) - impossible condition 'status' => ['NOT IN', []], // Ignored ];
LIKE
$where = [ 'name' => ['LIKE', '%John%'], 'email' => ['LIKE', '%@gmail.com'], ];
MATCH (Full-text Search)
// Simple match $where = [ 'description' => ['MATCH', 'search terms'], ]; // Match with mode $where = [ 'description' => ['MATCH BOOLEAN', '+required -excluded'], 'content' => ['MATCH NATURAL LANGUAGE', 'search query'], 'text' => ['MATCH QUERY EXPANSION', 'keywords'], ];
AND Operator (Default)
$where = [ 'active' => 1, 'verified' => 1, 'age' => ['>', 18], ]; // WHERE active=1 AND verified=1 AND age>18
OR Operator
$where = [ 'OR' => [ 'status' => 'active', 'status' => 'pending', ], ]; // Alternative syntax $where = [ ['OR', [ 'status' => 'active', 'status' => 'pending', ]], ];
Nested Conditions
$where = [ 'active' => 1, 'OR' => [ 'role' => 'admin', 'AND' => [ 'role' => 'moderator', 'verified' => 1, ], ], ]; // WHERE active=1 AND (role='admin' OR (role='moderator' AND verified=1))
Complex Nested Example
$where = [ 'AND' => [ 'active' => 1, 'OR' => [ 'status' => 'premium', 'AND' => [ 'status' => 'free', 'credits' => ['>', 0], ], ], ], ];
Sub-operator Format
$where = [ [ 'sub' => [ 'role' => 'admin', 'verified' => 1, ], 'operator' => 'OR', ], ];
Array Format for Conditions
// Two-element array: [column, value] $where = [ ['status', 'active'], ]; // Three-element array: [column, operator, value] $where = [ ['age', '>', 18], ['created_at', '<', '2023-01-01'], ];
Raw SQL Conditions
$where = [ 'YEAR(created_at) = 2023', 'MONTH(updated_at) = ' . date('m'), ];
Table Prefixes in WHERE
$where = [ 'users.active' => 1, 'profiles.verified' => 1, ];
UNION SELECT
Combine multiple SELECT queries:
$query = $qb->unionSelect([ [ 'table' => 'users', 'where' => ['active' => 1], 'options' => ['fields' => ['id', 'name', 'email']], ], [ 'table' => 'archived_users', 'where' => ['restored' => 1], 'options' => ['fields' => ['id', 'name', 'email']], ], ], [ 'order_by' => 'name ASC', 'limit' => 100, ]);
Special Data Types
POINT (Geospatial)
// Insert with POINT $qb->insert('locations', [ 'name' => 'Office', 'coordinates' => ['lat' => 40.7128, 'lng' => -74.0060], ]); // Query with POINT $qb->select('locations', [ 'coordinates' => ['lat' => 40.7128, 'lng' => -74.0060], ]);
The QueryBuilder automatically handles POINT columns:
- In INSERT/UPDATE: Converts arrays to
POINT(lng, lat)
format - In SELECT: Uses
ST_AsText()
for retrieval
JSON
// Insert with JSON $qb->insert('settings', [ 'config' => ['theme' => 'dark', 'language' => 'en'], ]);
DateTime Objects
$qb->insert('events', [ 'name' => 'Conference', 'start_date' => new DateTime('2023-06-01'), ]);
Utility Methods
Build Query String
Build a WHERE clause string separately:
$whereString = $qb->buildQueryString(['active' => 1, 'role' => 'admin'], [ 'table' => 'users', 'operator' => 'AND', 'validate' => true, ]);
Parse Column
Format column names with backticks:
$parsed = $qb->parseColumn('name', 'users'); // `users`.`name`
Parse Value
Format values for SQL:
$parsed = $qb->parseValue('John', 'varchar'); // 'John' $parsed = $qb->parseValue(null); // NULL
Automatic parsing
Please note: all column names and values are already automatically parsed, the parseColumn
and parseValue
methods are for exception use cases.
Get Fields Involved in WHERE
Extract field names from a WHERE clause:
$fields = $qb->getFieldsInvolvedInWhere([ 'name' => 'John', 'age' => ['>', 18], 'OR' => [ 'status' => 'active', 'verified' => 1, ], ]); // Returns: ['name', 'age', 'status', 'verified']
Validation
The QueryBuilder validates queries against your database schema by default:
- Column existence: Ensures columns exist in the specified table
- Data types: Validates that values match column types (int, varchar, date, etc.)
- Length constraints: Checks varchar/char length limits
- NULL constraints: Prevents NULL values for NOT NULL columns
- Foreign keys: Auto-detects FK relationships for joins
Disable Validation
// Disable validation for specific operations $qb->insert('users', $data, ['validate_data' => false]); $qb->update('users', $where, $data, ['validate_where' => false, 'validate_data' => false]); $qb->select('users', $where, ['validate_where' => false]);
Cast NULL Values
When cast_null
is enabled, NULL values for NOT NULL columns are automatically converted:
$qb->insert('users', [ 'name' => null, // Will be cast to '' for varchar 'age' => null, // Will be cast to 0 for int ], ['cast_null' => true]);
Cast values:
- Numeric types (int, float, etc.):
0
- Date:
0000-00-00
- Time:
00:00:00
- DateTime:
0000-00-00 00:00:00
- Other types:
''
(empty string)
Best Practices
- Use validation: Keep validation enabled to catch errors early
- Use prepared statements: Always execute queries with PDO prepared statements
- Table aliases: Use aliases for complex queries with joins
- Specific fields: Select only the fields you need instead of
*
- Indexes: Ensure WHERE and JOIN columns are indexed
- Foreign keys: Define FKs properly to leverage auto-detection in joins
Error Handling
The QueryBuilder throws exceptions for various error conditions:
try { $query = $qb->select('users', ['nonexistent_column' => 1]); } catch (\Exception $e) { // Handle error: Column "nonexistent_column" does not exist in table "users" }
Common exceptions:
- Column does not exist
- Invalid operator
- Bad value type for column
- Invalid date format
- Length exceeded for varchar/char columns
- NULL value for NOT NULL column
- Ambiguous foreign key in joins
- Missing required join parameters
Examples
Pagination
$page = 1; $perPage = 20; $query = $qb->select('users', ['active' => 1], [ 'order_by' => 'created_at DESC', 'limit' => $perPage, 'offset' => ($page - 1) * $perPage, ]);
Search with Filters
$query = $qb->select('products', [ 'category_id' => ['IN', [1, 2, 3]], 'price' => ['BETWEEN', [10, 100]], 'active' => 1, 'OR' => [ 'name' => ['LIKE', '%' . $searchTerm . '%'], 'description' => ['LIKE', '%' . $searchTerm . '%'], ], ], [ 'order_by' => 'created_at DESC', 'limit' => 50, ]);
Complex Join Query
$query = $qb->select('orders', ['orders.status' => 'completed'], [ 'alias' => 'o', 'fields' => [ 'id', 'total', 'created_at', ], 'joins' => [ [ 'table' => 'users', 'alias' => 'u', 'on' => ['o.user_id' => 'u.id'], 'fields' => [ 'name' => 'customer_name', 'email' => 'customer_email', ], ], [ 'table' => 'order_items', 'alias' => 'oi', 'type' => 'LEFT', 'on' => ['o.id' => 'oi.order_id'], 'fields' => [], ], ], 'count' => ['oi.id' => 'total_items'], 'sum' => ['oi.quantity' => 'total_quantity'], 'group_by' => ['o.id'], 'order_by' => [['o.created_at', 'DESC']], ]);
Conditional Update
$query = $qb->update('users', [ 'last_login' => ['<', date('Y-m-d', strtotime('-30 days'))], 'status' => 'active', ], [ 'status' => 'inactive', 'updated_at' => date('Y-m-d H:i:s'), ] );