Laravel Eloquent Mastery: 30 Pro Tips to Supercharge Your Queries (Series-4)

• Scenario 16: Advanced Queries with Raw Expressions
• Scenario 17: Optimal Use of increment and decrement
• Scenario 18: Dynamic Where Clauses with where*
• Scenario 19: Using having in Aggregated Queries
• Scenario 20: Relation Existence Queries

Let's continue unveiling more advanced scenarios and best practices to utilize Laravel Eloquent's capabilities to their fullest.

#Scenario 16: Advanced Queries with Raw Expressions

Good Practice: Use raw expressions judiciously for complex queries that aren't easily handled by Eloquent's fluent methods.

// Using a raw expression to get the total amount spent by users
$users = App\\Models\\User::select([
    'users.*',
    \\DB::raw('(SELECT SUM(orders.amount) FROM orders WHERE orders.user_id = users.id) AS total_spent')
])->get();

Bad Practice: Executing separate queries to calculate aggregates can be less efficient.

$users = App\\Models\\User::all()->each(function ($user) {
    $user->total_spent = $user->orders->sum('amount');
});

#Scenario 17: Optimal Use of increment and decrement

Good Practice: Utilize the increment and decrement methods for updating numeric columns efficiently.

// Increment the view count without loading the model
App\\Models\\Post::where('id', 1)->increment('view_count');

Bad Practice: Manually incrementing and saving the model, which is less efficient.

$post = App\\Models\\Post::find(1);
$post->view_count++;
$post->save();

#Scenario 18: Dynamic Where Clauses with where*

Good Practice: Use Eloquent’s dynamic where methods to build expressive queries.

// Retrieve posts with status 'published' using dynamic where
$posts = App\\Models\\Post::whereStatus('published')->get();

Bad Practice: Using generic where clauses for columns that could benefit from dynamic where.

// Less expressive
$posts = App\\Models\\Post::where('status', 'published')->get();

#Scenario 19: Using having in Aggregated Queries

Good Practice: Use the having clause in conjunction with group by for conditions on aggregated data.

// Select users with more than 10 orders
$users = App\\Models\\User::groupBy('id')
    ->having(DB::raw('COUNT(orders.id)'), '>', 10)
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->get();

Bad Practice: Retrieving all data and filtering in-memory.

$users = App\\Models\\User::all()->filter(function ($user) {
    return $user->orders->count() > 10;
});

#Scenario 20: Relation Existence Queries

Good Practice: Utilize relation existence queries to filter models based on relationships elegantly.

// Retrieve posts that have comments
$posts = App\\Models\\Post::has('comments')->get();

Bad Practice: Loading models and their relationships without leveraging query scopes.

// Inefficient if you just want to know existence
$posts = App\\Models\\Post::all()->filter(function ($post) {
    return $post->comments->isNotEmpty();
});

By applying these techniques, you'll be able to construct more performant and expressive queries that leverage the full range of Eloquent's ORM capabilities. Invoking query scopes, making use of Eloquent's dynamic methods, and knowing when to use raw expressions can significantly reduce boilerplate and maximize efficiency in your applications. Keep honing your Eloquent skills and embrace the art of creating eloquent and effective queries! Stay tuned for even more advanced scenarios and expert tips in future blog entries.

Stay tuned as we continue our deep dive into Eloquent's less-traveled paths and reveal more secrets to mastering this ORM.

If you have missed our earlier series you can

Read series 1

Read series 2

Read series 3