Query Builder Macros

Query Builder Macros allow you to extend QueryBuilder with custom methods. This feature enables you to create reusable query logic that can be called as methods on QueryBuilder instances, making your code more expressive and maintainable.

Overview
Registering Macros
Using Macros
Macro Parameters
Return Values
Examples
Best Practices
Differences from Scopes

Overview

Macros provide a way to register custom methods that can be called on QueryBuilder instances. They are registered globally and can be used with any QueryBuilder instance, making them ideal for:

Custom query methods: Create domain-specific query methods (e.g., active(), published())
Reusable query patterns: Encapsulate common query logic
Method chaining: Macros can return QueryBuilder instances for fluent chaining
Flexible returns: Macros can return QueryBuilder instances or any other value

Registering Macros

Macros are registered using the static macro() method on QueryBuilder:

use tommyknocker\pdodb\query\QueryBuilder;

QueryBuilder::macro('active', function (QueryBuilder $query) {
    return $query->where('status', 'active');
});

The macro callable receives the QueryBuilder instance as the first argument, followed by any additional arguments passed when calling the macro.

Using Macros

Once registered, macros can be called as methods on QueryBuilder instances:

$activeProducts = $db->find()
    ->table('products')
    ->active()
    ->get();

Macros can be chained with other QueryBuilder methods:

$result = $db->find()
    ->table('products')
    ->active()
    ->orderBy('created_at', 'DESC')
    ->limit(10)
    ->get();

Checking if Macro Exists

You can check if a macro is registered using hasMacro():

if (QueryBuilder::hasMacro('active')) {
    // Macro exists
}

Macro Parameters

Macros can accept parameters, just like regular methods:

QueryBuilder::macro('wherePrice', function (QueryBuilder $query, string $operator, float $price) {
    return $query->where('price', $price, $operator);
});

// Use the macro with parameters
$expensive = $db->find()
    ->table('products')
    ->wherePrice('>', 100.00)
    ->get();

Default Parameters

Macros support default parameters:

QueryBuilder::macro('recent', function (QueryBuilder $query, int $days = 7) {
    $date = date('Y-m-d H:i:s', strtotime("-{$days} days"));
    return $query->where('created_at', $date, '>=');
});

// Use default (7 days)
$recent = $db->find()->table('products')->recent()->get();

// Use custom parameter
$veryRecent = $db->find()->table('products')->recent(1)->get();

Return Values

Macros can return different types of values:

Returning QueryBuilder (for Chaining)

When a macro returns a QueryBuilder instance, it can be chained with other methods:

QueryBuilder::macro('active', function (QueryBuilder $query) {
    return $query->where('status', 'active');
});

$result = $db->find()
    ->table('products')
    ->active()
    ->orderBy('name')
    ->get();

Returning Other Values

Macros can return any value:

QueryBuilder::macro('countActive', function (QueryBuilder $query) {
    return $query->where('status', 'active')->getValue('COUNT(*)');
});

$count = $db->find()->table('products')->countActive();

Examples

Example 1: Simple Status Filter

QueryBuilder::macro('active', function (QueryBuilder $query) {
    return $query->where('status', 'active');
});

QueryBuilder::macro('inactive', function (QueryBuilder $query) {
    return $query->where('status', 'inactive');
});

$activeProducts = $db->find()->table('products')->active()->get();
$inactiveProducts = $db->find()->table('products')->inactive()->get();

Example 2: Parameterized Macros

QueryBuilder::macro('wherePrice', function (QueryBuilder $query, string $operator, float $price) {
    return $query->where('price', $price, $operator);
});

QueryBuilder::macro('inCategory', function (QueryBuilder $query, int $categoryId) {
    return $query->where('category_id', $categoryId);
});

$result = $db->find()
    ->table('products')
    ->wherePrice('>', 100.00)
    ->inCategory(1)
    ->get();

Example 3: Complex Macro

QueryBuilder::macro('available', function (QueryBuilder $query) {
    return $query
        ->where('status', 'active')
        ->andWhere('price', 0, '>')
        ->andWhereNotNull('stock');
});

$availableProducts = $db->find()->table('products')->available()->get();

Example 4: Date-Based Macros

QueryBuilder::macro('recent', function (QueryBuilder $query, int $days = 7) {
    $date = date('Y-m-d H:i:s', strtotime("-{$days} days"));
    return $query->where('created_at', $date, '>=');
});

QueryBuilder::macro('thisMonth', function (QueryBuilder $query) {
    $startOfMonth = date('Y-m-01 00:00:00');
    $endOfMonth = date('Y-m-t 23:59:59');
    return $query
        ->where('created_at', $startOfMonth, '>=')
        ->andWhere('created_at', $endOfMonth, '<=');
});

$recentProducts = $db->find()->table('products')->recent(30)->get();
$monthProducts = $db->find()->table('products')->thisMonth()->get();

Example 5: Macro Returning Count

QueryBuilder::macro('countActive', function (QueryBuilder $query) {
    return $query->where('status', 'active')->getValue('COUNT(*)');
});

$activeCount = $db->find()->table('products')->countActive();

Example 6: Chaining Multiple Macros

QueryBuilder::macro('active', function (QueryBuilder $query) {
    return $query->where('status', 'active');
});

QueryBuilder::macro('inStock', function (QueryBuilder $query) {
    return $query->where('stock', 0, '>');
});

QueryBuilder::macro('featured', function (QueryBuilder $query) {
    return $query->where('featured', 1);
});

$result = $db->find()
    ->table('products')
    ->active()
    ->inStock()
    ->featured()
    ->orderBy('price', 'ASC')
    ->limit(10)
    ->get();

Error Handling

If you call a non-existent macro, a RuntimeException is thrown:

try {
    $db->find()->table('products')->nonExistentMacro();
} catch (\RuntimeException $e) {
    echo "Macro not found: " . $e->getMessage();
}

Best Practices

Use descriptive names: Choose macro names that clearly indicate their purpose

// Good
QueryBuilder::macro('active', ...);
QueryBuilder::macro('published', ...);

// Avoid
QueryBuilder::macro('filter1', ...);
QueryBuilder::macro('q', ...);

Return QueryBuilder for chaining: When creating query-building macros, return the QueryBuilder instance

QueryBuilder::macro('active', function (QueryBuilder $query) {
    return $query->where('status', 'active'); // Return $query
});

Keep macros focused: Each macro should do one thing well

// Good - focused
QueryBuilder::macro('active', function (QueryBuilder $query) {
    return $query->where('status', 'active');
});

// Avoid - too many responsibilities
QueryBuilder::macro('activeAndFeaturedAndInStock', function (QueryBuilder $query) {
    // Too many things
});

Document parameters: If macros accept parameters, document their purpose

/**
 * Filter products by price range.
 *
 * @param QueryBuilder $query
 * @param string $operator Comparison operator (>, <, >=, <=, =)
 * @param float $price Price threshold
 */
QueryBuilder::macro('wherePrice', function (QueryBuilder $query, string $operator, float $price) {
    return $query->where('price', $price, $operator);
});

Use default parameters: Make macros flexible with default values when appropriate

QueryBuilder::macro('recent', function (QueryBuilder $query, int $days = 7) {
    // Default to 7 days if not specified
});

Register macros early: Register macros during application bootstrap or in service providers

Differences from Scopes

Macros and scopes serve similar purposes but have different use cases:

Macros

Global registration: Registered once, available everywhere
Method-like syntax: Called as methods on QueryBuilder
No model dependency: Work with any QueryBuilder instance
No automatic application: Must be called explicitly
Can return any value: Not limited to QueryBuilder instances

Scopes

Model-specific: Defined in Model classes
Global scopes: Automatically applied to all queries
Local scopes: Applied via scope() method
Can be disabled: withoutGlobalScope() to temporarily disable
Model context: Better for model-specific logic

When to Use Macros

Use macros when you need:

Custom method names that read naturally (active(), published())
Methods that work across different models/tables
Reusable query patterns used throughout your application
Methods that can return values other than QueryBuilder

When to Use Scopes

Use scopes when you need:

Model-specific query logic
Automatic application (global scopes)
Ability to temporarily disable scopes
Logic that belongs to a specific model

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Query Builder Macros

Table of Contents

Overview

Registering Macros

Using Macros

Checking if Macro Exists

Macro Parameters

Default Parameters

Return Values

Returning QueryBuilder (for Chaining)

Returning Other Values

Examples

Example 1: Simple Status Filter

Example 2: Parameterized Macros

Example 3: Complex Macro

Example 4: Date-Based Macros

Example 5: Macro Returning Count

Example 6: Chaining Multiple Macros

Error Handling

Best Practices

Differences from Scopes

Macros

Scopes

When to Use Macros

When to Use Scopes

See Also

FilesExpand file tree

18-query-macros.md

Latest commit

History

18-query-macros.md

File metadata and controls

Query Builder Macros

Table of Contents

Overview

Registering Macros

Using Macros

Checking if Macro Exists

Macro Parameters

Default Parameters

Return Values

Returning QueryBuilder (for Chaining)

Returning Other Values

Examples

Example 1: Simple Status Filter

Example 2: Parameterized Macros

Example 3: Complex Macro

Example 4: Date-Based Macros

Example 5: Macro Returning Count

Example 6: Chaining Multiple Macros

Error Handling

Best Practices

Differences from Scopes

Macros

Scopes

When to Use Macros

When to Use Scopes

See Also