jeudi 31 mars 2016

get first and last value of a groupby object in laravel 5 and mysql

I have a table called income.

+----+-------------+---------------------+--------+--------+--------+-----------+---------------------+---------------------+
| id | employee_id | date                | gross  | income | credit | comission | created_at          | updated_at          |
+----+-------------+---------------------+--------+--------+--------+-----------+---------------------+---------------------+
|  1 |           1 | 2016-03-30 19:21:09 | 100.00 |  29.00 |  11.00 |     60.00 | 2016-03-31 19:21:46 | 2016-03-31 19:21:46 |
|  2 |           1 | 2016-03-31 19:24:44 | 110.00 |  43.00 |   1.00 |     60.00 | 2016-03-31 19:24:56 | 2016-03-31 19:24:56 |
|  3 |           2 | 2016-03-31 21:44:09 |  77.00 |  30.80 |   0.00 |     60.00 | 2016-03-31 21:44:19 | 2016-03-31 21:44:19 |
+----+-------------+---------------------+--------+--------+--------+-----------+---------------------+---------------------+

what i want to do is query these and group it by employee_id and i want the date it started and date it end. What i got is

$records = Income::whereBetween('date', [$start, $end])
                ->groupBy('employee_id')
                ->selectRaw('store_incomes.* , sum(gross) as total_gross , sum(income) as total_income, sum(credit) as total_credit')
                ->get();

This always return the first date for that employee. For example, employee_id 1 always return the date of 2016-03-30 19:21:09. What i want is to get the first and last date for each employee_id, so employee id 1 would have start date of 2016-30-30 and end date of 2016-03-31. Is there a way to do this without messy manual code?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire