Add a key to a query builder in Laravel

Question

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?

Answer 1

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.

Answer 2

You can do this in the query directly:

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',
    DB::raw('... AS can_approve')
)

If you had an eloquent model, you could even solve this via an accessor.

Answer 3

You can add new keys and values in many ways but as your custom logic do the following way:

$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);
});

//Add custom keys here...
if(Auth::user()->manager_rank == $form->approve_state){
    $list = $list->addSelect(DB::raw("'true' as can_approve"));
}else{
    $list = $list->addSelect(DB::raw("'false' as can_approve"));
}

$list = $list->paginate(2);

Answer 4

you could do something like this after pagination

foreach ($list as $item)
        {
            //do your logic here 
            $item->can_ approve = 'false';
        }