CodeIgniter Forums
SelectMax Join Tables - Printable Version

+- CodeIgniter Forums (
+-- Forum: Using CodeIgniter (
+--- Forum: General Help (
+--- Thread: SelectMax Join Tables (/showthread.php?tid=79193)

SelectMax Join Tables - Mostafa Khudair - 05-08-2021

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.*, as home_department, as customer_branch, forms.code as form_code, as form_name, creator.username as creator, as creator_position, as creator_division, as creator_department, submitter.username as submitter, as submitter_position, as submitter_division, as submitter_department, assignor.username as assignor, application_owner.username as application_owner, as application_position, as application_division, as application_department')
    ->join('departments home_department'' = applications.home_department_id''left')
    ->join('departments customer_branch'' = applications.customer_branch_id''left')
    ->join('forms'' = applications.form_id''left')
    ->join('users creator'' = applications.created_by''left')
    ->join('positions creator_position'' = applications.creator_position_id''left')
    ->join('divisions creator_division'' = applications.creator_division_id''left')
    ->join('departments creator_department'' = applications.creator_department_id''left')
    ->join('users submitter'' = applications.submitted_by''left')
    ->join('positions submitter_position'' = applications.submitter_position_id''left')
    ->join('divisions submitter_division'' = applications.submitter_division_id''left')
    ->join('departments submitter_department'' = applications.submitter_department_id''left')
    ->join('positions application_position'' = applications.application_position_id''left')
    ->join('divisions application_division'' = applications.application_division_id''left')
    ->join('departments application_department'' = applications.application_department_id''left')
    ->join('users assignor'' = applications.stage_assigned_by''left')
    ->join('users application_owner'' = applications.application_owner_id''left')
        'applications.stage' => 2,
        'applications.application_owner_id' => $userID,
        'applications.application_position_id' => $userPosition,
        'applications.application_department_id' => $userDepartment

[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]

RE: SelectMax Join Tables - kleber - 05-09-2021


Would a View work better in this case?

RE: SelectMax Join Tables - wdeda - 05-09-2021

I am not a MySql expert, but I did not find any reference to 'selectmax', I believe it would be a redundancy to select ('*') and/or SELECT ALL.
As you need to 'rename' some rows and without knowing if it can work, I suggest trying:
PHP Code:
-> select ('*'// and after that the rows that need to be 'renamed'
-> select (' as home_department, as customer_branch, // and so on'

RE: SelectMax Join Tables - Mostafa Khudair - 05-09-2021

I want to select rows with max sequence of each application_name opener and closer
to get the result in the second photo

RE: SelectMax Join Tables - craig - 05-10-2021

What about this? Reduced for clarity.

SELECT MAX(applications.sequence)
FROM applications
GROUP BY applications.application_number