PHPackages                             model/query-builder - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. model/query-builder

ActiveLibrary[Database & ORM](/categories/database)

model/query-builder
===================

MySQL query builder

v0.6.8(2mo ago)04.0k↓50%1PHP

Since May 21Pushed 2mo ago1 watchersCompare

[ Source](https://github.com/model-composer/query-builder)[ Packagist](https://packagist.org/packages/model/query-builder)[ RSS](/packages/model-query-builder/feed)WikiDiscussions master Synced 1mo ago

READMEChangelogDependencies (2)Versions (76)Used By (1)

QueryBuilder
============

[](#querybuilder)

A powerful SQL query builder for PHP that provides a fluent interface for constructing database queries with automatic validation against your database schema.

Features
--------

[](#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
-----------

[](#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
--------------

[](#insert-queries)

### Simple Insert

[](#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

[](#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

[](#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

[](#empty-insert)

```
// Insert empty row (all default values)
$query = $qb->insert('users', []);
// INSERT INTO `users`() VALUES()
```

UPDATE Queries
--------------

[](#update-queries)

### Simple Update

[](#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

[](#update-with-where-clause)

```
$query = $qb->update('users', ['active' => 1], [
	'status' => 'verified',
]);
```

### Update with Joins

[](#update-with-joins)

```
$query = $qb->update('users', ['role' => 'admin'],
	['verified' => 1],
	[
		'alias' => 'u',
		'joins' => [
			'profiles' => [
				'on' => 'user_id',
				'fields' => [],
			],
		],
	]
);
```

### Update Options

[](#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
--------------

[](#delete-queries)

### Simple Delete

[](#simple-delete)

```
// Delete by ID
$query = $qb->delete('users', 5);
// DELETE `users` FROM `users` WHERE `users`.`id`=5
```

### Delete with WHERE Clause

[](#delete-with-where-clause)

```
$query = $qb->delete('users', [
	'active' => 0,
	'created_at' => [' 5'],
]);
```

### ORDER BY

[](#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

[](#limit-and-offset)

```
$query = $qb->select('users', [], [
	'limit' => 10,
	'offset' => 20,
]);
// SELECT ... FROM `users` LIMIT 20,10
```

### Joins

[](#joins)

#### Simple Join (Auto-detected FK)

[](#simple-join-auto-detected-fk)

```
$query = $qb->select('users', [], [
	'joins' => [
		'profiles',  // Will auto-detect FK relationship
	],
]);
```

#### Join with Specific Fields

[](#join-with-specific-fields)

```
$query = $qb->select('users', [], [
	'joins' => [
		'profiles' => ['bio', 'avatar'],
	],
]);
```

#### Join with Custom ON Clause

[](#join-with-custom-on-clause)

```
$query = $qb->select('users', [], [
	'joins' => [
		'profiles' => [
			'on' => ['id' => 'user_id'],
			'fields' => ['bio', 'avatar'],
		],
	],
]);
```

#### Join with Alias

[](#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

[](#join-with-where-condition)

```
$query = $qb->select('users', [], [
	'joins' => [
		'profiles' => [
			'where' => ['verified' => 1],
			'fields' => ['bio'],
		],
	],
]);
```

#### Join Types

[](#join-types)

```
$query = $qb->select('users', [], [
	'joins' => [
		[
			'table' => 'profiles',
			'type' => 'LEFT',  // INNER (default), LEFT, RIGHT, etc.
			'fields' => ['bio'],
		],
	],
]);
```

### Select Options

[](#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
-------------

[](#where-clauses)

### Simple Conditions

[](#simple-conditions)

```
// Key-value pairs (uses = operator)
$where = [
	'active' => 1,
	'status' => 'verified',
];

// Specify by ID
$where = 5;  // Equivalent to ['id' => 5]
```

### Comparison Operators

[](#comparison-operators)

```
$where = [
	'age' => ['>', 18],
	'created_at' => ['=', 4.5],
];
```

### NULL Comparisons

[](#null-comparisons)

```
$where = [
	'deleted_at' => null,  // IS NULL
	'email' => ['!=', null],  // IS NOT NULL
];
```

### BETWEEN

[](#between)

```
$where = [
	'age' => ['BETWEEN', [18, 65]],
	'created_at' => ['BETWEEN', ['2023-01-01', '2023-12-31']],
];
```

### IN and NOT IN

[](#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

[](#like)

```
$where = [
	'name' => ['LIKE', '%John%'],
	'email' => ['LIKE', '%@gmail.com'],
];
```

### MATCH (Full-text Search)

[](#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)

[](#and-operator-default)

```
$where = [
	'active' => 1,
	'verified' => 1,
	'age' => ['>', 18],
];
// WHERE active=1 AND verified=1 AND age>18
```

### OR Operator

[](#or-operator)

```
$where = [
	'OR' => [
		'status' => 'active',
		'status' => 'pending',
	],
];

// Alternative syntax
$where = [
	['OR', [
		'status' => 'active',
		'status' => 'pending',
	]],
];
```

### Nested Conditions

[](#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

[](#complex-nested-example)

```
$where = [
	'AND' => [
		'active' => 1,
		'OR' => [
			'status' => 'premium',
			'AND' => [
				'status' => 'free',
				'credits' => ['>', 0],
			],
		],
	],
];
```

### Sub-operator Format

[](#sub-operator-format)

```
$where = [
	[
		'sub' => [
			'role' => 'admin',
			'verified' => 1,
		],
		'operator' => 'OR',
	],
];
```

### Array Format for Conditions

[](#array-format-for-conditions)

```
// Two-element array: [column, value]
$where = [
	['status', 'active'],
];

// Three-element array: [column, operator, value]
$where = [
	['age', '>', 18],
	['created_at', '', 18],
	'OR' => [
		'status' => 'active',
		'verified' => 1,
	],
]);
// Returns: ['name', 'age', 'status', 'verified']
```

Validation
----------

[](#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)

```
// 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

[](#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
--------------

[](#best-practices)

1. **Use validation**: Keep validation enabled to catch errors early
2. **Use prepared statements**: Always execute queries with PDO prepared statements
3. **Table aliases**: Use aliases for complex queries with joins
4. **Specific fields**: Select only the fields you need instead of `*`
5. **Indexes**: Ensure WHERE and JOIN columns are indexed
6. **Foreign keys**: Define FKs properly to leverage auto-detection in joins

Error Handling
--------------

[](#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
--------

[](#examples)

### Pagination

[](#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

[](#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

[](#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

[](#conditional-update)

```
$query = $qb->update('users',
	[
		'last_login' => ['