How to write below query in eloquent form. Eloquent seems good with the 2 table relationship, but with 4 table relationship and group_concat it is not working properly. The below code is working fine, just need to know how can I achieve the below code in eloquent format.
Relation goes like this
Tables > Columns :
mrs > id, name
mr_regions > id, mrs_id(mrs table), regions_id(regions table)
regions > id, name, headquaters_id(headquaters table)
headquaters > id, name
DB::table('mrs as m')
->select(
'm.id',
'first_name',
'last_name',
'email',
'designation',
'mobile_number',
DB::raw('GROUP_CONCAT(DISTINCT r.name SEPARATOR ", ") AS regions'),
DB::raw('GROUP_CONCAT(DISTINCT h.name SEPARATOR ", ") AS headquaters')
)
->join('mr_regions AS mr', function($join){
$join->on('mr.mrs_id', '=', 'm.id');
$join->on('mr.is_active', '=', DB::raw(Config('constants.STATUS_ACTIVE')));
})
->join('regions AS r', function($join){
$join->on('r.id', '=', 'mr.mrs_id');
$join->on('mr.is_active', '=', DB::raw(Config('constants.STATUS_ACTIVE')));
})
->join('headquaters AS h', function($join){
$join->on('h.id', '=', 'r.headquaters_id');
$join->on('h.is_active', '=', DB::raw(Config('constants.STATUS_ACTIVE')));
})
->where(function($where) use ($id){
$where->where('m.is_active', Config('constants.STATUS_ACTIVE'));
$where->where('m.id', '=', $id);
})
->groupby('m.id')
->first();