CodeIgniter Forums
SelectMax Join Tables - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- 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.*, 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]


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

Hi.

Would a View work better in this case?

https://www.mysqltutorial.org/mysql-views-tutorial.aspx


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 ('home_department.name as home_department, customer_branch.name 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.

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