README

A lightweight, fluent SQL query builder for PHP, providing secure and intuitive database interactions.

✨ Features

• Fluent Interface: Chainable methods for building queries.
• CRUD Support: Quickly create, read, update, and delete.
• Secure Binding: Automatic placeholder handling to prevent SQL injection.
• Alias Parsing: Easy table aliasing (e.g., answers_services|a).
• Condition Groups: Complex WHERE clauses with closures.
• Join Clauses: INNER JOIN, LEFT JOIN, RIGHT JOIN support in SELECT and UPDATE.
• Raw SQL: Safely insert raw SQL snippets when needed.
• RawExpression Support: Use RawExpression objects in select fields for complex expressions.
• Select Bindings: Add parameters directly to the select statement for secure value binding.
• HAVING Support: Add HAVING clauses the same way you use WHERE.
• Field Mapping: Consistent field mapping for both search and orderBy operations across joins.
• Search Functionality: Easily implement search with keywords across fields and optional fields mapping when using joins.

📥 Installation

Install via Composer:

composer require solophp/query-builder

🚀 Usage

Initialization

use App\Core\QueryBuilder\QueryBuilder;
use Solo\Database;

$db = new Database(/* your config */);
$qb = new QueryBuilder($db);

📘 API Reference

Core Methods

Query Methods (SELECT)

Execution & Results

📚 Examples

SELECT

$results = $qb
    ->select(['id', 'title'])
    ->from('posts|p')
    ->where('p.created_at', '>', '2023-01-01')
    ->leftJoin('users|u', 'u.id = p.author_id')
    ->orderBy('p.id', 'DESC')
    ->limit(10)
    ->get();

SELECT With Bindings

$results = $qb
    ->select([
        'id', 
        'title', 
        'DATE_FORMAT(created_at, ?s) AS formatted_date'
    ], ['%Y-%m-%d'])
    ->from('posts')
    ->get();

Using RawExpressions

use Solo\Database\Expressions\RawExpression;

$results = $qb
    ->select([
        'u.id',
        'u.email',
        new RawExpression('CONCAT(u.first_name, " ", u.last_name) AS full_name'),
        new RawExpression('DATEDIFF(NOW(), u.created_at) AS days_registered')
    ])
    ->from('users|u')
    ->where('u.status', '=', 'active')
    ->get();

SELECT WITH JOIN

$results = $qb
    ->select(['u.id', 'u.name', 'p.phone'])
    ->from('users|u')
    ->leftJoin('profiles|p', 'p.user_id = u.id')
    ->where('u.status', '=', 'active')
    ->get();

INSERT

$qb->insert('posts')
    ->values([
        'title' => 'Hello World',
        'content' => 'Welcome to my blog!',
        'author_id' => 1
    ])
    ->execute();

UPDATE

$qb->update('posts')
    ->set(['title' => 'Updated Title'])
    ->where('id', '=', 5)
    ->execute();

UPDATE with JOIN

$qb->update('users AS u')
    ->innerJoin('profiles p', 'p.user_id = u.id')
    ->set(['u.status' => 'active', 'p.updated_at' => 'NOW()'])
    ->where('u.id', '=', 42)
    ->execute();

DELETE

$qb->delete('posts')
    ->where('id', 'IN', [4, 5, 6])
    ->execute();

Raw SQL

$qb->select()
    ->from('users')
    ->whereRaw('LENGTH(username) > ?i', [10])
    ->get();

Using WHERE

$qb->select()
    ->from('orders|o')
    ->whereGroup(function ($builder) {
        $builder->where('o.status', '=', 'pending')
                ->orWhere('o.payment_status', '=', 'failed');
    })
    ->get();

Using HAVING

$results = $qb
    ->select(['p.category', 'COUNT(p.id) AS total'])
    ->from('products|p')
    ->groupBy('p.category')
    ->having('total', '>', 10)
    ->get();

You can also chain multiple conditions, use andHavingGroup(), orHaving(), etc., just like with WHERE.

Complex Grouping with WHERE

$results = $qb->select()
    ->from('orders')
    ->whereGroup(function ($builder) {
        $builder->where('status', '=', 'pending')
                ->orWhere('priority', '>', 5);
    })
    ->get();

Get a specific field value from the first row

$email = $qb->select(['email'])
    ->from('users')
    ->where('id', '=', 42)
    ->getFieldValue('email');

Get an array of values for a specific field

$emails = $qb->select(['email'])
    ->from('users')
    ->where('status', '=', 'active')
    ->getFieldValues('email');

// Example result: ['user1@example.com', 'user2@example.com', ...]

Indexed Results

$users = $qb->select()
    ->from('users')
    ->getIndexedBy('id'); 
// Returns array keyed by user ID

Counting Records

$total = $qb->select()
    ->from('posts')
    ->where('status', '=', 'published')
    ->count();

Using Field Mapping

Search with field mapping:

$fieldMap = [
    'name' => 'p.name',
    'category' => 'c.name'
];

$results = $qb
    ->select(['p.*', 'c.name AS category_name'])
    ->from('products|p')
    ->join('categories|c', 'c.id = p.category_id')
    ->smartSearch('category:Electronics', ['name', 'category'], $fieldMap)
    ->get();

OrderBy with field mapping:

$fieldMap = [
    'name' => 'p.name',
    'price' => 'p.price',
    'category' => 'c.name'
];

$results = $qb
    ->select(['p.*', 'c.name AS category_name'])
    ->from('products|p')
    ->join('categories|c', 'c.id = p.category_id')
    ->smartSearch('Electronics', ['name', 'category'], $fieldMap)
    ->orderBy('price', 'DESC', $fieldMap)
    ->get();

Using smartSearch

Basic search across multiple fields:

$results = $qb
    ->select()
    ->from('products|p')
    ->smartSearch('laptop', ['p.name', 'p.description'])
    ->get();

Search in a specific field using colon syntax:

$results = $qb
    ->select()
    ->from('products|p')
    ->smartSearch('name:gaming laptop', ['p.name', 'p.description', 'p.category'])
    ->get();

Combined field mapping for search and sorting:

$fieldMap = [
    'category_name' => 'c.name',
    'product_name'  => 'p.name',
    'price'         => 'p.price'
];

$results = $qb
    ->select(['p.*', 'c.name AS category_name'])
    ->from('products|p')
    ->join('categories|c', 'c.id = p.category_id')
    ->smartSearch('category_name:Electronics', ['category_name', 'product_name'], $fieldMap)
    ->orderBy('price', 'DESC', $fieldMap)
    ->get();

The search functionality supports multiple keywords (space-separated) and will apply them with an AND condition.

✅ Requirements

• PHP 8.2+
• solophp/database for database connections

📄 License

MIT License. See LICENSE.