CODEWITHSUNDEEP
phpmysqllaraveleloquent
XcOder
XcOder

Reputation: 147

Eloquent Merge Queried rows from Model

I have 2 Models

Models/Tasks

protected $fillable = [
    'id',
    'date',
    'workdone',
    'duration',
    'location',
];

public function Duty()
{
    return $this->belongsTo(Duty::class, 'duty_id', 'id');
}

public function TaskType()
{
    return $this->belongsTo(TaskType::class, 'TaskType_id', 'id');
}

Models/Duty

protected $fillable = [
        'id',
        'date',
        'vehicle_number',
        ...other fields
    ];

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

In my controller I am fetching all the tasks for the user like this

 $records = Tasks::all()
                ->with('TaskType')
                ->get();

NOTE: The date in Models/Tasks can vary for each task by +/- 1 day. eg. Two of three assigned tasks on duty day number 4 maybe recorded as 2022/4/22 and the third task assigned on the same day maybe recorded as the next day(2022/4/23). However all three tasks will belong to the Duty day number 4 have duty day number 4s id as their foreign key

and all is well and this data is returned.

Date Duration Work Done Location
2021/12/04 00:30 Hammering Barn
2021/12/04 01:15 Ironing Basement
2021/12/04 00:15 Jumping Backyard
2021/12/05 00:45 Sweeping Kitchen
2021/12/05 00:35 Weeding Garden

In a variation, I would like combine all the tasks in one day as follows using ORM. I'm new to eloquent and I have no idea how to approach it even. So the date column would have just the date once, Duration would be summed up and work done combined by commas and so on...

Date Duration Work Done Location
2021/12/04 02:00 Hammering, Ironing, Jumping Barn, Basement, Backyard
2021/12/05 01:20 Sweeping, Weeding Kitchen, Garden

Is this possible with Eloquent or should I take a recursive in PHP approach? TIA

Upvotes: 0

Views: 58

Answers (1)

Saroj Shrestha
Saroj Shrestha

Reputation: 2875

You need something like this:

$tasks = Tasks::with('TaskType')->get()->groupBy('date');

$final_arr = [];
foreach($tasks as $tk => $tv){
    $temp_arr = [];
    foreach($tv as $k => $v){
        if($k === 0){
            $temp_arr = [
                'date'      => $v->date,
                'duration'  => $v->duration,
                'work_done' => $v->work_done,
                'location'  => $v->location
            ];
        }else{
            $temp_arr['duration'] = date("H:i:s",strtotime($temp_arr['duration'])+strtotime($v->duration));
            $temp_arr['work_done'] .= ', '.$v->work_done;
            temp_arr['location'] .= ', '.$v->location;
        }
    }
    $final_arr[] = $temp_arr;
}
dd($final_arr);

I have not tested this code, but it should be something like this, hope it should give you an idea.

  • You don't need to write all() when you are writing get() there
  • groupBy will just group those data on date key so, all the data from same date will be inside same array. you can dd($tv) and you should see all those data from same date
  • In case of first index 0, it is just saving the data straight forward, on other case it is just adding value or concatenating value.

Upvotes: 1

Related Questions