Laravel Grouping by Eloquent Relationship

Question

How can I group by relation?

Example

Sales::with('product_detail.product')->groupBy('product_name')->get()

How can I get a result with eloquent code?

Answer 1

You can specify a callback function for grouping your relation like this:

Sales::with(['product_detail.product' => function($query){
        $query->groupBy('product_name');
    }])->get();

Answer 2

I think the top answer is not answering the question. As I understand, the aim is to group the results of the outer query, not the relationship.

This is not possible in an Eloquent-y DB call because the call for the With is separate. You can manually join the tables and group by the required column.

If you want to stay Eloquent-y, there are many convenient methods available to Collections, including GroupBy. So you can get the data first and group later.

Answer 3

This may already be old but yeah, it can help a seeker.

You can chain directly to relationships/distant relationships with groupBy in Laravel. I don't know if this is specific to any version but V8 works. And the relationship is loaded as well like you would use with.

 Sales::query()->where('this', $that)->get()->groupBy('product.name'); // OR

 Sales::query()->where('this', $that)->get()->groupBy('product.status.name');

Answer 4

laravel >= 7 you can use:

In controller

$user->sales()->groupBy('product_name')->get();

In User.php file :

public function sales(){
     return $this->hasMany('App\Sales','user_id');
}

Answer 5

Group by distant relation:

Sales::select(DB::raw('count(*) as count'), 'products.name')
  ->join('product_details', 'sales.product_detail_id', '=', 'product_details.id')
  ->join('products', 'product_details.product_id', '=', 'products.id')
  ->groupBy('products.name')
  ->get();