5 Useful Examples from groupBy to Improve Your Laravel Skills

Powerful PHP framework Laravel is renowned for its beautiful syntax and wide range of database-related features. One of its key advantages is the ability to employ the GROUP BY clause in database queries.. We will look at how to use GroupBy in Laravel with a number of examples covering from simple to complex queries in this tutorial.By the end of this post, you should have a better understanding of how to use this functionality to improve your Laravel skills..

Example 1: Basic GroupBy

Let's begin with a simple example. Let's say we wish to group the orders in a database table called orders according to their status:

$orders = DB::table('orders')
    ->select('status', DB::raw('count(*) as total'))
    ->groupBy('status')
    ->get();

Example 2: Grouping with Relationships

In this example, we have two related tables: orders and products. We want to group orders by the product category:

$orders = DB::table('orders')
    ->join('products', 'orders.product_id', '=', 'products.id')
    ->select('products.category', DB::raw('count(*) as total'))
    ->groupBy('products.category')
    ->get();

Example 3: Grouping with Date Intervals

$sales = DB::table('sales')
    ->select(DB::raw('YEAR(created_at) as year, MONTH(created_at) as month'), DB::raw('SUM(amount) as total'))
    ->groupBy('year', 'month')
    ->get();

You can also perform multiple groupBy operations in a single query. Here, we're grouping by both category and status:

$orders = DB::table('orders')
    ->select('category', 'status', DB::raw('count(*) as total'))
    ->groupBy('category', 'status')
    ->get();

Example 5: Advanced Subquery Grouping

In more complex scenarios, you may want to group by the result of a subquery. This example groups orders by the average price of the products in each category:

$orders = DB::table('orders')
    ->select('category', DB::raw('count(*) as total'))
    ->groupBy(DB::raw('(SELECT AVG(price) FROM products WHERE products.category = orders.category)'))
    ->get();

Conclusion:

For data analysis and reporting, Laravel's groupBy clause gives up a world of options. Laravel provides a flexible and effective way to use the power of SQL's GROUP BY clause, whether you're working with basic grouping, relationships, date intervals, multiple groupings, or complex subqueries.. Think about trying out with group searches to make your database searches more successful and informative.