How to Query Relationship in Complex Raw DB using Laravel

Question

I have this models in Laravel-5.8:

Here are the models:

class Employee extends Model
{
    public $timestamps = false;
    protected $table = 'employees';
    protected $primaryKey = 'id';

    protected $fillable = [
                  'id',
                  'first_name',
                  'last_name',
                  'hr_status',      
                  'employee_type_id',
              ];

    public function employeetype()
    {
        return $this->belongsTo('AppModelsHrEmployeeType','employee_type_id','id');
    }           
}

class EmployeeType extends Model
{
    public $timestamps = false;
    protected $table = 'employee_types';
    protected $primaryKey = 'id';

    protected $fillable = [
                  'type_name',
                  'is_active',
              ];
}

Then I have this Query in Employee controller function:

The DB Raw query is shown below:

    $published = DB::table('employees AS e')
                ->leftJoin('goals AS a', function($join) use ($identities)
                    {
                        $join->on('a.employee_id', '=', 'e.id')
                        ->where('a.identity_id', '=', $identities)
                        ->whereNull('a.deleted_at');
                    })
                ->join('departments AS d', function($join) use ($userCompany)
                    {
                        $join->on('e.department_id', '=', 'd.id')
                        ->where('d.company_id', '=', $userCompany);
                    })                        
                ->leftJoin('employees AS em', function($join) use ($userCompany)
                    {
                        $join->on('em.employee_code', '=', 'e.line_manager_id')
                        ->where('em.company_id', '=', $userCompany)
                        ->where('em.hr_status', '=', '0')
                        ->where('em.validation_status', '=', 'VALID');
                    }) 
                ->leftJoin('employees AS emm', function($join) use ($userCompany)
                    {
                        $join->on('emm.employee_code', '=', 'em.line_manager_id')
                        ->where('emm.company_id', '=', $userCompany)
                        ->where('emm.hr_status', '=', '0')
                        ->where('emm.validation_status', '=', 'VALID');
                    })                        
                ->leftJoin('employees AS eh', function($join) use ($userCompany)
                    {
                        $join->on('eh.employee_code', '=', 'd.hr_business_partner_id')
                        ->where('eh.company_id', '=', $userCompany)
                        ->where('eh.hr_status', '=', '0')
                        ->where('eh.validation_status', '=', 'VALID');
                    })    
                ->where('e.company_id', '=', $userCompany)
                ->where('e.hr_status', '=', '0')
                ->where('e.validation_status', '=', 'VALID')
                ->select(
                        'e.employee_code',
                        DB::raw('CONCAT(e.first_name, " ", e.last_name) AS fullname'),
                        'e.email',
            DB::raw('(CASE WHEN a.is_approved = 0 THEN "DRAFT" WHEN a.is_approved = 1 THEN "AWAITING APPROVAL" WHEN a.is_approved = 2 THEN "NOT APPROVED" WHEN a.is_approved = 3 THEN "APPROVED" ELSE "NOT STARTED" END) AS is_approved'),
                        DB::raw('(CASE WHEN a.line_manager_mid_year_approved = 0 THEN "DRAFT" WHEN a.line_manager_mid_year_approved = 1 THEN "AWAITING APPROVAL" WHEN a.line_manager_mid_year_approved = 2 THEN "NOT APPROVED" WHEN a.line_manager_mid_year_approved = 3 THEN "APPROVED" ELSE "NOT STARTED" END) AS line_manager_mid_year_approved'),
                        'd.dept_name',
                        'l.location_name',
                        'e.grade_level_name',
                        DB::raw('CONCAT(em.first_name, " ", em.last_name) AS manager'),
                        'em.email AS manager_email',
                        DB::raw('CONCAT(emm.first_name, " ", emm.last_name) AS manager_manager'),
                        DB::raw('CONCAT(eh.first_name, " ", eh.last_name) AS hrbp')
                       )
                ->distinct()
                ->get(); 

employee_types is another table. How do I include employee_types.is_active = 1;

->where('e.employee_type_id', '=', employee_types.id)
->where('em.employee_type_id', '=', employee_types.id)
->where('eh.employee_type_id', '=', employee_types.id)
->where('emm.employee_type_id', '=', employee_types.id)

in appropriate places (especially the leftjoins for employees) in the query: $published as shown above

question from:https://stackoverflow.com/questions/65926334/how-to-query-relationship-in-complex-raw-db-using-laravel

Answer 1

Waitting for answers