DEV Community

loading...

Cleaning up queries with dot notation

Ryan
I am a web developer interested in TDD, mentoring/giving back where possible, developer-first-DevOps, and Laravel.
ãƒģ4 min read

🔗 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

Arr::get($array, 'user.profile.billing_details.address');
Enter fullscreen mode Exit fullscreen mode

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

User::whereHas('invoices', function ($invoices) {
    $invoices->whereHas('payment');
})->get();
Enter fullscreen mode Exit fullscreen mode

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

User::whereHas('invoices.payment')->get();
Enter fullscreen mode Exit fullscreen mode

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:

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
Enter fullscreen mode Exit fullscreen mode

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

User::with('invoices', function ($invoices) {
    $invoices->select('id', 'amount', 'payment_id')
        ->with('payment', function ($payment) {
            $payment->select('id', 'paid_at');
        });
})->get();
Enter fullscreen mode Exit fullscreen mode

Sprinkle on some dot notation magic and...

User::with('invoices:id,amount,payment_id', 'invoices.payment:id,paid_at')->get();
Enter fullscreen mode Exit fullscreen mode

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.

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;
Enter fullscreen mode Exit fullscreen mode

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 where I share all of my articles, packages, and occasional opinions (mostly technical).

Discussion (0)