Download the PHP package halimzidoune/query-macro-helper without Composer

Laravel Query Macro Helper

A Laravel package that extends the Query Builder with database-agnostic SELECT macros. Transform your raw SQL expressions into readable, chainable methods that automatically generate the correct syntax for each database driver. Replace complex DB::raw() statements with intuitive methods like selectConcat(), selectDateFormat(), and selectCase() that work seamlessly across MySQL, SQLite, PostgreSQL, Oracle, SQL Server.

🚀 Features

• Database Agnostic: Automatically adapts SQL syntax for MySQL, PostgreSQL, SQLite, SQL Server, and Oracle
• Clean API: Simple, intuitive method names that work like native Laravel methods
• No Raw SQL: Eliminate the need for DB::raw() statements and driver-specific code
• Easy Extension: Create custom macros with the built-in artisan command
• Laravel 10 & 11 Support: Compatible with the latest Laravel versions

✨ Benefits

• Single code path: One query works across all supported drivers
• Readable queries: Intent-revealing selectX() methods instead of raw SQL
• Safer expressions: Centralize SQL generation and avoid copy/paste errors
• Easy to extend: Add your own macros with a tiny class; auto-registered
• Composable: Chain multiple macros with standard Query/Eloquent builders
• Production-ready: Covers common String, Number, Datetime, and Cast use cases

📦 Installation

Via Composer

Service Provider Registration

Add this to your config/app.php:

For laravel > 10 bootstrap/providers.php:

It's recomended to add this line in AppSeriveProvider@boot for a better cast:

✍️ Writing a custom macro

Use the generator, then implement driver-aware SQL as needed.

This creates app/Builders/Macros/Lower.php. Example implementation:

Usage:

Example 2: Driver-specific implementation

Here's a more complex example that shows how to handle different database drivers:

Usage:

Note: When creating custom macros that accept multiple arguments, consider using str() helper for literal strings to distinguish them from column names, similar to how selectConcat works.

More ideas you could implement:

• selectNullIfEmpty(column): Treat empty string as NULL across drivers
• selectQuarter(dateColumn): Extract quarter number Q1–Q4
• selectJsonValue(column, path): JSON path extraction with cross-driver support
• selectNormalizePhone(column): Keep digits only for phone normalization

📚 Macro reference (quick table)

Below are the macros shipped with the package, grouped by category. Call these as chained methods on Query\Builder or Eloquent\Builder. Aliases can be provided using "column as alias".

String

Number

Datetime

Casts

🔧 Available Macros

selectConcat(result_alias, column1, ...)

Concatenates multiple columns or values with database-specific syntax.

Important: Use str() helper for literal strings to distinguish them from column names.

More examples:

selectUpper(column)

Converts text to uppercase.

selectLower(column)

Converts text to lowercase.

selectLength(column)

Gets the length of a string.

selectSubstring(column, start, length)

Extracts a substring from a string.

selectReplace(column, search, replace)

Replaces occurrences of a substring.

selectTrim(column)

Removes leading and trailing whitespace.

selectPad(column, length, pad_string, pad_type)

Pads a string to a specified length.

selectStartsWith(column, prefix)

Checks if a string starts with a specific prefix.

selectEndsWith(column, suffix)

Checks if a string ends with a specific suffix.

selectContains(column, substring)

Checks if a string contains a substring.

selectRegexp(column, pattern)

Performs regex pattern matching.

selectSlug(column)

Converts text to URL-friendly slug format.

selectCase(column, cases, default)

Performs CASE statement operations.

Number Operations

selectAdd(column1, column2)

Adds two numeric values.

selectSubtract(column1, column2)

Subtracts one numeric value from another.

selectMultiply(column1, column2)

Multiplies two numeric values.

selectRandom()

Generates a random number (database-specific implementation).

selectRandomBetween(min, max)

Generates a random number within a range.

selectAbs(column)

Returns the absolute value of a number.

selectRound(column, decimals)

Rounds a number to a specified number of decimal places.

selectFloor(column)

Rounds a number down to the nearest integer.

selectCeil(column)

Rounds a number up to the nearest integer.

selectPower(column, exponent)

Raises a number to a specified power.

selectSqrt(column)

Calculates the square root of a number.

selectModulo(column, divisor)

Returns the remainder of division.

selectPercent(column, total)

Calculates percentage.

selectTruncate(column, decimals)

Truncates a number to a specified number of decimal places.

selectSafeDivision(column1, column2, default)

Performs safe division with fallback for division by zero.

DateTime Operations

selectDateFormat(column, format)

Formats a date using Carbon-style format strings.

selectStartOfDay(column)

Gets the start of day for a date.

selectEndOfDay(column)

Gets the end of day for a date.

selectStartOfWeek(column)

Gets the start of week for a date.

selectEndOfWeek(column)

Gets the end of week for a date.

selectEndOfMonth(column)

Gets the end of month for a date.

selectStartOfYear(column)

Gets the start of year for a date.

selectEndOfYear(column)

Gets the end of year for a date.

selectStartOfHour(column)

Gets the start of hour for a datetime.

selectEndOfHour(column)

Gets the end of hour for a datetime.

selectDayOfWeek(column)

Gets the day of week (1-7).

selectWeekOfYear(column)

Gets the week number of the year.

selectDaysInMonth(column)

Gets the number of days in a month.

selectAge(column)

Calculates age from a birth date.

selectDiffInDays(column1, column2)

Calculates the difference in days between two dates.

selectDiffInMinutes(column1, column2)

Calculates the difference in minutes between two datetimes.

selectDiffInSeconds(column1, column2)

Calculates the difference in seconds between two datetimes.

selectAddTime(column, amount, unit)

Adds time to a date/datetime.

selectIsSameDay(column1, column2)

Checks if two dates are the same day.

selectIsSameYear(column1, column2)

Checks if two dates are in the same year.

selectIsSameHour(column1, column2)

Checks if two datetimes are in the same hour.

selectIsSameMinute(column1, column2)

Checks if two datetimes are in the same minute.

selectIsLeapYear(column)

Checks if a year is a leap year.

Type Casting Operations

selectString(column, length)

Casts a value to string type.

selectInteger(column)

Casts a value to integer type.

selectFloat(column, precision, scale)

Casts a value to float type.

selectBoolean(column)

Casts a value to boolean type.

selectDate(column)

Casts a value to date type.

selectDateTime(column)

Casts a value to datetime type.

🛠️ Creating Custom Macros

Using Artisan Command

This will create a new macro class in app/Builders/Macros/ directory.

Manual Creation

Create a class that extends BaseMacro and implements the required methods:

🔍 Advanced Usage Examples

Complex Queries with Multiple Macros

Using with Eloquent Models

🌟 Supported Database Drivers

• MySQL - Full support for all macros
• PostgreSQL - Full support for all macros
• SQLite - Full support for all macros
• SQL Server - Full support for all macros
• Oracle - Full support for all macros

📚 Best Practices

1. Use Descriptive Aliases: Always provide meaningful aliases for your macro results
2. Combine with Existing Methods: Mix macros with standard Laravel query methods
3. Performance Considerations: Some macros may be more efficient than others on specific databases
4. Testing: Test your queries across different database drivers when possible

🤝 Contributing

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

📄 License

The MIT License (MIT). Please see License File for more information.

🆘 Support

If you encounter any issues or have questions, please open an issue on GitHub or contact the maintainer.

Made with ❤️ for the Laravel community