Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Jonjie's avatar
Level 12

How to get the list of items when using groupBy

I'm trying to use groupBy the payroll data using client field. It's working fine but my problem is that I also want to include all the items related to the grouped items. How can I do that? Please see my code below

PayrollController

Payroll::with('employees')
	->select('*')
	->groupBy('client')
	->get();

I tried to use the employees relationship but it only includes 1 employee data.

Payroll Model

public function employees()
{
	return $this->hasMany(Employee::class);
}

Expected output

[
	{
		client: 1,
		total: 1,821
		employee_ids: [1, 2, 3, 4, 29, 50, 89]
	},
	{
		client: 2,
		total: 3,098
		employee_ids: [5, 8, 15, 43]
	}
]
0 likes
7 replies
tykus's avatar

I also want to include all the items related to the grouped items

I don't understand; you have no aggregating function in the query - what are you hoping to select exactly?

MohamedTammam's avatar
Level 51

I didn't try it mysql, by according to the docs. You can JSON_ARRAYAGG aggregation function to convert rows into an array

Aggregates a result set as a single JSON array whose elements consist of the rows

1 like
Jonjie's avatar
Level 12

@MohamedTammam I think that is the one I'm looking for. But how can I use it in laravel? I tried to use it but it returns an error.

SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION myapp.JSON_ARRAYAGG does not exist

Select code

->addSelect([DB::raw('JSON_ARRAYAGG(employee_code) as  employee_codes')])

Please or to participate in this conversation.