Some tables has multiple row in this case I want to add auto increment surfix number end of field name and make all data in single array

Question

I have more them 10 sql table with has employee details. some table have single row details like basic details table and this is main table. 2nd contact table I index this table with id=emp_id and leftjoin to get all data in single array. and also I have few more table like eduction which has multiple lines matches with emp_id and I want all the table data in single array. if repeated field value means ex. employee education field name education_level and it have 2 row I want result like education_level_1=>value, education_level_2=>value so on...

$employeeData = EmployeeBasicDetail::select(
            '*','employees_basic_details.id as emp_id',
            'employees_basic_details.client_id',)
        ->where('employees_basic_details.id',$emp_id)
        ->leftJoin('employees_company_details', 'employees_company_details.employee_id', '=', 'employees_basic_details.id')
        ->leftJoin('employees_contact_details', 'employees_contact_details.employee_id', '=', 
        ->first();

 $employeeEducations = EmployeeEducationDetail::where('employee_id',$emp_id)->get();
        $employeeEmploymentDates = EmployeeEmploymentDateDetail::where('employee_id',$emp_id)->get();

this code is ok as long as I send them in blade so I can make foreach and show repeated data in loop and show all other in employeeData ex:

return view('hrm.employees.create',compact('auth_user','auth_role','superadmin','employeeData','employeeEducations','employeeEmploymentDates'));

But I want to make excel download so, I modified code into

$data = EmployeeBasicDetail::select('employees_basic_details.employee_first_name', DB::raw('GROUP_CONCAT(employees_education_details.education_level SEPARATOR ",") as education_levels'))
            ->leftJoin('employees_education_details', 'employees_basic_details.id', '=', 'employees_education_details.employee_id')
            ->where('employees_basic_details.id', 1)
            ->groupBy('employees_basic_details.id', 'employees_basic_details.employee_first_name')
            ->first();
            //dd($data);
        if ($data !== null) {
            $employeeData = array(
                'employee_first_name' => $data->employee_first_name,
            );
            $education_levels = explode(',', $data->education_levels);
            foreach ($education_levels as $key => $level) {
                $employeeData['education_level_' . ($key + 1)] = $level;
            }
        } else {
            $employeeData = array();
        }

It's working I can get all data in single array I this code I need to mention all the fields in select and group and again in foreach I'm dealing with 15 tables and 120 fields total. is their any shorter way to achieve this. Please let me know If any more details need. I really like to her any idea to make it smarter way to deal this friends I hope it will interest you to give me answer. Thanks.

Answer 1

Define a model for the basic employee details

class EmployeeBasicDetail extends Model {
    public function companyDetails() {
        return $this->hasOne(EmployeeCompanyDetail::class, 'employee_id');
    }
    public function contactDetails() {
        return $this->hasOne(EmployeeContactDetail::class, 'employee_id');
    }
    public function educationDetails() {
        return $this->hasMany(EmployeeEducationDetail::class, 'employee_id');
    }
    public function employmentDates() {
        return $this->hasMany(EmployeeEmploymentDateDetail::class, 'employee_id');
    }
}

And in your controller method, get the basic employee details and eager load the related tables

$employeeData = EmployeeBasicDetail::with(['companyDetails', 'contactDetails', 'educationDetails', 'employmentDates'])
                    ->where('id', $emp_id)->first();

Now you can access related data using dot notation

echo $employeeData->companyDetails->company_name;
echo $employeeData->contactDetails->email;

To get education data in a single array, you can use map() method

$educationData = $employeeData->educationDetails->mapWithKeys(function ($item, $key) {
    return ['education_level_' . ($key + 1) => $item->education_level];
});

Merge all data into a single array

$employeeArray = array_merge($employeeData->toArray(), $educationData->toArray(), ...);

Finally, return the array or generate the excel download

return view('hrm.employees.create', compact('employeeArray'));