phplaravelquery-builderlaravel-7case-when

Add a key to a query builder in Laravel


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?


Solution

  • 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.