Welcome Guest, Not a member yet? Register   Sign In
SelectMax Join Tables
#1

PHP Code:
/**
 * Returns all acquired applications info for a given user team/department id.
 *
 * @param integer $userID
 * @param integer $userPosition
 * @param integer $userDepartment
 *
 * @return mixed
 */
public function getAcquiredApplications(int $userIDint $userPositionint $userDepartment)
{
    return $this->select('applications.*, home_department.name as home_department, customer_branch.name as customer_branch, forms.code as form_code, forms.name as form_name, creator.username as creator, creator_position.name as creator_position, creator_division.name as creator_division,  creator_department.name as creator_department, submitter.username as submitter, submitter_position.name as submitter_position, submitter_division.name as submitter_division, submitter_department.name as submitter_department, assignor.username as assignor, application_owner.username as application_owner, application_position.name as application_position, application_division.name as application_division, application_department.name as application_department')
    ->join('departments home_department''home_department.id = applications.home_department_id''left')
    ->join('departments customer_branch''customer_branch.id = applications.customer_branch_id''left')
    ->join('forms''forms.id = applications.form_id''left')
    ->join('users creator''creator.id = applications.created_by''left')
    ->join('positions creator_position''creator_position.id = applications.creator_position_id''left')
    ->join('divisions creator_division''creator_division.id = applications.creator_division_id''left')
    ->join('departments creator_department''creator_department.id = applications.creator_department_id''left')
    ->join('users submitter''submitter.id = applications.submitted_by''left')
    ->join('positions submitter_position''submitter_position.id = applications.submitter_position_id''left')
    ->join('divisions submitter_division''submitter_division.id = applications.submitter_division_id''left')
    ->join('departments submitter_department''submitter_department.id = applications.submitter_department_id''left')
    ->join('positions application_position''application_position.id = applications.application_position_id''left')
    ->join('divisions application_division''application_division.id = applications.application_division_id''left')
    ->join('departments application_department''application_department.id = applications.application_department_id''left')
    ->join('users assignor''assignor.id = applications.stage_assigned_by''left')
    ->join('users application_owner''application_owner.id = applications.application_owner_id''left')
    ->where([
        'applications.stage' => 2,
        'applications.application_owner_id' => $userID,
        'applications.application_position_id' => $userPosition,
        'applications.application_department_id' => $userDepartment
    
])
    ->orderBy('applications.submitted_at''DESC')
    ->findAll();


[Image: 0001.jpg]

I want to selectMax('applications.sequence')
NOTE: I don't want to use select('applications.application_number, applications.application_name) because my table have more than 30 column and I need to retrieve all of them.

I expect query returns
[Image: 0002.jpg]
Reply


Messages In This Thread
SelectMax Join Tables - by Mostafa Khudair - 05-08-2021, 12:50 PM
RE: SelectMax Join Tables - by kleber - 05-09-2021, 05:14 AM
RE: SelectMax Join Tables - by wdeda - 05-09-2021, 11:29 AM
RE: SelectMax Join Tables - by Mostafa Khudair - 05-09-2021, 03:52 PM
RE: SelectMax Join Tables - by craig - 05-10-2021, 01:09 AM



Theme © iAndrew 2016 - Forum software by © MyBB