Laravel: How to order date, change the date format and group by the date?

I have a date list in the database and from the list, I get the month and despite lay in the dropdown. Right now it works but I don’t know how to order the date by ascending before I change the format. Below is the date list:

date
2021-08-02
2021-09-30
2021-08-01
2021-09-06
2021-11-05

Right now in the dropdown display

August
November
September

It should be

August
September
November

Here’s my eloquent to display the dropdown

$date = DateList::where('id',$id->id)->select(DB::raw("(DATE_FORMAT(date, '%M')) as newDate"))->groupBy('newDate')->get();

I have a search around but still could not find the answer on how to get the correct way to order the date before changing it to the format. Hope anyone can help.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Why don’t you use laravel’s awesome collection api?

DateList::where('id',$id->id)
  ->orderBy('date', 'ASC')
  // uncomment the below line if you're concerned with performance
  // ->select('date')
  ->pluck('date')
  ->map(function($date) {
    return $date->format("F");
  })
  ->unique();

Method 2

Hey GarfieldJuniorI couldn’t think any way to implement the above but the way I would suggest is to fetch month’s number instead of name and sort it then map the array/Collection to Months name,
for mapping thing either you can user index array of like

$monthNames=[
  1=>"JANUARY",
  2=>"FABRUARY
]

OR

you can use by creating a dummy date by months Number using date() method of php, It takes format type of string as first argument and second $timestamp
$format ='F' ,
and
$timestamp=mktime(0,0,0,$monthNumber)
ref mktime
so in short your if you get sorted months number Array like this (I’m using date method not indexed Array)

$monthsArray=[1,2,3];
$monthNames=array_map(
      function($monthNumber){
           return date("F", mktime(0, 0, 0, $monthNumber));
           }
     ,$monthsArray);

your output will look like this
[
     "January",
     "February",
     "March",
   ]

Method 3

You can do order by from your query like this

$date = DateList::where('id',$id->id)
->select(DB::raw("(DATE_FORMAT(date, '%M')) as newDate"))
->groupBy('newDate')
->orderBy('newDate', 'ASC')
->get();


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x