Query Builder

ZephyrPHP's query builder provides a fluent interface for building and executing database queries.

Basic Usage

Start a query from your model:

<?php

use App\Models\User;

$users = User::query()
    ->where('active', '=', true)
    ->all();

Selecting Data

Get All Records

// Get all users
$users = User::query()->all();

// Same as
$users = User::all();

Get First Record

$user = User::query()
    ->where('email', '=', 'john@example.com')
    ->first();

Find by ID

$user = User::find(1);

// Or via query
$user = User::query()
    ->where('id', '=', 1)
    ->first();

Where Clauses

Basic Where

$users = User::query()
    ->where('status', '=', 'active')
    ->all();

Multiple Conditions

// AND conditions (chained where)
$users = User::query()
    ->where('status', '=', 'active')
    ->where('role', '=', 'admin')
    ->all();

// All conditions must be true

Comparison Operators

// Equal
->where('status', '=', 'active')

// Not equal
->where('status', '!=', 'banned')

// Greater than
->where('age', '>', 18)

// Less than
->where('price', '<', 100)

// Greater than or equal
->where('quantity', '>=', 10)

// Less than or equal
->where('quantity', '<=', 100)

// LIKE
->where('name', 'LIKE', '%john%')

// IN
->where('status', 'IN', ['active', 'pending'])

// NOT IN
->where('role', 'NOT IN', ['banned', 'suspended'])

// IS NULL
->where('deleted_at', 'IS', null)

// IS NOT NULL
->where('email_verified_at', 'IS NOT', null)

Ordering

// Single order
$users = User::query()
    ->orderBy('name', 'ASC')
    ->all();

// Multiple orders
$users = User::query()
    ->orderBy('role', 'DESC')
    ->orderBy('name', 'ASC')
    ->all();

// Latest first (by created_at)
$users = User::query()
    ->orderBy('created_at', 'DESC')
    ->all();

Limiting Results

// Limit
$users = User::query()
    ->limit(10)
    ->all();

// Offset
$users = User::query()
    ->limit(10)
    ->offset(20)
    ->all();

// Get page 3 (10 per page)
$page = 3;
$perPage = 10;
$users = User::query()
    ->limit($perPage)
    ->offset(($page - 1) * $perPage)
    ->all();

Pagination

Built-in pagination support:

// 15 items per page
$result = User::query()
    ->where('active', '=', true)
    ->paginate(15);

// Access pagination data
$users = $result['items'];      // Array of users
$currentPage = $result['currentPage'];
$totalPages = $result['totalPages'];
$total = $result['total'];
$perPage = $result['perPage'];

Use in your controller:

public function index()
{
    $page = (int) request('page', 1);

    $result = User::query()
        ->where('active', '=', true)
        ->orderBy('name')
        ->paginate(15, $page);

    return $this->render('users/index', [
        'users' => $result['items'],
        'pagination' => $result
    ]);
}

Counting

// Count all records
$total = User::query()->count();

// Count with conditions
$activeCount = User::query()
    ->where('active', '=', true)
    ->count();

Aggregates

For aggregate functions, use the underlying Doctrine query builder:

use ZephyrPHP\Database\Database;

$db = Database::getInstance();
$qb = $db->getConnection()->createQueryBuilder();

// Sum
$total = $qb
    ->select('SUM(amount) as total')
    ->from('orders')
    ->where('status = ?')
    ->setParameter(0, 'completed')
    ->fetchOne();

// Average
$avg = $qb
    ->select('AVG(price) as average')
    ->from('products')
    ->fetchOne();

// Min/Max
$result = $qb
    ->select('MIN(price) as min_price, MAX(price) as max_price')
    ->from('products')
    ->fetchAssociative();

Combining Queries

// Complex query
$users = User::query()
    ->where('active', '=', true)
    ->where('role', '=', 'admin')
    ->where('created_at', '>', '2024-01-01')
    ->orderBy('name', 'ASC')
    ->limit(10)
    ->all();

Raw Doctrine Queries

For complex queries, use Doctrine's query builder directly:

use ZephyrPHP\Database\Database;

$db = Database::getInstance();
$qb = $db->getConnection()->createQueryBuilder();

$results = $qb
    ->select('u.*, COUNT(o.id) as order_count')
    ->from('users', 'u')
    ->leftJoin('u', 'orders', 'o', 'u.id = o.user_id')
    ->where('u.active = :active')
    ->groupBy('u.id')
    ->having('COUNT(o.id) > :min_orders')
    ->setParameter('active', true)
    ->setParameter('min_orders', 5)
    ->orderBy('order_count', 'DESC')
    ->fetchAllAssociative();

DQL Queries

Use Doctrine Query Language for object queries:

use ZephyrPHP\Database\Database;

$db = Database::getInstance();
$em = $db->getEntityManager();

$query = $em->createQuery(
    'SELECT u FROM App\Models\User u
     WHERE u.active = true
     ORDER BY u.createdAt DESC'
);

$users = $query->getResult();

// With parameters
$query = $em->createQuery(
    'SELECT u FROM App\Models\User u
     WHERE u.role = :role AND u.createdAt > :date'
);
$query->setParameter('role', 'admin');
$query->setParameter('date', new \DateTime('-30 days'));

$users = $query->getResult();