I'm trying to figure out if it's possible to add a key that is not a table's column.
Query code:
$list = DB::table('ouin_request_base as base')
->select(
'base.request_id',
'users.last_name as last_name',
'users.first_name as first_name',
'division.name as division_name',
'base.created_at',
'status.name as status_name'
)
->join('users', 'base.user_id' , '=', 'users.id')
->join('ouin_request_data as data', 'base.request_id', '=', 'data.request_id')
->join('m_request_status as status', 'base.request_state', '=', 'status.ouin')
->join('m_divisions as division', 'users.division_id', '=', 'division.id')
->when(isset($search_name), function($query) use ($search_name){
return $query->where('users.id', '=', $search_name);
})
->when(isset($search_division), function($query) use ($search_division){
return $query->where('division.id', '=', $search_division);
})
->when(isset($search_status), function($query) use ($search_status){
return $query->where('status.ouin', '=', $search_status);
})
->paginate(2);
Query builder result:
#items: array:2 [▼
0 => {#1356 ▼
+"request_id": "1-20210802142739"
+"last_name": "last_name"
+"first_name": "first_name"
+"division_name": "OA"
+"created_at": "2021-08-02 14:27:39"
+"status_name": "state"
}
1 => {#1427 ▼
+"request_id": "1-20210802171508"
+"last_name": "last_name"
+"first_name": "first_name"
+"division_name": "OA"
+"created_at": "2021-08-02 17:15:08"
+"status_name": "state"
}
]
What I want to add is checking weather the current logged-in user can approve these forms by checking if(Auth::user()->manager_rank == $form->approve_state)
(manager_rank and approve_state are integers) and add 'can_approve' key to the collection before paginating.
the $form is an example for the individual forms from the 'ouin_request_base' table. so if the manager_rank of the user is equals to the state of the individual form, can_approve will be true.
Example:
#items: array:2 [▼
0 => {#1356 ▼
+"request_id": "1-20210802142739"
+"last_name": "last_name"
+"first_name": "first_name"
+"division_name": "OA"
+"created_at": "2021-08-02 14:27:39"
+"status_name": "state"
+"can_approve": "true"
}
1 => {#1427 ▼
+"request_id": "1-20210802171508"
+"last_name": "last_name"
+"first_name": "first_name"
+"division_name": "OA"
+"created_at": "2021-08-02 17:15:08"
+"status_name": "state"
+"can_approve": "false"
}
]
Is it possible to use the when clause to add 'can_approve' to the collection before the pagination?
I tried this and it works so I'm going to post my codes here.
$list =
DB::table('ouin_request_base as base')
->select(
'base.request_id',
'users.last_name as last_name',
'users.first_name as first_name',
'division.name as division_name',
'base.created_at',
'status.name as status_name',
'base.request_state as can_approve'
)
->join('users', 'base.user_id', '=', 'users.id')
->join('ouin_request_data as data', 'base.request_id', '=', 'data.request_id')
->join('m_request_status as status', 'base.request_state', '=', 'status.ouin')
->join('m_divisions as division', 'users.division_id', '=', 'division.id')
->when(isset($search_name), function ($query) use ($search_name)
{
return $query->where('users.id', '=', $search_name);
})
->when(isset($search_division), function ($query) use ($search_division)
{
return $query->where('division.id', '=', $search_division);
})
->when(isset($search_status), function ($query) use ($search_status)
{
return $query->where('status.ouin', '=', $search_status);
})->paginate(2);
foreach($list as $item)
{
$item->can_approve = Auth::user()->manager_rank == $item->can_approve ? true : false;
}
I added 'base.request_state as can_approve'
to the select query to get the individuals forms request_state which is an int. and then outside the query, I loop my list and checked if the manager_rank of the current logged in user is the same as the request_state of the idividual form.