How to order by last name in laravel controller return value using foreach loop

Question

I tried to put orderBy(hr_workers.l_name) before get() but it's not working.. also tried to put it on $workers but still not working... I don't have any idea why isn't working... anyone can help me all answer must be appreciated thanks in advance

I want the result is alphabetically arrange by last name

public function show6($id)
    {

    $workers = DB::table('hr_workers')
    ->select('wrk_id','f_name','m_name','l_name');

    $attendance = DB::table('payroll_daily_attendance')->where('payroll_daily_id', $id)
    ->select('payroll_daily_attendance.*','f_name','l_name','m_name')
    ->rightjoinSub($workers,'worker', function($join){
        $join->on('payroll_daily_attendance.wrk_id','=','worker.wrk_id');
    });
   
    $payroll = DB::table('payroll_daily_details')
    ->select('payroll_daily_details.*','wrk_id','f_name','m_name','l_name','attendance.*')
    ->rightjoinSub($attendance,'attendance', function($join){
        $join->on('payroll_daily_details.payroll_daily_id','=','attendance.payroll_daily_id');
    })
    
    ->get();

    $fetch = [];
    foreach($payroll as $key){
         if(!isset($fetch[$key->wrk_id]['total_ot']) && !isset($fetch[$key->wrk_id]['total_days']) && !isset($fetch[$key->wrk_id]['grand_total'])){
            $fetch[$key->wrk_id]['total_ot'] = 0;
            $fetch[$key->wrk_id]['total_days'] = 0;
            $fetch[$key->wrk_id]['total_allowance'] = 0;
            $fetch[$key->wrk_id]['grand_total'] = 0;
          }
            $fetch[$key->wrk_id]['wrk_id'] = $key->wrk_id;
            $fetch[$key->wrk_id]['f_name'] = $key->f_name;
            $fetch[$key->wrk_id]['l_name'] = $key->l_name;
            $fetch[$key->wrk_id]['m_name'] = $key->m_name;
            $fetch[$key->wrk_id]['total_days'] += $key->reg_hour + $key->adj_hour;
            $fetch[$key->wrk_id]['total_allowance'] += $key->allowance;
         
          
      }
      return $fetch;


    }

`hr_workers` is not a valid alias at the top level of your query, and ordering in a subquery without limit is generally ignored. But you should be able to `orderBy('l_name')` at the top level — Nick, Apr 13 '21 at 02:11
not working bro.. ordering by last name doesn't affect when I used foreach to return value? — Not a Pro, Apr 13 '21 at 02:17
@Nick try to return $payroll orderBy('l_name') is working but on my code I need to return the data which in the $fetch — Not a Pro, Apr 13 '21 at 02:20
The `$fetch` array should have the same `l_name` ordering as the `$payroll` result set, assuming the `wrk_id` values are always associated with the same `l_name`. How are you displaying the results? — Nick, Apr 13 '21 at 02:26
I'm using vuejs for the front end.. The result of `$fetch` in to network tab is isn't ordered by l_name but when I try to return the $payroll Its working.. but in my case I need to return the data from `$fetch` not in `$payroll` — Not a Pro, Apr 13 '21 at 02:31
Do some of the `wrk_id` values have more than one `l_name` associated with them? That could account for it. In which case you would need to sort `$fetch` by the `l_name` value, see for example https://stackoverflow.com/questions/2699086/how-to-sort-multi-dimensional-array-by-value — Nick, Apr 13 '21 at 02:33
before `return $fetch` use `usort($fetch, function($a, $b) { return $a['l_name'] <=> $b['l_name']; });` — Nick, Apr 13 '21 at 02:39

Nick · Accepted Answer · 2021-04-13T02:50:48.773

Because there can be multiple l_name values associated with each wrk_id in the $fetch array, any l_name ordering in the query will be overwritten because of the way the $fetch array is being generated. You need to sort the $fetch array by the l_name value before you return it, which you can do using this method (derived from this Q&A):

usort($fetch, function($a, $b) { return $a['l_name'] <=> $b['l_name']; });

score 0 · Answer 2 · answered Apr 13 '21 at 02:18

0

Use:

$payroll = DB::table('payroll_daily_details')
->select('payroll_daily_details.*','wrk_id','f_name','m_name','l_name','attendance.*')
->rightjoinSub($attendance,'attendance', function($join){
    $join->on('payroll_daily_details.payroll_daily_id','=','attendance.payroll_daily_id');
})
->orderBy('l_name')
->get();

answered Apr 13 '21 at 02:18

Euclides Cardoso Júnior

16
4

Can you send more information about the error? – Euclides Cardoso Júnior Apr 13 '21 at 02:24
It display the data but isn't sorted by l_name – Not a Pro Apr 13 '21 at 02:25

score 0 · Answer 3 · answered Apr 13 '21 at 02:43

As was highlighted by Euclides Cardoso Junior. I believe for you to have your results arranged alphabetically you need to put the second parameter to the orderBy function to entail the order to be followed and also put the group by function. Check the code below;
$yourQuery->orderBy('l_name', 'asc') ->groupBy('l_name') ->get();

Hope this is helpful.

How to order by last name in laravel controller return value using foreach loop

3 Answers3