Download the PHP package model/query-builder without Composer

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

• 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

INSERT Queries

Simple Insert

Bulk Insert

Insert Options

Empty Insert

UPDATE Queries

Simple Update

Update with WHERE Clause

Update with Joins

Update Options

DELETE Queries

Simple Delete

Delete with WHERE Clause

Delete with Joins

SELECT Queries

Simple Select

Select Specific Fields

Select with Aliases

Select with Raw Fields

Aggregation Functions

GROUP BY and HAVING

ORDER BY

LIMIT and OFFSET

Joins

Simple Join (Auto-detected FK)

Join with Specific Fields

Join with Custom ON Clause

Join with Alias

Join with WHERE Condition

Join Types

Select Options

WHERE Clauses

Simple Conditions

Comparison Operators

NULL Comparisons

BETWEEN

IN and NOT IN

LIKE

MATCH (Full-text Search)

AND Operator (Default)

OR Operator

Nested Conditions

Complex Nested Example

Sub-operator Format

Array Format for Conditions

Raw SQL Conditions

Table Prefixes in WHERE

UNION SELECT

Combine multiple SELECT queries:

Special Data Types

POINT (Geospatial)

The QueryBuilder automatically handles POINT columns:

• In INSERT/UPDATE: Converts arrays to POINT(lng, lat) format
• In SELECT: Uses ST_AsText() for retrieval

JSON

DateTime Objects

Utility Methods

Build Query String

Build a WHERE clause string separately:

Parse Column

Format column names with backticks:

Parse Value

Format values for SQL:

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:

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

Cast NULL Values

When cast_null is enabled, NULL values for NOT NULL columns are automatically converted:

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:

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

Search with Filters

Complex Join Query

Conditional Update