CODEWITHSUNDEEP
phplaravellaravel-5query-builder
JsWizard
JsWizard

Reputation: 1749

Get monthly data from timestamp and sort by the same month

I tried to show table by monthly total duration, so I did sort by duration of same monthly data of timestamp, but I couldn't.

My goal is to create a table by return value that an object or array value.

public function monthlyResult(Request $request = null)
{
  $user_id = Auth::user()->id;

         if($request){
            $year = $request->year;
            $month = $request->month;
         }
         else{
            $year = date('Y');
         }


         $monthlyResult = DB::table('workings')
        ->where('user_id', $user_id)
        ->whereYear('created_at', $year)
        ->orwhere(function ($query) use($user_id, $year) {
            $query->where('user_id', $user_id)
            ->whereYear('deleted_at', $year);
        })
        ->get();

        foreach($monthlyResult as $mResult){
          $month = date('n', strtotime($mResult->created_at));
          $duration = $mResult->duration;

        ---- code ----
        }
   return ;
}

month | total duration |

----1--- | xxxxxxxx |

----2--- | xxxxxxxx |

----3--- | xxxxxxxx |

...

---11--- | xxxxxxxx |

---12--- | xxxxxxxx |

Upvotes: 0

Views: 109

Answers (1)

rkj
rkj

Reputation: 8287

You can create an associate array and sort by key 

$results = [];

foreach($monthlyResult as $mResult){
   $month = date('n', strtotime($mResult->created_at));
   $results[$month] = (isset($results[$month])) ? ($results[$month] + $mResult->duration) : $mResult->duration ;
}

//now sort $results based on key
ksort($results);

As mention by @C2486 you can directly sort your record using orderBy

$monthlyResult = DB::table('workings')
        ->where('user_id', $user_id)
        ->whereYear('created_at', $year)
        ->orwhere(function ($query) use ($user_id, $year) {
            $query->where('user_id', $user_id)
            ->whereYear('deleted_at', $year);
        })
        ->orderBy(DB::raw('month(created_at)'))
        ->get();

Upvotes: 1

Related Questions