DEV Community: Michael

Laravel named query arguments

Michael — Fri, 12 Aug 2022 12:11:43 +0000

When writing a query it's common to use closures when using advanced clauses or grouping conditions together in-order for the query to behave as expected.

$users = DB::table('users')
           ->where('name', '=', 'John')
           ->where(function ($query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

With more complex queries which have numerous closures the repeated use of the $query argument can make readability awkward.

$users = DB::table('users')
           ->where('name', '=', 'John')
           ->where(function ($query) {
               $query->whereHas('role', function ($query) {
                   $query->whereIn('slug', ['leader', 'deputy'])
                        ->whereHas('team', function ($query) {
                            $query->whereIn(
                               'slug', 
                               ['sales', 'finance'],
                            );
                        });
               });
           })
           ->get();

By replacing the $query arguments with a variable named as per the table's name, eg $teams, the methods then appear prefixed with the table they're operating on which can make things feel more fluent.

$users = DB::table('users')
           ->where('name', '=', 'John')
           ->where(function ($users) {
               $users->whereHas('role', function ($roles) {
                   $roles->whereIn('slug', ['leader', 'deputy'])
                        ->whereHas('team', function ($teams) {
                            $teams->whereIn(
                               'slug', 
                               ['sales', 'finance'],
                            );
                        });
               });
           })
           ->get();

This approach also helps when scanning over many lines or reading things out of context, for example when trying to debug a certain query's conditions.

// Need to add the "senior-managers" 
// to the condition for the teams table

// Could this be the right condition,
// which table's slug is this?
$query->whereIn(
   'slug',
   ['sales', 'finance'],
);

// If the query argument has been named,
// then we immediately know which table it is 🙂
$teams->whereIn(
   'slug',
   ['sales', 'finance'],
);

Determining if multiple inputs have values with Laravel

Michael — Sat, 04 Dec 2021 17:15:51 +0000

Although it doesn't appear in the docs Laravel has a handy method to determine if the HTTP request contains one or more inputs with values:

if ($request->anyFilled(['name', 'type', 'status']) {
    //
}

The anyFilled method will return true if any of the inputs given in the array contain a non-empty value.

Array inputs validation and the N+1 query problem in Laravel

Michael — Sun, 28 Nov 2021 22:24:33 +0000

The N+1 query problem can crop up in a variety of circumstances, one area that could be surprising to find this problem is when validating a form submission.

Below is a validation rule to ensure an id exists in a corresponding table.

$request->validate([
    'comments.*.status_id' => ['required', 'exists:statuses,id'],
]);

If this was a single status_id input that would be fine, no N+1 problem would exist, but looking closely we can see status_id belongs to an array of inputs as shown by the dot notation. As status_id is an input in the comments array Laravel will perform the same exists query for each element, which could mean an excessive amount of queries.

As always the solution is to do some eager loading and in this case it can be achieve by making use of Rule::in().

use Illuminate\Validation\Rule;

$statuses = Status::pluck('id')->toArray();

$request->validate([
    'comments.*.status_id' => ['required', Rule::in($statuses)],
]);

Laravel will still perform the same validation on each of the comments array inputs but now the statuses table is only queried once, no matter how many elements there are in comments.

Array initialisation and key data types in PHP

Michael — Sun, 28 Nov 2021 20:07:03 +0000

When initialising PHP array elements the order of the keys can result in changes to all the key types, even when you explicitly specify the key value/type.

$foo[0] = 'a';
$foo[1] = 'b';
$foo[2] = 'c';
$foo[3] = 'd';

// $foo has integer keys

$bar[0] = 'a';
$bar[1] = 'b';

// $bar has integer keys

$bar[3] = 'c';

// $bar now has string keys, because we skipped element 2
// this is despite us using an integer key

$bar[2] = 'd';

// $bar still has string keys, despite it having element 2
// and it being declared with an integer key

json_encode($foo) !== json_encode($bar);

// True

Same string, different output... how Laravel's slug & snake helpers differ

Michael — Sun, 28 Nov 2021 19:47:37 +0000

TL;DR 👇

Str::slug('PHP') === Str::snake('PHP');

// False

Str::slug('php') === Str::snake('php');

// True

At face value both Str::slug and Str::snake seem to have a similar purpose and are simple to use, given a string they convert it to their respective formats.

use Illuminate\Support\Str;

Str::slug('php');

// php

Str::snake('php');

// php

You'd therefore think that given the same single word string both methods would consistently produce similar outputs, each in their respective formats. However lets see what happens if that string contains uppercase characters.

Str::slug('PHP');

// php

Str::snake('PHP');

// p_h_p

These two methods aren't that similar after all, as uppercase characters result in a separator character being added when using Str::snake.

Both Str::slug and Str::snake do allow you to specify which character is used as the separator, so maybe if we specify what separator is to be used things will become more consistent.

Str::slug('PHP', '_');

// php

Str::snake('PHP', '-');

// p-h-p

It's the same behaviour there as well, Str::snake still uses the separator character even when we provide the slug separator (dash) as the parameter. Likewise Str::slug doesn't use the custom separator even though the snake separator (underscore) was given as the parameter.

Now we know the Str::snake method is actually case sensitive, which could be very import when making comparisons or converting from one format to another.

$input = $request->input('programming_language');

Str::snake('php') === Str::snake($input);

// True when $input === 'php'
// False if $input === 'PHP'

$input = Str::lower($request->input('programming_language'));

Str::snake('php') === Str::snake($input);

// True if $input === 'php' or 'PHP'

The importance of ordering when using Laravel pagination

Michael — Sun, 28 Nov 2021 16:23:52 +0000

TL;DR when paginating it's best to always order your query using the primary key, a column with a unique constraint, or at very least a column which is reasonably likely to be different on each row, eg created_at.

Pagination uses limit and offset to return a subset of rows from a query, and that same query could be called multiple times if navigating between result pages. We must therefore ensure the rows are ordered consistently so upon each query call it moves through the result set as expected, otherwise rows may appear to the user to be missing or duplicated.

Remember that SQL can return rows in any order it likes, so even if we specified something like ->orderBy('name') if the values in the name column are identical across several rows we could end up with things sorted in a random order.

This is mostly likely to be of concern if there's a large number of rows and/or many identical values being ordered by, as when navigating between the pages the rows will appear to "move" between page numbers. This problem will become more apparent if the pagination row count is a low number.