How to use laravel group by with example ?

MySQL group by is used to group same column value multiple rows into one row so to create the group by query we used groupBy method. laravel groupBy method accepts single and multiple parameter as column name.

In this article i will show you to use groupBy in multiple ways, laravel also provide to pass raw query to group so we can pass multiple parameter to group by.

Here is the syntax of groupBy

Article::select("status")->groupBy('status');  // uses mysql group by method and more performant

//or

Article::select("status")->groupBy(DB::raw('status as is_active'))->first();  // uses mysql group by method but we can pass raw sql query

SQL of above query will be

select status from `table` group by status

//OR

select statusfrom `table` group by status as is_active

So let’s understand laravel group by rows with example

Example 1 – Laravel groupBy method with single column

In this example I will show to use MySQL group by method in laravel eloquent. groupBy can accept multiple column and we will pass single column in this example

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\User;
  
class ArticleController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $Article = Article::select("*")->groupBy('status');
      \\or
      DB::table('articles')->groupBy("status");

   }
}

Output will be :

select `statsu` from `articles` group by `status`

Example 2 – Laravel group by with multiple column

In this example I will show to use laravel eloquent group by method with multiple column in group by

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\User;
  
class ArticleController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $Article = Article::select("select","is_active")->groupBy("select","is_active");
         
    }
}

Output will be :

select `status`,`is_active` from `articles` group by `status`,`is_active`

Here we passed multiple parameter to groupBy method which is acceptable by laravel eloquent and query builder

Example 3 – Laravel group by with raw query

Sometimes we need to pass raw query like count or some aggregate function in group by query so we can use as below

<?php
  
namespace App\Http\Controllers;
  
use Illuminate\Http\Request;
use App\User;
  
class ArticleController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $Article = Article::select("select")->groupBy(\DB::raw('status as newstatus'));
         
    }
}

Output will be :

select `status` from `articles` group by status as newstatus

I hope this makes clear to you.

Also read : How to use laravel count rows with example ?

Related

Related Posts