model/query-builder

There is no license information available for the latest version (v0.6.6) of this package.

MySQL query builder

Maintainers

Details

github.com/model-composer/query-builder

Source

Issues

Installs: 3 560

Dependents: 1

Suggesters: 0

Security: 0

Stars: 0

Watchers: 1

Forks: 0

Open Issues: 0

pkg:composer/model/query-builder

v0.6.6 2025-10-06 16:26 UTC

Requires

Unknown License 2744c3e7d089410d48ba5004e57f859999ac6c02

  • Domenico Giambra <zorinik.woop@gmail.com>

This package is auto-updated.

Last update: 2025-10-09 09:37:55 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

  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

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'),
	]
);