README

A set of useful Laravel builder macros.

Requirements

PHP 8.2+
Laravel 12 or 13

Installation

You can install the package via composer:

composer require datalogix/laravel-builder-macros

The package will automatically register itself.

The package ships AI guidelines for Laravel Boost, which are loaded when you run php artisan boost:install.

If a macro with the same name is already registered, for example by another package, it is kept. To replace one of these macros in your application, register your own with Builder::macro() in the boot method of a service provider.

`addSubSelect`

Add a select sub query.

// Params: $column, $query (Eloquent or query builder)
$query->addSubSelect('primary_address_id',
    Address::select('id')
        ->where('user_id', $user->id)
        ->primary()
);

// It adds primary_address_id to the result set

When no columns are selected, it also selects all columns of the main table (see defaultSelectAll), so the sub query is added to them instead of replacing them. The sub query is limited to one row.

`defaultSelectAll`

It selects all columns from the query. Useful for queries with joins and additional selects.

$query->defaultSelectAll()
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->addSelect('contacts.name as contact_name');

`filter`

Filter in your models. Each filter is applied with whereLike, and blank values are ignored.

$query->filter(['name' => 'john'])->get();

// Returns all results where name includes `john`

You can also supply multiple filters:

$query->filter(['name' => 'john', 'contact.email' => '@'])->get();

// Returns all results where name includes `john` and contact.email includes `@`

When filtering with request input, pass the allowed columns as the second argument:

$query->filter($request->all(), ['name', 'email', 'contact.email'])->get();

// Or
$query->filter($request->only(['name', 'email', 'contact.email']))->get();

Pass true as the third argument to escape %, _ and ! in the values, so they are matched literally (see whereLike):

$query->filter($request->all(), ['name', 'email'], true)->get();

Warning

Never pass unfiltered input such as $request->all() without allowed columns: every key becomes a column (or a relation, when it contains a .), so users could filter on any column, like password, or break the query with keys like page. Laravel resolves a relation by calling the model method of the same name, so a key like archive.x would run a public archive() method of your model. Laravel's own methods, like truncate, save or markEmailAsVerified, are rejected even when your model overrides them, but methods of your model and of other packages' traits, like Scout's searchable, are not.

`joinRelation`

A query way to join relations.

// Params: $relationName, $operator
$query->joinRelation('contact');

Nested relations are joined one after another:

$query->joinRelation('posts.comments');

// Joins posts and then comments

Supports BelongsTo, HasOne, HasMany, MorphOne and MorphMany relations. Polymorphic relations also match the morph type, so only rows of the model type are joined. MorphTo relations aren't supported, as the related table depends on each row.

When no columns are selected, it selects only the columns of the main table (see defaultSelectAll), so columns of the joined table like id don't override the model attributes. Soft deleted rows of models using SoftDeletes are not joined. Other constraints defined in the relation (where, latestOfMany, etc.) are not applied to the join. Joining a HasMany relation returns the main model once for each related row.

Relations to the same table, like parent or children, are joined under the relation name, so its columns are referenced through it:

$query->joinRelation('parent')->addSelect('parent.name as parent_name');

The same applies to relations to a table that is already joined, like author and editor both to users. The first one keeps the table name and the next ones are joined under the relation name:

$query->joinRelation('author')
    ->joinRelation('editor')
    ->addSelect('users.name as author_name', 'editor.name as editor_name');

`leftJoinRelation`

A query to left join relations.

// Params: $relationName, $operator
$query->leftJoinRelation('contact');

It works like joinRelation, with a LEFT JOIN.

`map`

A direct method to retrieve the results and map it.

$emails = User::where('active', true)->map(function ($user) {
    return $user->email;
});

// Returns a collection

`search`

An alias of whereLike, with the same arguments, that doesn't conflict with the query builder's native whereLike.

$query->search(['title', 'contact.name'], 'john')->get();

Note

Models using Laravel Scout have a static search method, so Model::search() calls Scout. Use Model::query()->search() instead.

`whereLike`

Search in your models with the LIKE operator.

Note

The query builder has a native whereLike($column, $value, $caseSensitive = false) method. On Eloquent builders this macro takes precedence over it, with a different behavior: the value is wrapped with % and the third argument is $start, not $caseSensitive. On DB::table() queries the native method is used. Prefer the search alias to avoid the confusion.

$query->whereLike('title', 'john')->get();

// Returns all results where title includes `john`

$query->whereLike('title', 'john', false)->get();

// Returns all results where title starts with `john`

$query->whereLike('title', 'john', true, false)->get();

// Returns all results where title ends with `john`

You can also supply an array of columns to search in:

$query->whereLike(['title', 'contact.name'], 'john')->get();

// Returns all results where title or contact.name includes `john`

Notes:

Columns containing a . are searched in relations (contact.name searches name in the contact relation, and posts.comments.body searches nested relations). Table-prefixed columns like users.name are not supported. Relation searches don't support aliased tables (from('users as u')), as Laravel's whereHas doesn't.
Each name of a relation path must be a relation of the model. Names of Laravel's own methods (like truncate, save or markEmailAsVerified, even when overridden by the model), static, non-public and methods with required arguments throw a RelationNotFoundException before any method is called; other public methods are called to check that they return a relation and throw it if they don't. A MorphTo relation can only be the last name of the path, as its related model is only known per row; followed by other names, it throws an InvalidArgumentException.
Columns ending in _id are compared with = instead of LIKE.
Blank and boolean values are ignored.
% and _ in the value act as wildcards, unless escaped with the $escape argument:

// Params: $columns, $value, $start, $end, $escape, $ignoreCase
$query->whereLike('title', '100%', true, true, true)->get();

// Or
$query->whereLike('title', '100%', escape: true)->get();

// Returns all results where title includes `100%`, but not `1000`

Escaping uses LIKE ? ESCAPE '!', so it works the same way in MySQL, PostgreSQL, SQLite and SQL Server.

Case sensitivity follows the database: PostgreSQL's LIKE is case sensitive, while MySQL, SQLite and SQL Server ignore case with their default collations. Pass the $ignoreCase argument to ignore case on PostgreSQL too (it uses ILIKE there; the other databases are unchanged):

$query->whereLike('title', 'john', ignoreCase: true)->get();

// Returns all results where title includes `john`, `John` or `JOHN`, on PostgreSQL and on databases with case-insensitive collations

datalogix / laravel-builder-macros

Package info

Security

Statistics