Database and Eloquent ORM: New features and improvements since the original Laravel 8 release (1/2)

05 Nov, 2021

In this series, I show you new features and improvements to the Laravel framework since the original release of version 8. Last week, I wrote about the Collection class. This week is about the Database and Eloquent features in Laravel 8. The team added so many great improvements to the weekly versions that I split the Database and Eloquent features into two blog posts. Here is part one!

I got most code examples and explanations from the PRs and official documentation.

v8.5.0 Added crossJoinSub method to the query builder (#34400)

Add a subquery cross join to the query.

use Illuminate\Support\Facades\DB;

$totalQuery = DB::table('orders')->selectRaw('SUM(price) as total');

DB::table('orders')
    ->select('*')
    ->crossJoinSub($totalQuery, 'overall')
    ->selectRaw('(price / overall.total) * 100 AS percent_of_total')
    ->get();

v8.10.0 Added is() method to 1-1 relations for model comparison (#34693)

We can now do model comparisons between related models, without extra database calls!

// Before: foreign key is leaking from the post model
$post->author_id === $user->id;

// Before: performs extra query to fetch the user model from the author relation
$post->author->is($user);

// After
$post->author()->is($user);

v8.10.0 Added upsert to Eloquent and Base Query Builders (#34698, #34712)

If you would like to perform multiple "upserts" in a single query, then you may use the upsert method instead of multiple updateOrCreate calls.

Flight::upsert([
    ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
    ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
], ['departure', 'destination'], ['price']);

v8.12.0 Added explain() to Query\Builder and Eloquent\Builder (#34969)

The explain() method allows you to receive the explanation from the builder (both Query and Eloquent).

User::where('name', 'Illia Sakovich')->explain();

User::where('name', 'Illia Sakovich')->explain()->dd();

v8.15.0 Added support of MorphTo relationship eager loading constraints (#35190)

If you are eager loading a morphTo relationship, Eloquent will run multiple queries to fetch each type of related model. You may add additional constraints to each of these queries using the MorphTo relation's constrain method:

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Relations\MorphTo;

$comments = Comment::with(['commentable' => function (MorphTo $morphTo) {
    $morphTo->constrain([
        Post::class => function (Builder $query) {
            $query->whereNull('hidden_at');
        },
        Video::class => function (Builder $query) {
            $query->where('type', 'educational');
        },
    ]);
}])->get();

v8.17.2 Added BelongsToMany::orderByPivot() (#35455)

This method allows you to directly order the query results of a BelongsToMany relation:

class Tag extends Model
{
    public $table = 'tags';
}

class Post extends Model
{
    public $table = 'posts';

    public function tags()
    {
        return $this->belongsToMany(Tag::class, 'posts_tags', 'post_id', 'tag_id')
            ->using(PostTagPivot::class)
            ->withTimestamps()
            ->withPivot('flag');
    }
}

class PostTagPivot extends Pivot
{
    protected $table = 'posts_tags';
}

// Somewhere in a controller
public function getPostTags($id)
{
    return Post::findOrFail($id)->tags()->orderPivotBy('flag', 'desc')->get();
}

v8.23.0 Added BuildsQueries::sole() (#35869, #35908, #35902, #35912)

The sole method will return the only record that matches the criteria. If no records are found, a NoRecordsFoundException will be thrown. If multiple records were found, a MultipleRecordsFoundException will be thrown.

DB::table('products')->where('ref', '#123')->sole()

v8.27.0 Allow adding multiple columns after a column (#36145)

The after method may be used to add columns after an existing column in the schema:

Schema::table('users', function (Blueprint $table) {
    $table->after('remember_token', function ($table){
        $table->string('card_brand')->nullable();
        $table->string('card_last_four', 4)->nullable();
    });
});

v8.37.0 Added anonymous migrations (#36906)

Laravel automatically assign a class name to all of the migrations. You may now return an anonymous class from your migration file:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up()
    {
        Schema::table('people', function (Blueprint $table) {
            $table->string('first_name')->nullable();
        });
    }
};

v8.27.0 Add query builder map method (#36193)

The new chunkMap method is similar to the each query builder method, where it automatically chunks over the results:

return User::orderBy('name')->chunkMap(fn ($user) => [
    'id' => $user->id,
    'name' => $user->name,
]), 25);

v8.28.0 ArrayObject + Collection Custom Casts (#36245)

Since the array cast returns a primitive type, it is not possible to mutate an offset of the array directly. To solve this, the AsArrayObject cast casts your JSON attribute to an ArrayObject class:

// Within model...
$casts = ['options' => AsArrayObject::class];

// Manipulating the options...
$user = User::find(1);

$user->options['foo']['bar'] = 'baz';

$user->save();

v8.40.0 Added Eloquent\Builder::withOnly() (#37144)

If you would like to override all items within the $with property for a single query, you may use the withOnly method:

class Product extends Model{
    protected $with = ['prices', 'colours', 'brand'];

    public function colours(){ ... }
    public function prices(){ ... }
    public function brand(){ ... }
}

Product::withOnly(['brand'])->get();

v8.41.0 Added cursor pagination (aka keyset pagination) (#37216, #37315)

Cursor-based pagination places a "cursor" string in the query string, an encoded string containing the location that the next paginated query should start paginating and the direction it should paginate. This method of pagination is particularly well-suited for large data sets and "infinite" scrolling user interfaces.

use App\Models\User;
use Illuminate\Support\Facades\DB;

$users = User::orderBy('id')->cursorPaginate(10);
$users = DB::table('users')->orderBy('id')->cursorPaginate(10);

v8.12.0 Added withMax, withMin, withSum and withAvg methods to QueriesRelationships (#34965, #35004)

In addition to the withCount method, Eloquent now provides withMin, withMax, withAvg, and withSum methods. These methods will place a {relation}_{function}_{column} attribute on your resulting models.

Post::withCount('comments');

Post::withMin('comments', 'created_at');
Post::withMax('comments', 'created_at');
Post::withSum('comments', 'foo');
Post::withAvg('comments', 'foo');

Under the hood, these methods use the withAggregate method:

Post::withAggregate('comments', 'created_at', 'distinct');
Post::withAggregate('comments', 'content', 'length');
Post::withAggregate('comments', 'created_at', 'custom_function');

Comment::withAggregate('post', 'title');
Post::withAggregate('comments', 'content');

v8.13.0 Added loadMax, loadMin, loadSum and loadAvg methods to Eloquent\Collection. Added loadMax, loadMin, loadSum, loadAvg, loadMorphMax, loadMorphMin, loadMorphSum and loadMorphAvg methods to Eloquent\Model (#35029)

In addition to the new with* method above, new load* methods are added to the Collection and Model class.

// Eloquent/Collection
public function loadAggregate($relations, $column, $function = null) {...}
public function loadCount($relations) {...}
public function loadMax($relations, $column)  {...}
public function loadMin($relations, $column)  {...}
public function loadSum($relations, $column)  {...}
public function loadAvg($relations, $column)  {...}

// Eloquent/Model
public function loadAggregate($relations, $column, $function = null) {...}
public function loadCount($relations) {...}
public function loadMax($relations, $column) {...}
public function loadMin($relations, $column) {...}
public function loadSum($relations, $column) {...}
public function loadAvg($relations, $column) {...}

public function loadMorphAggregate($relation, $relations, $column, $function = null) {...}
public function loadMorphCount($relation, $relations) {...}
public function loadMorphMax($relation, $relations, $column) {...}
public function loadMorphMin($relation, $relations, $column) {...}
public function loadMorphSum($relation, $relations, $column) {...}
public function loadMorphAvg($relation, $relations, $column) {...}

v8.13.0 Modify QueriesRelationships::has() method to support MorphTo relations (#35050)

Add a polymorphic relationship count / exists condition to the query.

public function hasMorph($relation, ...)

public function orHasMorph($relation,...)
public function doesntHaveMorph($relation, ...)
public function whereHasMorph($relation, ...)
public function orWhereHasMorph($relation, ...)
public function orHasMorph($relation, ...)
public function doesntHaveMorph($relation, ...)
public function orDoesntHaveMorph($relation,...)

Example with a closure to customize the relationship query:

// Retrieve comments associated to posts or videos with a title like code%...
$comments = Comment::whereHasMorph(
    'commentable',
    [Post::class, Video::class],
    function (Builder $query) {
        $query->where('title', 'like', 'code%');
    }
)->get();

// Retrieve comments associated to posts with a title not like code%...
$comments = Comment::whereDoesntHaveMorph(
    'commentable',
    Post::class,
    function (Builder $query) {
        $query->where('title', 'like', 'code%');
    }
)->get();