README

A lightweight and flexible SQL query builder for PHP 8.2+ with support for multiple SQL dialects.

Features

• 🚀 Fast and lightweight SQL builder with zero external dependencies
• 💪 PHP 8.2+ support with strict typing
• 🔒 Secure parameterized queries for protection against SQL injections
• 🧩 Intuitive fluent interface for building queries
• 🔄 Support for different DBMS (MySQL, PostgreSQL, SQLite) with extensibility
• 📦 Simple integration into any PHP project
• 🧩 Advanced features: query caching, subqueries, raw SQL expressions

Installation

composer require solophp/query-builder

Quick Start

use Solo\QueryBuilder\Facade\Query;
use Solo\QueryBuilder\Executors\PdoExecutor\PdoExecutor;
use Solo\QueryBuilder\Executors\PdoExecutor\Connection;
use Solo\QueryBuilder\Executors\PdoExecutor\Config;

// Create PDO executor
$config = new Config('localhost', 'username', 'password', 'database');
$connection = new Connection($config);
$executor = new PdoExecutor($connection);

// Creating a Query instance
$query = new Query($executor);

// Select data
$result = $query->from('users')
    ->select('id', 'name', 'email')
    ->where('status = ?', 'active')
    ->orderBy('created_at DESC')
    ->limit(10)
    ->get();

// Insert data
$insertId = $query->insert('users')
    ->values([
        'name' => 'John Doe', 
        'email' => 'john@example.com', 
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->insertGetId();

// Update data
$affectedRows = $query->update('users')
    ->set('status', 'inactive')
    ->set('updated_at', date('Y-m-d H:i:s'))
    ->where('last_login < ?', date('Y-m-d', strtotime('-6 months')))
    ->execute();

// Delete data
$affectedRows = $query->delete('users')
    ->where('id = ?', 5)
    ->execute();

Building without Executing

You can also build queries without executing them:

// Build a query without executing
[$sql, $bindings] = $query->from('users')
    ->select('id', 'name')
    ->where('status = ?', 'active')
    ->build();

// Now you have the SQL string and parameter bindings
echo $sql;
// SELECT `id`, `name` FROM `users` WHERE status = ?

print_r($bindings);
// ['active']

Multi-DBMS Support

The library implements SQL grammar abstraction, allowing you to work with different database systems using the same API.

Setting Default DBMS

// Create a Query instance
$query = new Query($executor);

// Set MySQL as default grammar
$query->setDatabaseType('mysql');

// Set PostgreSQL as default grammar
$query->setDatabaseType('postgresql'); // or 'postgres', 'pgsql'

// Set SQLite as default grammar
$query->setDatabaseType('sqlite');

Specifying DBMS for a Query

// Create a Query instance
$query = new Query($executor);

// Query with MySQL grammar
$query->setDatabaseType('mysql');
$mysqlResults = $query->from('users')
    ->select('id', 'name')
    ->where('status = ?', 'active')
    ->get();

// Query with PostgreSQL grammar
$query->setDatabaseType('postgresql');
$postgresResults = $query->from('users')
    ->select('id', 'name')
    ->where('status = ?', 'active')
    ->get();

SELECT Queries

Basic Selection Operations

// Create a Query instance
$query = new Query($executor);

// Select all records from table
$allUsers = $query->from('users')->get();

// Select specific columns
$users = $query->from('users')
    ->select('id', 'name', 'email')
    ->get();

// WHERE conditions
$activeUsers = $query->from('users')
    ->where('status = ?', 'active')
    ->get();

// Multiple conditions
$recentActiveUsers = $query->from('users')
    ->where('status = ?', 'active')
    ->where('created_at > ?', '2023-01-01')
    ->get();

// Sorting
$sortedUsers = $query->from('users')
    ->orderBy('name ASC')
    ->orderBy('created_at DESC')
    ->get();

// Limit and offset
$paginatedUsers = $query->from('users')
    ->limit(10)
    ->get();

Raw SQL Expressions

You can use raw SQL expressions by enclosing them in curly braces {...}:

// Raw expressions in select
$users = $query->from('users')
    ->select('id', 'name', '{CONCAT(first_name, " ", last_name) as full_name}')
    ->get();

// Aggregation functions
$userStats = $query->from('orders')
    ->select('user_id', '{COUNT(*) as order_count}', '{SUM(amount) as total_spend}')
    ->groupBy('user_id')
    ->having('total_spend > ?', 1000)
    ->get();

// Date functions
$ordersByMonth = $query->from('orders')
    ->select('id', '{DATE_FORMAT(created_at, "%Y-%m") as month}', 'status')
    ->where('created_at >= ?', '2023-01-01')
    ->get();

// Complex expressions
$categorizedProducts = $query->from('products')
    ->select(
        'id', 
        'name',
        '{CASE WHEN price > 100 THEN "Premium" WHEN price > 50 THEN "Standard" ELSE "Basic" END as category}'
    )
    ->get();

Complex Conditions

// Create a Query instance
$query = new Query($executor);

// Nested conditions
$users = $query->from('users')
    ->where(function($condition) {
        $condition->where('status = ?', 'active')
                 ->orWhere('role = ?', 'admin');
    })
    ->get();

// IN conditions
$specificUsers = $query->from('users')
    ->where('id IN (?, ?, ?)', 1, 2, 3)
    ->get();

// BETWEEN conditions
$usersInRange = $query->from('users')
    ->where('created_at BETWEEN ? AND ?', '2023-01-01', '2023-12-31')
    ->get();

JOIN Operations

// Create a Query instance
$query = new Query($executor);

// INNER JOIN
$ordersWithUsers = $query->from('orders')
    ->select('orders.id', 'orders.amount', 'users.name')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->get();

// LEFT JOIN
$usersWithProfiles = $query->from('users')
    ->select('users.id', 'users.name', 'profiles.bio')
    ->leftJoin('profiles', 'users.id', '=', 'profiles.user_id')
    ->get();

// RIGHT JOIN
$usersWithOrders = $query->from('orders')
    ->select('orders.id', 'users.name')
    ->rightJoin('users', 'orders.user_id', '=', 'users.id')
    ->get();

// FULL JOIN
$allUsersProfiles = $query->from('users')
    ->select('users.id', 'profiles.bio')
    ->fullJoin('profiles', 'users.id', '=', 'profiles.user_id')
    ->get();

Grouping and Aggregation

// Create a Query instance
$query = new Query($executor);

// GROUP BY with aggregate functions
$userOrderStats = $query->from('orders')
    ->select('user_id', '{COUNT(*) as order_count}', '{SUM(amount) as total_spend}')
    ->groupBy('user_id')
    ->having('total_spend > ?', 1000)
    ->get();

INSERT Queries

// Create a Query instance
$query = new Query($executor);

// Insert one record and get ID
$userId = $query->insert('users')
    ->values([
        'name' => 'John Doe', 
        'email' => 'john@example.com', 
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->insertGetId();

// Insert one record and get affected rows
$affectedRows = $query->insert('users')
    ->values([
        'name' => 'John Doe', 
        'email' => 'john@example.com', 
        'created_at' => date('Y-m-d H:i:s')
    ])
    ->execute();

// Insert multiple records
$affectedRows = $query->insert('logs')
    ->values([
        ['user_id' => 1, 'action' => 'login', 'created_at' => date('Y-m-d H:i:s')],
        ['user_id' => 2, 'action' => 'logout', 'created_at' => date('Y-m-d H:i:s')]
    ])
    ->execute();

UPDATE Queries

// Create a Query instance
$query = new Query($executor);

// Update with array of values
$affectedRows = $query->update('users')
    ->set([
        'status' => 'inactive',
        'updated_at' => date('Y-m-d H:i:s')
    ])
    ->where('last_login < ?', date('Y-m-d', strtotime('-6 months')))
    ->execute();

// Or update by setting fields individually
$affectedRows = $query->update('users')
    ->set('status', 'inactive')
    ->set('updated_at', date('Y-m-d H:i:s'))
    ->where('id = ?', 5)
    ->execute();

// Check if update was successful
$isUpdated = $query->update('users')
    ->set('status', 'inactive')
    ->where('id = ?', 5)
    ->wasSuccessful();

DELETE Queries

// Create a Query instance
$query = new Query($executor);

// Delete with condition
$affectedRows = $query->delete('expired_tokens')
    ->where('expires_at < ?', date('Y-m-d H:i:s'))
    ->execute();

// Delete by ID
$affectedRows = $query->delete('users')
    ->where('id = ?', 5)
    ->execute();

// Check if delete was successful
$isDeleted = $query->delete('users')
    ->where('id = ?', 5)
    ->wasSuccessful();

COUNT Queries

// Create a Query instance
$query = new Query($executor);

// Count records
$activeUserCount = $query->count('users')
    ->where('status = ?', 'active')
    ->get();

// Check if records exist
$hasActiveUsers = $query->count('users')
    ->where('status = ?', 'active')
    ->exists();

Query Execution

The library provides a flexible mechanism for executing queries using the PDO executor or your own custom executor:

use Solo\QueryBuilder\Executors\PdoExecutor\PdoExecutor;
use Solo\QueryBuilder\Executors\PdoExecutor\Connection;
use Solo\QueryBuilder\Executors\PdoExecutor\Config;

// Configure PDO connection
$config = new Config(
    'localhost',     // host
    'db_user',       // username
    'db_password',   // password
    'database_name', // database
    'mysql',         // driver
    3306,            // port
    \PDO::FETCH_ASSOC // fetch mode
);

// Create connection and executor
$connection = new Connection($config);
$executor = new PdoExecutor($connection);

// Create query builder with executor
$query = new Query($executor);

// Execute query and get results
$users = $query->from('users')
    ->select('id', 'name')
    ->where('status = ?', 'active')
    ->get();

Query Caching

The library supports query caching to improve performance:

// Enable caching globally
\Solo\QueryBuilder\Factory\BuilderFactory::enableCache();

// Enable caching with custom TTL (30 minutes)
\Solo\QueryBuilder\Factory\BuilderFactory::enableCache(1800);

// Disable caching
\Solo\QueryBuilder\Factory\BuilderFactory::disableCache();

Extending Functionality

Adding Support for a New DBMS

To add support for a new DBMS:

1. Create a new grammar class inheriting from AbstractGrammar
2. Add the new DBMS to GrammarFactory

namespace Solo\QueryBuilder\Grammar;

final class CustomGrammar extends AbstractGrammar
{
    protected string $tableQuote = '`';
    protected string $columnQuote = '`';
    
    // Implement required methods
    public function compileSelect(string $table, array $columns, array $clauses): string
    {
        // Custom implementation
    }
    
    // Other required methods...
}

// Then update GrammarFactory

Requirements

• PHP 8.2 or higher
• PDO Extension (for database connections)

License

MIT