如果使用查询构建器来处理其laravel形式的内容？

Question

I have a query sql like the following may be a bit complex, I am a bit of trouble to convert into a for framework laravel. Please help for everything with framework laravel query results. My problem here is, I do not know how to create a sub select query to laravel framework. Thanks guys.

SELECT
 lin_users.status_employee_id,
  lin_users.id,
  lin_users.username,
  lin_users.created,
  lin_users.modified,
  lin_employee_attributes.unit_code,
  lin_employee_attributes.position_code,
  lin_employee_attributes.begin_date,
  lin_employee_attributes.end_date,
  contactnumber.contact_id as phone_number,
  contactmobile.contact_id as cell_number,
  contactemail.contact_id as email

FROM lin_users
  INNER JOIN lin_status_employees
    ON lin_users.status_employee_id = lin_status_employees.id

  INNER JOIN lin_people
    ON lin_status_employees.person_id = lin_people.id

  INNER JOIN lin_employee_attributes
    ON lin_users.status_employee_id = lin_employee_attributes.status_employee_id

  LEFT JOIN lin_contacts AS contactnumber
    ON lin_people.id = contactnumber.person_id AND contactnumber.contact_type = 'Work Telephone'

  LEFT JOIN lin_contacts AS contactmobile
    ON lin_people.id = contactmobile.person_id AND contactmobile.contact_type = 'Mobile'

  LEFT JOIN lin_contacts AS contactemail
    ON lin_people.id = contactemail.person_id AND contactemail.contact_type = 'Email'
WHERE lin_employee_attributes.begin_date = '2016-11-07'
      OR lin_employee_attributes.end_date = '2017-10-21'
GROUP BY lin_users.id,
  lin_employee_attributes.unit_code,
  lin_employee_attributes.position_code,
  lin_employee_attributes.begin_date,
  lin_employee_attributes.end_date, lin_people.id,
  contactnumber.contact_id,
  contactmobile.contact_id,
  contactemail.contact_id;

Answer 1

Using this work. Finally, with the above query can be solved with the concept query to query in laravel.

$users = $this->db->getTable('users')
            ->select('users.status_employee_id',
                     'users.id',
                     'users.username',
                     'users.email',
                     'users.created',
                     'users.modified',
                     'users.flag_delete',
                     'employee_attributes.unit_code',
                     'employee_attributes.position_code',
                     'employee_attributes.begin_date',
                     'employee_attributes.end_date',
                     'contactnumber.contact_id as phone_number',
                     'contactmobile.contact_id as cell_number',
                     'contactemail.contact_id as email'
                    )
            ->join('status_employees', 'users.status_employee_id', '=', 'status_employees.id')
            ->join('people', 'status_employees.person_id', '=', 'people.id')
            ->join('employee_attributes', 'users.status_employee_id', '=', 'employee_attributes.status_employee_id')
            ->leftJoin('contacts AS contactnumber', function($join)
                     {
                         $join->on('people.id', '=', 'contactnumber.person_id');
                         $join->on('contactnumber.contact_type','=', DB::raw("'Work Telephone'"));
                     })
             ->leftJoin('contacts AS contactmobile', function($join)
                     {
                          $join->on('people.id', '=', 'contactmobile.person_id');
                          $join->on('contactmobile.contact_type','=', DB::raw("'Mobile'"));
                     })
              ->leftJoin('contacts AS contactemail', function($join)
                     {
                         $join->on('people.id', '=', 'contactemail.person_id');
                         $join->on('contactemail.contact_type','=', DB::raw("'Email'"));
                     })

            ->where(function ($query) use ($begin_date, $end_date) {
                $query->where('employee_attributes.begin_date', $begin_date)
                      ->orWhere('employee_attributes.end_date', $end_date);
            })
            ->groupby('users.status_employee_id',
                      'users.id',
                      'users.username',
                      'users.email',
                      'users.created',
                      'users.modified',
                      'users.flag_delete',
                      'employee_attributes.unit_code',
                      'employee_attributes.position_code',
                      'employee_attributes.begin_date',
                      'employee_attributes.end_date',
                      'contactnumber.contact_id',
                      'contactmobile.contact_id',
                      'contactemail.contact_id'
                    )
            ->get();