PHP code example of halimzidoune / query-macro-helper

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

halimzidoune / query-macro-helper example snippets

'providers' => [
    // ...
    Hz\QueryMacroHelper\QueryMacroHelperServiceProvider::class,
],

return [
    // ...
    Hz\QueryMacroHelper\QueryMacroHelperServiceProvider::class,
],

DB::connection()->getPdo()->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

namespace App\Builders\Macros;

use Hz\QueryMacroHelper\Extensions\BaseMacro;

class Lower extends BaseMacro
{
    public static function name(): string
    {
        return 'selectLower';
    }

    // First argument is the column with optional alias; additional args are your parameters
    public function defaultExpression($column): string
    {
        // LOWER() function exists on all major databases
        return "LOWER($column)";
    }

    // Optionally, override per driver if needed:
    // public function mysql($column) { return "LOWER($column)"; }
    // public function pgsql($column) { return "LOWER($column)"; }
}

DB::table('users')
    ->selectLower('email as email_lower')
    ->get();

namespace App\Builders\Macros;

use Hz\QueryMacroHelper\Extensions\BaseMacro;

class ExtractYear extends BaseMacro
{
    public static function name(): string
    {
        return 'selectExtractYear';
    }

    // Default implementation (PostgreSQL style)
    public function defaultExpression($column): string
    {
        return "EXTRACT(YEAR FROM $column)";
    }

    // MySQL implementation
    public function mysql($column): string
    {
        return "YEAR($column)";
    }

    // SQL Server implementation
    public function sqlsrv($column): string
    {
        return "YEAR($column)";
    }

    // Oracle implementation
    public function oracle($column): string
    {
        return "EXTRACT(YEAR FROM $column)";
    }

    // SQLite implementation
    public function sqlite($column): string
    {
        return "strftime('%Y', $column)";
    }
}

DB::table('users')
    ->selectExtractYear('birth_date as birth_year')
    ->get();

// Automatically generates the correct SQL for each database:
// MySQL: YEAR(birth_date) AS birth_year
// PostgreSQL: EXTRACT(YEAR FROM birth_date) AS birth_year
// SQL Server: YEAR(birth_date) AS birth_year
// Oracle: EXTRACT(YEAR FROM birth_date) AS birth_year
// SQLite: strftime('%Y', birth_date) AS birth_year

// Works on all databases automatically
DB::table('users')
    ->selectConcat('full_name', 'first_name', str(' '), 'last_name')
    ->get();

// Column names: 'first_name', 'last_name'
// Literal string: str(' ') for the space separator
// Result: first_name + ' ' + last_name AS full_name

// MySQL: CONCAT(first_name, ' ', last_name) AS full_name
// PostgreSQL/SQLite: first_name || ' ' || last_name AS full_name
// SQL Server: ISNULL(first_name, '') + ' ' + ISNULL(last_name, '') AS full_name

// Concatenate with custom separator
DB::table('products')
    ->selectConcat('full_name', 'brand', str(' - '), 'model')
    ->get();

// Concatenate with multiple literals
DB::table('addresses')
    ->selectConcat('full_address', 'street', str(', '), 'city', str(', '), 'state')
    ->get();

// Mix columns and literals
DB::table('users')
    ->selectConcat('display_name', str('@'), 'username')
    ->get();

DB::table('users')
    ->selectUpper('name as name_upper')
    ->get();

DB::table('users')
    ->selectLower('email as email_lower')
    ->get();

DB::table('posts')
    ->selectLength('content as content_length')
    ->get();

DB::table('users')
    ->selectSubstring('email as email_prefix', 1, 10)
    ->get();

DB::table('posts')
    ->selectReplace('title as updated_title', 'old', 'new')
    ->get();

DB::table('users')
    ->selectTrim('username as clean_username')
    ->get();

DB::table('products')
    ->selectPad('sku as padded_sku', 8, '0', 'left')
    ->get();

DB::table('files')
    ->selectStartsWith('filename as is_image', 'IMG_')
    ->get();

DB::table('files')
    ->selectEndsWith('filename as is_jpg', '.jpg')
    ->get();

DB::table('posts')
    ->selectContains('content as has_important', 'important')
    ->get();

DB::table('users')
    ->selectRegexp('email as valid_email', '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$')
    ->get();

DB::table('posts')
    ->selectSlug('title as url_slug')
    ->get();

DB::table('users')
    ->selectCase('status as status_label', [
        'active' => 'Active User',
        'inactive' => 'Inactive User'
    ], 'Unknown')
    ->get();

DB::table('orders')
    ->selectAdd('subtotal as total', 'tax')
    ->get();

DB::table('inventory')
    ->selectSubtract('stock as available', 'reserved')
    ->get();

DB::table('order_items')
    ->selectMultiply('quantity as line_total', 'unit_price')
    ->get();

DB::table('users')
    ->selectRandom('random_value')
    ->get();

DB::table('products')
    ->selectRandomBetween('random_priority', 1, 100)
    ->get();

DB::table('transactions')
    ->selectAbs('amount as absolute_amount')
    ->get();

DB::table('products')
    ->selectRound('price as rounded_price', 2)
    ->get();

DB::table('measurements')
    ->selectFloor('length as floor_length')
    ->get();

DB::table('measurements')
    ->selectCeil('length as ceil_length')
    ->get();

DB::table('squares')
    ->selectPower('number as squared', 2)
    ->get();

DB::table('geometry')
    ->selectSqrt('area as side_length')
    ->get();

DB::table('numbers')
    ->selectModulo('value as is_even', 2)
    ->get();

DB::table('sales')
    ->selectPercent('amount as percentage', 'total_sales')
    ->get();

DB::table('prices')
    ->selectTruncate('cost as truncated_cost', 2)
    ->get();

DB::table('calculations')
    ->selectSafeDivision('numerator as result', 'denominator', 0)
    ->get();

DB::table('users')
    ->selectDateFormat('created_at as formatted_date', 'Y-m-d H:i:s')
    ->get();

// Automatically converts to database-specific format:
// MySQL: DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s')
// PostgreSQL: TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS')
// SQL Server: FORMAT(created_at, 'yyyy-MM-dd HH:mm:ss')

DB::table('events')
    ->selectStartOfDay('event_date as day_start')
    ->get();

DB::table('events')
    ->selectEndOfDay('event_date as day_end')
    ->get();

DB::table('schedules')
    ->selectStartOfWeek('week_date as week_start')
    ->get();

DB::table('schedules')
    ->selectEndOfWeek('week_date as week_end')
    ->get();

DB::table('reports')
    ->selectEndOfMonth('report_date as month_end')
    ->get();

DB::table('fiscal_years')
    ->selectStartOfYear('fiscal_date as year_start')
    ->get();

DB::table('fiscal_years')
    ->selectEndOfYear('fiscal_date as year_end')
    ->get();

DB::table('logs')
    ->selectStartOfHour('timestamp as hour_start')
    ->get();

DB::table('logs')
    ->selectEndOfHour('timestamp as hour_end')
    ->get();

DB::table('events')
    ->selectDayOfWeek('event_date as day_number')
    ->get();

DB::table('schedules')
    ->selectWeekOfYear('date as week_number')
    ->get();

DB::table('calendar')
    ->selectDaysInMonth('month_date as days_count')
    ->get();

DB::table('users')
    ->selectAge('birth_date as age')
    ->get();

DB::table('bookings')
    ->selectDiffInDays('check_in as stay_duration', 'check_out')
    ->get();

DB::table('calls')
    ->selectDiffInMinutes('start_time as call_duration', 'end_time')
    ->get();

DB::table('races')
    ->selectDiffInSeconds('start_time as race_time', 'finish_time')
    ->get();

DB::table('appointments')
    ->selectAddTime('scheduled_time as next_hour', 1, 'hour')
    ->get();

DB::table('events')
    ->selectIsSameDay('event_date as is_today', 'today')
    ->get();

DB::table('fiscal_records')
    ->selectIsSameYear('fiscal_date as is_current_fiscal', 'current_year')
    ->get();

DB::table('logs')
    ->selectIsSameHour('timestamp as is_current_hour', 'now')
    ->get();

DB::table('high_frequency_data')
    ->selectIsSameMinute('timestamp as is_current_minute', 'now')
    ->get();

DB::table('years')
    ->selectIsLeapYear('year_date as is_leap')
    ->get();

DB::table('products')
    ->selectString('sku as sku_string', 50)
    ->get();

DB::table('prices')
    ->selectInteger('amount as amount_int')
    ->get();

DB::table('measurements')
    ->selectFloat('value as value_float', 10, 2)
    ->get();

DB::table('settings')
    ->selectBoolean('is_active as active_bool')
    ->get();

DB::table('events')
    ->selectDate('datetime as date_only')
    ->get();

DB::table('logs')
    ->selectDateTime('timestamp as formatted_datetime')
    ->get();

namespace App\Builders\Macros;

use Hz\QueryMacroHelper\Extensions\BaseMacro;

class CustomMacro extends BaseMacro
{
    public static function name(): string
    {
        return 'selectCustom';
    }

    public function defaultExpression($column, $param): string
    {
        return "CUSTOM_FUNCTION($column, $param)";
    }

    public function mysql($column, $param): string
    {
        return "MYSQL_CUSTOM_FUNCTION($column, $param)";
    }

    public function pgsql($column, $param): string
    {
        return "POSTGRES_CUSTOM_FUNCTION($column, $param)";
    }

    // Add other database-specific methods as needed
}

$users = DB::table('users')
    ->selectConcat('full_name', 'first_name', str(' '), 'last_name')
    ->selectDateFormat('created_at as join_date', 'Y-m-d')
    ->selectAge('birth_date as age')
    ->selectCase('status as status_label', [
        'active' => 'Active',
        'inactive' => 'Inactive'
    ], 'Unknown')
    ->where('age', '>', 18)
    ->get();

$posts = Post::query()
    ->selectSlug('title as url_slug')
    ->selectLength('content as content_length')
    ->selectDateFormat('published_at as formatted_date', 'F j, Y')
    ->where('content_length', '>', 1000)
    ->get();

bash
php artisan make:macro Lower

bash
php artisan make:macro MyCustomMacro