Download the PHP package axn/laravel-database-extension without Composer

Laravel Database Extension

Includes some extensions/improvements to the Database section of Laravel Framework

• Installation
• Usage
  • Natural sorting
  • Default order
  • Joins using relationships
  • Eloquent whereHasIn macro
  • Eloquent whereLike macro
  • SoftDeletes withoutTrashedExcept scope

Installation

With Composer:

Usage

Natural sorting

Method orderByNatural has been added to QueryBuilder (macro) for natural sorting (see: http://kumaresan-drupal.blogspot.fr/2012/09/natural-sorting-in-mysql-or.html). Use it like orderBy.

Example:

Default order

Add the global scope DefaultOrderScope to the model if you want to have select results automatically sorted:

option can be:

• 'asc'
• 'desc'
• 'natural' (apply orderByNatural())
• 'natural_asc' (same as 'natural')
• 'natural_desc' (same as 'natural' but descendant)
• 'raw' (apply orderByRaw())

If you don't precise option, it will be "asc" by default.

Example:

If you don't want the default order applied, simply use the Eloquent method withoutGlobalScope() on the model:

Note that the default order is automatically disabled if you manually set ORDER BY clause.

Joins using relationships

This is the most important feature of this package: you can do joins using Eloquent relationships!

WARNING: only BelongsTo, HasOne, HasMany, MorphOne and MorphMany relations are supported. So, if you want to use BelongsToMany, you have to go with the HasMany/BelongsTo relations to/from the pivot table.

Example:

You may also want to use:

• leftJoinRel()
• rightJoinRel()

Or if the model uses SoftDeletes and you want to include trashed records:

• joinRelWithTrashed()
• leftJoinRelWithTrashed()
• rightJoinRelWithTrashed()

And to add extra criteria:

Note that extra criteria are automatically added if they are defined on the relation:

WARNING: an instance of JoinRelBuilder is created and attached to the Eloquent Builder instance via WeakMap to handle this feature. If you ever clone the Builder instance, note that there is no cloning of the attached JoinRelBuilder instance. This can be a problem if you use "joinRel" on the cloned instance with a reference to an alias created in the original instance.

For example:

If you need to handle this case, use the "cloneWithJoinRelBuilder" method instead of clone:

Eloquent whereHasIn macro

If you have performance issues with the whereHas method, you can use whereHasIn instead.

It uses in clause instead of exists to check existence:

You can use a callback to add extra criteria:

Note that it does not support "dot" notation, but you can use joins:

You may also want to use:

• orWhereHasIn()
• whereDoesntHaveIn()
• orWhereDoesntHaveIn()

Eloquent whereLike macro

Source: https://murze.be/searching-models-using-a-where-like-query-in-laravel

Warning! This only works on instances of the Eloquent Builder, not on the generic Query Builder.

A replacement of this:

By that:

Or more advanced, a replacement of this:

By that:

SoftDeletes withoutTrashedExcept scope

Our SoftDeletes trait extends the Eloquent one to provide the withoutTrashedExcept scope :

To use it, add the trait Axn\Illuminate\Database\Eloquent\SoftDeletes to models: