PHPackages                             monkeyscloud/monkeyslegion-query - 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. monkeyscloud/monkeyslegion-query

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

monkeyscloud/monkeyslegion-query
================================

Lightweight Query Builder & Micro‑ORM for MonkeysLegion

1.0.25(1mo ago)11.4k↑250%6MITPHPPHP ^8.4

Since Jul 23Pushed 1mo agoCompare

[ Source](https://github.com/MonkeysCloud/MonkeysLegion-Query)[ Packagist](https://packagist.org/packages/monkeyscloud/monkeyslegion-query)[ RSS](/packages/monkeyscloud-monkeyslegion-query/feed)WikiDiscussions main Synced 1mo ago

READMEChangelog (2)Dependencies (10)Versions (33)Used By (6)

MonkeysLegion Query Builder
===========================

[](#monkeyslegion-query-builder)

A **powerful, fluent Query Builder & Micro-ORM** for PHP 8.4+, designed for the MonkeysLegion framework. Built on PDO with zero external dependencies, providing a clean, expressive API for database operations.

[![PHP Version](https://camo.githubusercontent.com/bfb98d885e37493cddcc01059ebf02a8872de9da37c12691b8bb6d13fcdca735/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f5048502d382e342532422d626c75652e737667)](https://www.php.net/)[![License](https://camo.githubusercontent.com/8bb50fd2278f18fc326bf71f6e88ca8f884f72f179d3e555e20ed30157190d0d/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f6c6963656e73652d4d49542d677265656e2e737667)](LICENSE)

✨ Features
----------

[](#-features)

- 🔗 **Fluent Query Builder** - Chainable, expressive API
- 🛡️ **SQL Injection Protection** - Automatic parameter binding
- 🔄 **Transaction Support** - Full ACID compliance with savepoints
- 🎯 **Multiple Database Support** - MySQL, PostgreSQL, SQLite
- 📊 **Advanced Queries** - Joins, subqueries, unions, CTEs
- 🏗️ **Repository Pattern** - Built-in entity repository support
- ⚡ **Performance Optimized** - Chunking, streaming, pagination
- 🎨 **Clean Code** - PSR-12 compliant, fully typed

---

📦 Installation
--------------

[](#-installation)

```
composer require monkeyscloud/monkeyslegion-query
```

Or add to your `composer.json`:

```
{
    "require": {
        "monkeyscloud/monkeyslegion-query": "^1.0"
    },
    "autoload": {
        "psr-4": {
            "MonkeysLegion\\Query\\": "src/Query/",
            "MonkeysLegion\\Repository\\": "src/Repository/"
        }
    }
}
```

---

🚀 Quick Start
-------------

[](#-quick-start)

```
use MonkeysLegion\Database\MySQL\Connection;
use MonkeysLegion\Query\QueryBuilder;

// Initialize connection
$conn = new Connection([
    'dsn' => 'mysql:host=localhost;dbname=myapp',
    'username' => 'root',
    'password' => 'secret'
]);

// Create query builder
$qb = new QueryBuilder($conn);

// Simple query
$users = $qb->from('users')
    ->where('status', '=', 'active')
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->fetchAll();

// With joins
$posts = $qb->from('posts', 'p')
    ->leftJoin('users', 'u', 'u.id', '=', 'p.user_id')
    ->leftJoin('categories', 'c', 'c.id', '=', 'p.category_id')
    ->select(['p.*', 'u.name as author', 'c.name as category'])
    ->where('p.published', '=', true)
    ->fetchAll();
```

---

📚 Table of Contents
-------------------

[](#-table-of-contents)

- [Select Operations](#select-operations)
- [Where Clauses](#where-clauses)
- [Joins](#joins)
- [Grouping & Ordering](#grouping--ordering)
- [Aggregate Functions](#aggregate-functions)
- [Insert, Update, Delete](#insert-update-delete)
- [Fetch Operations](#fetch-operations)
- [Transactions](#transactions)
- [Advanced Features](#advanced-features)
- [Repository Pattern](#repository-pattern)
- [Using Observers](#using-observers)

---

🔍 Select Operations
-------------------

[](#-select-operations)

### Basic SELECT

[](#basic-select)

```
// Select all columns
$users = $qb->from('users')->fetchAll();

// Select specific columns
$users = $qb->from('users')
    ->select(['id', 'name', 'email'])
    ->fetchAll();

// Select with alias
$users = $qb->from('users')
    ->selectAs('created_at', 'registered_date')
    ->fetchAll();

// Add columns to existing SELECT
$qb->select(['id', 'name'])
   ->addSelect(['email', 'phone']);
```

### SELECT with Expressions

[](#select-with-expressions)

```
// Raw expressions
$qb->selectRaw('COUNT(*) as total, DATE(created_at) as date');

// Aggregate shortcuts
$qb->from('orders')
   ->selectSum('amount', 'total')
   ->selectAvg('quantity', 'avg_qty')
   ->selectMax('price', 'max_price');

// CASE statements
$qb->selectCase([
    'status = "active"' => '"Active"',
    'status = "pending"' => '"Pending"'
], '"Unknown"', 'status_label');

// CONCAT
$qb->selectConcat(['first_name', 'last_name'], 'full_name', ' ');

// JSON extraction (MySQL 5.7+)
$qb->selectJson('settings', '$.theme', 'user_theme');
```

### Subqueries in SELECT

[](#subqueries-in-select)

```
// Using callback
$qb->from('users', 'u')
   ->selectSubQuery(function($sub) {
       $sub->from('orders')
           ->selectRaw('COUNT(*)')
           ->whereRaw('orders.user_id = u.id');
   }, 'order_count');

// Raw subquery
$qb->selectSub('SELECT COUNT(*) FROM orders WHERE user_id = users.id', 'order_count');
```

### DISTINCT

[](#distinct)

```
// Regular DISTINCT
$qb->from('users')->distinct()->select(['country']);

// DISTINCT ON (PostgreSQL)
$qb->from('events')->distinctOn(['user_id'])->orderBy('created_at', 'DESC');
```

---

🎯 Where Clauses
---------------

[](#-where-clauses)

### Basic WHERE

[](#basic-where)

```
// Simple where
$qb->where('status', '=', 'active');
$qb->where('age', '>', 18);

// Multiple conditions (AND)
$qb->where('status', '=', 'active')
   ->where('verified', '=', true);

// OR conditions
$qb->where('role', '=', 'admin')
   ->orWhere('role', '=', 'moderator');

// AND/OR combined
$qb->where('status', '=', 'active')
   ->andWhere('age', '>=', 18)
   ->orWhere('role', '=', 'admin');
```

### Advanced WHERE

[](#advanced-where)

```
// WHERE IN
$qb->whereIn('id', [1, 2, 3, 4, 5]);
$qb->whereNotIn('status', ['deleted', 'banned']);

// WHERE BETWEEN
$qb->whereBetween('age', 18, 65);
$qb->whereNotBetween('price', 100, 200);

// WHERE NULL
$qb->whereNull('deleted_at');
$qb->whereNotNull('verified_at');

// WHERE LIKE
$qb->whereLike('email', '%@gmail.com');
$qb->whereNotLike('name', '%test%');

// Column comparisons
$qb->whereColumn('updated_at', '>', 'created_at');

// WHERE EXISTS
$qb->whereExists('SELECT 1 FROM orders WHERE orders.user_id = users.id');
```

### Grouped WHERE

[](#grouped-where)

```
// WHERE groups with AND
$qb->where('status', '=', 'active')
   ->whereGroup(function($q) {
       $q->where('role', '=', 'admin')
         ->orWhere('role', '=', 'moderator');
   });
// Produces: WHERE status = 'active' AND (role = 'admin' OR role = 'moderator')

// OR WHERE groups
$qb->where('age', '>=', 18)
   ->orWhereGroup(function($q) {
       $q->where('parent_consent', '=', true)
         ->where('guardian_id', '!=', null);
   });
```

### Date/Time WHERE

[](#datetime-where)

```
// WHERE DATE
$qb->whereDate('created_at', '=', '2024-01-01');

// WHERE YEAR/MONTH/DAY
$qb->whereYear('created_at', '=', 2024);
$qb->whereMonth('created_at', '=', 1);
$qb->whereDay('created_at', '=', 15);
```

### JSON WHERE (MySQL 5.7+)

[](#json-where-mysql-57)

```
// JSON contains
$qb->whereJsonContains('meta', '$.tags', 'php');

// JSON extract
$qb->whereJsonExtract('settings', '$.theme', '=', 'dark');

// JSON length
$qb->whereJsonLength('tags', '>', 3);
```

### Raw WHERE

[](#raw-where)

```
$qb->whereRaw('YEAR(created_at) = ?', [2024]);
$qb->orWhereRaw('status IN (?, ?)', ['active', 'verified']);
```

---

🔗 Joins
-------

[](#-joins)

### Basic Joins

[](#basic-joins)

```
// INNER JOIN
$qb->from('posts', 'p')
   ->innerJoin('users', 'u', 'u.id', '=', 'p.user_id');

// LEFT JOIN
$qb->from('users', 'u')
   ->leftJoin('profiles', 'p', 'p.user_id', '=', 'u.id');

// RIGHT JOIN
$qb->rightJoin('orders', 'o', 'o.user_id', '=', 'u.id');

// CROSS JOIN
$qb->crossJoin('settings', 's');
```

### Multiple Conditions

[](#multiple-conditions)

```
// Using callback
$qb->from('orders', 'o')
   ->leftJoinOn('items', 'i', function($join) {
       $join->on('i.order_id', '=', 'o.id')
            ->andOn('i.deleted_at', 'IS', 'NULL')
            ->where('i.quantity', '>', 0, $this);
   });
```

### Subquery Joins

[](#subquery-joins)

```
// Join to subquery
$qb->from('users', 'u')
   ->leftJoinSubQuery(function($sub) {
       $sub->from('orders')
           ->select(['user_id', 'COUNT(*) as order_count'])
           ->groupBy('user_id');
   }, 'oc', 'oc.user_id', '=', 'u.id');
```

### USING Joins

[](#using-joins)

```
// When column names match
$qb->from('posts', 'p')
   ->leftJoinUsing('categories', 'c', 'category_id');
```

### Self Joins

[](#self-joins)

```
// Join table to itself
$qb->from('categories', 'c')
   ->leftSelfJoin('parent', 'parent.id', '=', 'c.parent_id');
```

### Lateral Joins (PostgreSQL)

[](#lateral-joins-postgresql)

```
$qb->from('users', 'u')
   ->leftJoinLateral(
       'SELECT * FROM posts WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3',
       'recent_posts'
   );
```

---

📊 Grouping & Ordering
-------------------------

[](#-grouping--ordering)

### GROUP BY

[](#group-by)

```
$qb->from('orders')
   ->select(['user_id', 'COUNT(*) as order_count'])
   ->groupBy('user_id');

// Multiple columns
$qb->groupBy('year', 'month', 'day');
```

### HAVING

[](#having)

```
$qb->from('orders')
   ->select(['user_id', 'COUNT(*) as total'])
   ->groupBy('user_id')
   ->having('COUNT(*)', '>', 5);

// Raw HAVING
$qb->havingRaw('SUM(amount) > ?', [1000]);
```

### ORDER BY

[](#order-by)

```
// Single column
$qb->orderBy('created_at', 'DESC');

// Multiple columns
$qb->orderBy('status', 'ASC')
   ->orderBy('priority', 'DESC')
   ->orderBy('created_at', 'DESC');

// Raw ORDER BY
$qb->orderByRaw('FIELD(status, "urgent", "high", "normal", "low")');
$qb->orderByRaw('RAND()'); // Random order
```

### LIMIT & OFFSET

[](#limit--offset)

```
$qb->limit(10)->offset(20); // Skip 20, take 10
$qb->limit(5); // First 5 rows
```

---

📈 Aggregate Functions
---------------------

[](#-aggregate-functions)

### Basic Aggregates

[](#basic-aggregates)

```
// COUNT
$total = $qb->from('users')->count();
$active = $qb->from('users')->where('status', '=', 'active')->count();

// SUM
$revenue = $qb->from('orders')->sum('amount');

// AVG
$avgPrice = $qb->from('products')->avg('price');

// MIN/MAX
$minPrice = $qb->from('products')->min('price');
$maxPrice = $qb->from('products')->max('price');
```

### Distinct Aggregates

[](#distinct-aggregates)

```
$uniqueCountries = $qb->from('users')->countDistinct('country');
$uniqueRevenue = $qb->from('orders')->sumDistinct('amount');
```

### Statistical Functions

[](#statistical-functions)

```
// Standard deviation
$stdDev = $qb->from('sales')->stdDev('amount');
$stdDevPop = $qb->from('sales')->stdDevPop('amount');

// Variance
$variance = $qb->from('sales')->variance('amount');
$varPop = $qb->from('sales')->varPop('amount');
```

### Conditional Aggregates

[](#conditional-aggregates)

```
// Count with condition
$activeCount = $qb->from('users')->countWhere('status', '=', 'active');

// Sum with condition
$activeRevenue = $qb->from('orders')->sumWhere('amount', 'status', '=', 'paid');
```

### Existence Checks

[](#existence-checks)

```
$exists = $qb->from('users')->where('email', '=', 'admin@example.com')->exists();
$doesntExist = $qb->from('users')->where('id', '=', 999)->doesntExist();
```

### GROUP\_CONCAT (MySQL)

[](#group_concat-mysql)

```
$tags = $qb->from('post_tags')
    ->where('post_id', '=', 1)
    ->groupConcat('tag_name', ', ', true); // Distinct, comma-separated
```

---

✏️ Insert, Update, Delete
-------------------------

[](#️-insert-update-delete)

### INSERT

[](#insert)

```
// Single insert
$userId = $qb->insert('users', [
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'status' => 'active'
]);

// Batch insert
$count = $qb->insertBatch('users', [
    ['name' => 'Alice', 'email' => 'alice@example.com'],
    ['name' => 'Bob', 'email' => 'bob@example.com'],
    ['name' => 'Carol', 'email' => 'carol@example.com']
]);
```

### UPDATE

[](#update)

```
// Update with WHERE
$affected = $qb->update('users', [
        'status' => 'inactive',
        'updated_at' => date('Y-m-d H:i:s')
    ])
    ->where('last_login', '', 10);

// WHERE subquery
$qb->from('users')
   ->whereExists(
       'SELECT 1 FROM orders WHERE orders.user_id = users.id AND orders.status = ?',
       ['completed']
   );
```

### UNION

[](#union)

```
$qb->from('customers')
   ->select(['id', 'name', '"customer" as type'])
   ->union(
       'SELECT id, name, "supplier" as type FROM suppliers',
       [],
       all: false
   );
```

### Raw Queries

[](#raw-queries)

```
// Execute raw query
$results = $qb->raw('SELECT * FROM users WHERE created_at > ?', ['2024-01-01']);

// Raw query with single result
$user = $qb->rawOne('SELECT * FROM users WHERE id = ?', [1]);
```

### Custom SQL

[](#custom-sql)

```
// Execute custom SQL with query builder features
$qb->custom('SELECT * FROM users')
   ->where('status', '=', 'active')
   ->orderBy('created_at', 'DESC')
   ->fetchAll();
```

### Query Introspection

[](#query-introspection)

```
// Get generated SQL
$sql = $qb->from('users')->where('id', '=', 1)->toSql();

// Get bound parameters
$params = $qb->getParams();

// Debug query
$qb->from('users')->where('id', '=', 1)->dump(); // Prints debug info
$qb->from('users')->where('id', '=', 1)->dd();   // Dump and die
```

### Conditional Building

[](#conditional-building)

```
// Conditional clauses
$qb->from('users')
   ->when($isAdmin, fn($q) => $q->select('*'))
   ->unless($isAdmin, fn($q) => $q->select(['id', 'name']))
   ->where('active', '=', true);

// Conditional joins
$qb->from('posts')
   ->leftJoinWhen($includeAuthor, 'users', 'u', 'u.id', '=', 'posts.user_id');
```

### Query Duplication

[](#query-duplication)

```
// Clone query for reuse
$baseQuery = $qb->from('users')->where('status', '=', 'active');

$admins = $baseQuery->clone()->where('role', '=', 'admin')->fetchAll();
$users = $baseQuery->clone()->where('role', '=', 'user')->fetchAll();
```

### Macros (Custom Methods)

[](#macros-custom-methods)

```
// Register custom macro
QueryBuilder::macro('whereDateRange', function($column, $start, $end) {
    return $this->whereBetween($column, $start, $end);
});

// Use macro
$qb->from('orders')->whereDateRange('created_at', '2024-01-01', '2024-12-31');
```

---

🏗️ Repository Pattern
---------------------

[](#️-repository-pattern)

### Creating a Repository

[](#creating-a-repository)

```
namespace App\Repository;

use MonkeysLegion\Repository\EntityRepository;
use App\Entity\User;

class UserRepository extends EntityRepository
{
    protected string $table = 'users';
    protected string $entityClass = User::class;

    // Custom methods
    public function findActive(): array
    {
        return $this->findBy(['status' => 'active']);
    }

    public function findByEmail(string $email): ?User
    {
        return $this->findOneBy(['email' => $email]);
    }

    public function getAdmins(): array
    {
        return $this->qb
            ->from($this->table)
            ->where('role', '=', 'admin')
            ->orderBy('name', 'ASC')
            ->fetchAll($this->entityClass);
    }
}
```

### Built-in Repository Methods

[](#built-in-repository-methods)

```
$userRepo = new UserRepository($qb);

// Find all
$users = $userRepo->findAll();

// Find by ID
$user = $userRepo->find(1);

// Find by criteria
$users = $userRepo->findBy(
    ['status' => 'active', 'verified' => true],
    ['created_at' => 'DESC'],
    limit: 10,
    offset: 0
);

// Find one by criteria
$user = $userRepo->findOneBy(['email' => 'admin@example.com']);

// Count
$total = $userRepo->count();
$active = $userRepo->count(['status' => 'active']);

// Save (insert or update)
$userId = $userRepo->save($user);

// Delete
$affected = $userRepo->delete(1);
```

### Repository Factory

[](#repository-factory)

```
namespace MonkeysLegion\Repository;

use MonkeysLegion\Query\QueryBuilder;

class RepositoryFactory
{
    public function __construct(private QueryBuilder $qb) {}

    /**
     * @template T of EntityRepository
     * @param class-string $repoClass
     * @return T
     */
    public function create(string $repoClass): object
    {
        return new $repoClass($this->qb);
    }
}

// Usage
$factory = new RepositoryFactory($qb);
$userRepo = $factory->create(UserRepository::class);
```

### Dependency Injection Setup

[](#dependency-injection-setup)

```
// In your DI container config
use MonkeysLegion\Database\Factory\ConnectionFactory;
use MonkeysLegion\Query\QueryBuilder;
use MonkeysLegion\Repository\RepositoryFactory;

return [
    Connection::class => fn() => ConnectionFactory::create(require __DIR__.'/database.php'),

    QueryBuilder::class => fn($c) => new QueryBuilder(
        $c->get(Connection::class)
    ),

    RepositoryFactory::class => fn($c) => new RepositoryFactory(
        $c->get(QueryBuilder::class)
    ),

    // Individual repositories
    UserRepository::class => fn($c) => new UserRepository(
        $c->get(QueryBuilder::class)
    ),
];
```

---

👀 Using Observers
-----------------

[](#-using-observers)

Observers allow you to hook into the lifecycle events of your entities. You can attach an observer to an entity class using the `#[ObservedBy]` attribute.

### 1. Create an Observer

[](#1-create-an-observer)

Extend the `EntityObserver` base class and override the methods you need:

```
use MonkeysLegion\Entity\Observers\EntityObserver;

class UserObserver extends EntityObserver
{
    public function creating(object $entity): void
    {
        // Set default values or hash passwords
        echo "Creating user: " . $entity->getUsername();
    }

    public function hydrated(object $entity): void
    {
        // Perform actions after the entity is loaded from the database
        echo "User hydrated!";
    }
}
```

### 2. Register the Observer on the Entity

[](#2-register-the-observer-on-the-entity)

You can register a single observer or an array of observers:

```
use MonkeysLegion\Entity\Attributes\Entity;
use MonkeysLegion\Entity\Attributes\ObservedBy;

#[Entity(table: 'users')]
#[ObservedBy(UserObserver::class)] // Single observer
class User
{
    // ...
}

#[Entity(table: 'posts')]
#[ObservedBy([PostObserver::class, ActivityLogObserver::class])] // Multiple observers
class Post
{
    // ...
}
```

### Observer Lifecycle Events

[](#observer-lifecycle-events)

The following table describes when each observer method is triggered by the `EntityRepository`:

EventTriggered byWhen exactly?**saving**`save()`Triggered before an insert or update begins.**creating**`save()`Triggered before a record is inserted.**created**`save()`Triggered after a record is successfully inserted.**updating**`save()`Triggered before an existing record is updated.**updated**`save()`Triggered after an existing record is successfully changed.**saved**`save()`Triggered after the save operation (after `created` or `updated`).**deleting**`delete()`Triggered before a record is deleted.**deleted**`delete()`Triggered after a record is successfully deleted.**hydrated**entity hydratorAutomatically triggered after an entity is loaded and hydrated.All details about observers can be found in the [Observers documentation](https://monkeyslegion.com/docs/packages/entity).

Tip

The **updated** event is only triggered if the database update resulted in at least one changed row (rowCount > 0). The **saved** event is always triggered regardless of actual differences.

---

🎨 Best Practices
----------------

[](#-best-practices)

### 1. Always Use Parameter Binding

[](#1-always-use-parameter-binding)

```
// ❌ BAD - SQL Injection risk
$qb->whereRaw("email = '{$email}'");

// ✅ GOOD - Safe parameter binding
$qb->where('email', '=', $email);
$qb->whereRaw('email = ?', [$email]);
```

### 2. Use Transactions for Related Operations

[](#2-use-transactions-for-related-operations)

```
// ✅ GOOD - Atomic operations
$qb->transaction(function($qb) use ($orderData, $items) {
    $orderId = $qb->insert('orders', $orderData);

    foreach ($items as $item) {
        $item['order_id'] = $orderId;
        $qb->insert('order_items', $item);
    }

    return $orderId;
});
```

### 3. Use Repositories for Business Logic

[](#3-use-repositories-for-business-logic)

```
// ✅ GOOD - Encapsulated logic
class OrderRepository extends EntityRepository
{
    public function createOrder(array $orderData, array $items): int
    {
        return $this->qb->transaction(function($qb) use ($orderData, $items) {
            $orderId = $qb->insert('orders', $orderData);

            foreach ($items as $item) {
                $item['order_id'] = $orderId;
                $qb->insert('order_items', $item);
            }

            return $orderId;
        });
    }
}
```

### 4. Use Chunking for Large Datasets

[](#4-use-chunking-for-large-datasets)

```
// ✅ GOOD - Memory efficient
$qb->from('users')->chunk(1000, function($users) {
    foreach ($users as $user) {
        // Process user
    }
});

// ❌ BAD - Loads all into memory
$users = $qb->from('users')->fetchAll();
```

### 5. Clone Queries for Reuse

[](#5-clone-queries-for-reuse)

```
// ✅ GOOD - Reusable base query
$activeUsers = $qb->from('users')->where('status', '=', 'active');

$admins = $activeUsers->clone()->where('role', '=', 'admin')->fetchAll();
$regular = $activeUsers->clone()->where('role', '=', 'user')->fetchAll();
```

---

🔒 Security
----------

[](#-security)

### SQL Injection Protection

[](#sql-injection-protection)

MonkeysLegion Query Builder automatically protects against SQL injection through:

1. **Automatic parameter binding** - All values are bound as PDO parameters
2. **Unique placeholder generation** - Prevents parameter collision
3. **Identifier quoting** - Table and column names are properly escaped

```
// All of these are safe
$qb->where('email', '=', $userInput);
$qb->whereIn('id', $arrayFromUser);
$qb->whereLike('name', $searchTerm);
```

### Safe Raw Queries

[](#safe-raw-queries)

When using raw SQL, always use parameter binding:

```
// ✅ SAFE
$qb->whereRaw('YEAR(created_at) = ?', [2024]);
$qb->selectRaw('COUNT(CASE WHEN status = ? THEN 1 END) as count', ['active']);

// ❌ UNSAFE
$qb->whereRaw("YEAR(created_at) = {$year}"); // Don't do this!
```

---

⚡ Performance Tips
------------------

[](#-performance-tips)

### 1. Use Indexes

[](#1-use-indexes)

```
// Ensure WHERE, JOIN, and ORDER BY columns are indexed
$qb->from('users')
   ->where('email', '=', $email)  // email should be indexed
   ->orderBy('created_at', 'DESC'); // created_at should be indexed
```

### 2. Select Only Needed Columns

[](#2-select-only-needed-columns)

```
// ✅ GOOD
$qb->select(['id', 'name', 'email']);

// ❌ BAD (if you don't need all columns)
$qb->select('*');
```

### 3. Use EXISTS Instead of COUNT

[](#3-use-exists-instead-of-count)

```
// ✅ FASTER for existence checks
$exists = $qb->from('users')->where('email', '=', $email)->exists();

// ❌ SLOWER
$exists = $qb->from('users')->where('email', '=', $email)->count() > 0;
```

### 4. Eager Load Relationships

[](#4-eager-load-relationships)

```
// ✅ GOOD - Single query with joins
$posts = $qb->from('posts', 'p')
    ->leftJoin('users', 'u', 'u.id', '=', 'p.user_id')
    ->select(['p.*', 'u.name as author_name'])
    ->fetchAll();

// ❌ BAD - N+1 query problem
$posts = $qb->from('posts')->fetchAll();
foreach ($posts as $post) {
    $post->author = $qb->from('users')->find($post->user_id); // N queries!
}
```

### 5. Use Pagination for Large Results

[](#5-use-pagination-for-large-results)

```
// ✅ GOOD
$result = $qb->from('posts')->paginate(1, 20);

// ❌ BAD - Loads all rows
$all = $qb->from('posts')->fetchAll();
```

---

🐛 Debugging
-----------

[](#-debugging)

### Query Debugging

[](#query-debugging)

```
// Print query and continue
$qb->from('users')->where('id', '=', 1)->dump();

// Print query and exit
$qb->from('users')->where('id', '=', 1)->dd();

// Log query
$qb->from('users')->where('id', '=', 1)->log('[UserQuery]');

// Get SQL and params
$sql = $qb->toSql();
$params = $qb->getParams();
```

### Enable PDO Error Mode

[](#enable-pdo-error-mode)

```
use MonkeysLegion\Database\MySQL\Connection;
$conn = new Connection([
    'dsn' => 'mysql:host=localhost;dbname=myapp',
    'username' => 'root',
    'password' => 'secret',
    'options' => [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
]);
```

---

🧪 Testing
---------

[](#-testing)

### Example PHPUnit Test

[](#example-phpunit-test)

```
use PHPUnit\Framework\TestCase;
use MonkeysLegion\Query\QueryBuilder;

class UserRepositoryTest extends TestCase
{
    private QueryBuilder $qb;

    protected function setUp(): void
    {
        $this->qb = new QueryBuilder($this->createTestConnection());
        $this->qb->beginTransaction();
    }

    protected function tearDown(): void
    {
        $this->qb->rollback();
    }

    public function testFindUser(): void
    {
        $userId = $this->qb->insert('users', [
            'name' => 'Test User',
            'email' => 'test@example.com'
        ]);

        $user = $this->qb->from('users')->find($userId);

        $this->assertEquals('Test User', $user['name']);
        $this->assertEquals('test@example.com', $user['email']);
    }
}
```

---

📖 API Reference
---------------

[](#-api-reference)

### Complete Method List

[](#complete-method-list)

#### Select Operations

[](#select-operations)

- `select()`, `addSelect()`, `selectAs()`, `selectRaw()`
- `selectSum()`, `selectAvg()`, `selectMin()`, `selectMax()`, `selectCount()`
- `selectConcat()`, `selectCoalesce()`, `selectCase()`, `selectJson()`
- `distinct()`, `distinctOn()`

#### Where Clauses

[](#where-clauses)

- `where()`, `andWhere()`, `orWhere()`, `whereRaw()`
- `whereIn()`, `whereNotIn()`, `orWhereIn()`, `orWhereNotIn()`
- `whereBetween()`, `whereNotBetween()`, `orWhereBetween()`
- `whereNull()`, `whereNotNull()`, `orWhereNull()`, `orWhereNotNull()`
- `whereLike()`, `whereNotLike()`, `orWhereLike()`
- `whereExists()`, `whereNotExists()`, `orWhereExists()`
- `whereColumn()`, `orWhereColumn()`
- `whereDate()`, `whereYear()`, `whereMonth()`, `whereDay()`, `whereTime()`
- `whereJsonContains()`, `whereJsonExtract()`, `whereJsonLength()`
- `whereGroup()`, `orWhereGroup()`, `andWhereGroup()`

#### Joins

[](#joins)

- `join()`, `innerJoin()`, `leftJoin()`, `rightJoin()`, `crossJoin()`
- `fullOuterJoin()`, `leftOuterJoin()`, `rightOuterJoin()`
- `joinOn()`, `innerJoinOn()`, `leftJoinOn()`, `rightJoinOn()`
- `joinSub()`, `leftJoinSub()`, `rightJoinSub()`, `joinSubQuery()`
- `joinUsing()`, `innerJoinUsing()`, `leftJoinUsing()`, `rightJoinUsing()`
- `naturalJoin()`, `naturalLeftJoin()`, `naturalRightJoin()`
- `joinLateral()`, `leftJoinLateral()`, `innerJoinLateral()`
- `selfJoin()`, `leftSelfJoin()`

#### Grouping & Ordering

[](#grouping--ordering)

- `groupBy()`, `having()`, `havingRaw()`
- `orderBy()`, `orderByRaw()`
- `limit()`, `offset()`

#### Aggregates

[](#aggregates)

- `count()`, `countDistinct()`, `countWhere()`
- `sum()`, `sumDistinct()`, `sumWhere()`
- `avg()`, `avgDistinct()`
- `min()`, `max()`
- `stdDev()`, `stdDevPop()`, `stdDevSamp()`
- `variance()`, `varPop()`, `varSamp()`
- `groupConcat()`
- `exists()`, `doesntExist()`

#### DML Operations

[](#dml-operations)

- `insert()`, `insertBatch()`
- `update()`, `delete()`
- `execute()`, `executeRaw()`

#### Fetch Operations

[](#fetch-operations)

- `fetchAll()`, `fetchAllAssoc()`, `fetchAllObjects()`
- `fetch()`, `first()`, `firstAs()`, `firstOrFail()`
- `find()`, `findOrFail()`, `findMany()`
- `value()`, `pluck()`, `fetchPairs()`, `fetchIndexed()`, `fetchGrouped()`
- `chunk()`, `cursor()`, `cursorAs()`, `each()`, `lazy()`
- `paginate()`, `simplePaginate()`
- `map()`, `filter()`, `reduce()`

#### Transactions

[](#transactions)

- `beginTransaction()`, `commit()`, `rollback()`
- `transaction()`, `safeTransaction()`, `transactionWithRetry()`
- `beginTransactionNested()`, `commitNested()`, `rollbackNested()`
- `savepoint()`, `rollbackToSavepoint()`, `releaseSavepoint()`
- `setTransactionIsolation()`, `readCommitted()`, `repeatableRead()`, `serializable()`
- `getLock()`, `releaseLock()`, `withLock()`

#### Utilities

[](#utilities)

- `from()`, `fromSub()`, `fromSubQuery()`
- `duplicate()`, `clone()`, `reset()`, `fresh()`
- `toSql()`, `getParams()`, `dump()`, `dd()`, `log()`
- `when()`, `unless()`, `tap()`

---

📝 License
---------

[](#-license)

MIT License - see [LICENSE](LICENSE) file for details

---

🤝 Contributing
--------------

[](#-contributing)

Contributions are welcome! Please feel free to submit a Pull Request.

1. Fork the repository
2. Create your feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add some amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request

---

📮 Support
---------

[](#-support)

- **Documentation**:
- **Issues**:
- **Slack**: [https://join.slack.com/t/monkeyslegion/shared\_invite/zt-36jut3kqo-WCwOabVrVrhHBln4xhMATA](https://join.slack.com/t/monkeyslegion/shared_invite/zt-36jut3kqo-WCwOabVrVrhHBln4xhMATA)

---

🙏 Credits
---------

[](#-credits)

Created and maintained by [MonkeysCloud](https://github.com/monkeyscloud)

---

**Built with ❤️ by the MonkeysLegion team**

Contributors
------------

[](#contributors)

   [ ![Jorge Peraza](https://github.com/yorchperaza.png)
 **Jorge Peraza** ](https://github.com/yorchperaza)   [ ![Amanar Marouane](https://github.com/Amanar-Marouane.png)
 **Amanar Marouane** ](https://github.com/Amanar-Marouane)

###  Health Score

51

—

FairBetter than 96% of packages

Maintenance89

Actively maintained with recent releases

Popularity23

Limited adoption so far

Community16

Small or concentrated contributor base

Maturity65

Established project with proven stability

 Bus Factor1

Top contributor holds 77.9% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~9 days

Recently: every ~1 days

Total

26

Last Release

59d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/51e4df19377776baa8eafb605d9e7d2374b855c686f552c20d6856e94e3597c3?d=identicon)[yorchperaza](/maintainers/yorchperaza)

---

Top Contributors

[![yorchperaza](https://avatars.githubusercontent.com/u/2913369?v=4)](https://github.com/yorchperaza "yorchperaza (67 commits)")[![Amanar-Marouane](https://avatars.githubusercontent.com/u/155680356?v=4)](https://github.com/Amanar-Marouane "Amanar-Marouane (19 commits)")

---

Tags

open-sourcephpphp-libraryquery

###  Code Quality

TestsPHPUnit

Static AnalysisPHPStan

Code StylePHP\_CodeSniffer

Type Coverage Yes

### Embed Badge

![Health badge](/badges/monkeyscloud-monkeyslegion-query/health.svg)

```
[![Health](https://phpackages.com/badges/monkeyscloud-monkeyslegion-query/health.svg)](https://phpackages.com/packages/monkeyscloud-monkeyslegion-query)
```

###  Alternatives

[doctrine/orm

Object-Relational-Mapper for PHP

10.2k285.3M6.2k](/packages/doctrine-orm)[jdorn/sql-formatter

a PHP SQL highlighting library

3.9k115.1M102](/packages/jdorn-sql-formatter)[illuminate/database

The Illuminate Database package.

2.8k52.4M9.4k](/packages/illuminate-database)[ramsey/uuid-doctrine

Use ramsey/uuid as a Doctrine field type.

90440.3M211](/packages/ramsey-uuid-doctrine)[reliese/laravel

Reliese Components for Laravel Framework code generation.

1.7k3.4M16](/packages/reliese-laravel)[wildside/userstamps

Laravel Userstamps provides an Eloquent trait which automatically maintains `created\_by` and `updated\_by` columns on your model, populated by the currently authenticated user in your application.

7511.7M13](/packages/wildside-userstamps)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)