CODEWITHSUNDEEP
phpmysqllaravel-5group-by
Mohammed
Mohammed

Reputation: 658

Laravel - group the record based on similar id in Laravel

How to group the table records based on similar id in Laravel For Example: in mysql products table have folllowing records with similar cat id Table name: products

id:1 pro_name:xyz1 cat_id:55
id:2 pro_name:xyz2 cat_id:22
id:3 pro_name:xyz3 cat_id:55
id:4 pro_name:xyz4 cat_id:22

What I am trying to do is for similar cat_id it should make its array. I Try to use

    $all = Products::groupBy('cat_id')->get();

But its displaying only first record of each similar cat_id.

output I am getting:

  {
    "id": 1,
    "pro_name": "xyz1",
    "cat": "55",
    "created_at": "2017-02-09 13:09:13",
    "updated_at": "2017-02-18 11:56:04"
  },
  {
     "id": 2,
    "pro_name": "xyz2",
    "cat": "22",
    "created_at": "2017-02-22 06:04:23",
    "updated_at": "2017-02-22 06:04:23"
  }

I want that it should display all record of similar id in an array Help me for this

Upvotes: 0

Views: 6985

Answers (3)

Zahid Gani
Zahid Gani

Reputation: 165

You should try this code...

$row = Products::selectRaw("pro_name,cat_id")->groupBy("cat_id","pro_name")->get();
            
echo "<pre>";
print_r($row);

Upvotes: 1

Elliot
Elliot

Reputation: 1616

Group by summarises your table, using the parameters given (hence it is only showing the first one, and in Naincy's answer, a total is calculated for the grouped elements).

Perhaps you are looking for Order By?

$info = DB::table('Products')
     ->orderBy('cat_id')
     ->get();

You will get the results from the whole table, sorted by the cat_id, and will have to detect when a category changes manually.

Another alternative is to get the distinct cat_id's, and then run your own query on them. You could encapsulate the code in the model or a repository. Note, this will run a number of queries on your database, which might not be preferable.

//Create an array
$groups = []

//Retrieve the list of cat_id's in use.
$cats = DB::table('Products')->distinct()->select('cat_id')->get()

//for each cat_id in use, find the products associated and then add a collection of those products to the relevant array element
foreach($cats as $cat){
    $groups[$cat->cat_id] = DB::table('Products')->where('cat_id', $cat->cat_id)->get();
}

Note: My only concern with what you are doing here is that you are really looking for relationships? If you used Eloquent, all of this becomes very easy.

Upvotes: 1

Naincy
Naincy

Reputation: 2943

Can try this

$info = DB::table('Products')
         ->select('cat_id', DB::raw('count(*) as total'), 
             DB::raw("GROUP_CONCAT(pro_name) AS product_name"))
         ->groupBy('cat_id')
         ->get();

Upvotes: 0

Related Questions