Cleaning up queries with dot notation
Tags: php, laravel, eloquent
Published: 2021-06-16 01:30:57

_🔗 This article was originally posted, with additional formatting, on my personal blog at https://ryanc.co/blog/cleaning-up-queries-using-dot-notation_

One of the many conventions found throughout the Laravel framework is dot notation. Dot notation is the use of dots `.` in strings to represent depth. In many instances, it allows for writing cleaner and more clear code. Take for example it's use case of accessing nested elements in an array using the `Arr` helper

```php
Arr::get($array, 'user.profile.billing_details.address');
```

This not only looks nicer but it also takes care of handling undefined index errors and this method specifically has the added benefit of providing a default value as a third parameter. Use cases similar to this can be found in most `Arr` methods, most `Collection` methods, when validating arrays, and in most `array_*` helper methods (for those of you on legacy versions of Laravel) to name a few.

## Eloquent & dot notation

Support for dot notation can can also be found throughout Laravel when interacting with Eloquent relations.

### whereHas()

For example, say you want to query a `User` model for all records that have an associated `Invoice` record which has an associated `Payment` record. While this can be done a handful of ways, one way would be to use the `whereHas()` Eloquent Builder method, like so

```php
User::whereHas('invoices', function ($invoices) {
	$invoices->whereHas('payment');
})->get();
```

Awesome! Task achieved. But consider the following alternative using dot notation

```php
User::whereHas('invoices.payment')->get();
```

While the contrived example may only save you a few lines/characters, that is still characters saved, at no expense to the readability of the code — if anything, it's improved. This usage of dot notation can be used to clean up complex nested relationships that would otherwise result in deep chains of single-depth `whereHas()` methods. Most importantly, **the resulting query is *exactly* the same between the two examples**:

```sql
select * from `users` where exists (select * from `invoices` where `users`.`id` = `invoices`.`user_id` and exists (select * from `payments` where `invoices`.`payment_id` = `payments`.`id` and `payments`.`deleted_at` is null) and `invoices`.`deleted_at` is null) and `users`.`deleted_at` is null
```

As an added bonus, you still have the flexibility of breaking away from dot notation at any level of depth and passing a closure to the second parameter of `whereHas()` to add query constraints and even continue with dot notation as part of your constraints. Still, the resulting query will be exactly the same as it's single-depth, chained `whereHas()` counterpart.

### with()

One usage of dot notation that I find more powerful than in `whereHas()` methods is in `with()` methods to eager load related models. Though, I often find myself questioning its inner-workings.

Using our previous example, say you want to not only fetch users with paid invoices, but you also want to fetch them with their related `Invoice` and `Payment` records eagerly loaded. One way to achieve this would be using single-depth, chained `with()` methods.

As you've probably guessed, you can use dot notation for that too! So I'll save you the time and get to the interesting part: say you only want to load these related models but only with select attributes to limit memory usage. Using the `with()` method without dot notation would look something like this

```php
User::with('invoices', function ($invoices) {
	$invoices->select('id', 'amount', 'payment_id')
		->with('payment', function ($payment) {
			$payment->select('id', 'paid_at');
		});
})->get();
```

Sprinkle on some dot notation magic and...

```php
User::with('invoices:id,amount,payment_id', 'invoices.payment:id,paid_at')->get();
```

A clean, considerably easier to parse one-line equivalent. Clearly, this is more than dot notation alone and, as you can see, this additional functionality does not rely on dot notation. But, when paired, this duo can clean up otherwise long, deeply nested blocks of code.

> ℹ️ One thing to be mindful of when writing queries that select specific columns in general is to be sure to select `id` and foreign key columns relating to any models you wish to load.

But... what's actually going on here? Is performance impacted, for each level of nesting are we re-querying the parent models? The most important thing to note is that **both approaches result in the exact same query**. Meaning there are no redundant queries being made or any other performance implications one way or the other.

```sql
select * from `users` where `users`.`deleted_at` is null;

select `id`, `amount`, `payment_id` from `invoices` where `invoices`.`user_id` in (?) and `invoices`.`deleted_at` is null;

select `id`, `paid_at` from `payments` where `payments`.`id` in (1) and `payments`.`deleted_at` is null;
```

## Closing

Safe to say, where it makes sense to, dot notation is a safe and viable way to clean up some of your Laravel queries without running the risk of any performance implications as it does not effect the resulting query in any way. Hopefully this has saved you at least a few minutes of tinkering trying to answer these questions on your own.

> 🎉 Like the content? Feel free to let me know on [Twitter](https://twitter.com/colson_ryan) where I share all of my articles, packages, and occasional opinions (*mostly* technical).