PHP Laravel如何为每个月分组日期并显示它们

Question

I have a table where I store music releases. Each row has a column named release_date and now I want my an overview for each month which makes it more clear and userfriendly. I was thinking about something like:

JANUARY
  4.1 - bla bla
  10.1 - bla bla
FEBRUARY
  8.2. - bla bla
  17.2. - bla bla

I am using Laravel, so is this possible somehow?

Right now I'm just displaying my data like this:

@if (count($releases) > 0) 
   @foreach ($releases as $release)
    <div>
        <p>
            <span>{{ \Carbon\Carbon::parse($release->release_date)->format('d. M')}}</span>&nbsp;
            <span>{{$release->artist}} - {{$release->album_title}} </span>
        </p>
    </div>
   @endforeach
@endif

and my controller looks like this:

public function index() {
    $releases = Release::orderBy('release_date', 'asc')->get();
    return view('pages.index')->with('releases', $releases);
}

Can someone help me out?

Answer 1

Sooo... I though of using DB::raw within the groupBy BUT then remembered we need to specify the column name in order for the query to understand with what it should group the results.

My idea is to specify a select to help us do this. And it can help sorting out another problem as well:

1. We need to target a column in order to group them;
2. It is better to just target the values you need to display instead of the entire table;

For such:

Release::select('album_title', 'release_date', DB::raw("MONTH('release_date') as month"))
->groupBy('month')
->orderBy('release_date', 'ASC')
->get();

Then we already create a extra column to use for grouping base solely on months :)

The problem it might trigger though, is joining all years into the same query. So let's say you have musics from 2016 and 2017. By using above solution you will end up with January songs from 2016 and 2017.

We can group by month, but we can also group them by year (both together), so we can, in reality, generate 2 column based to help us out separating the years as well, from the months:

Release::select('album_title', 'release_date', DB::raw("CONCAT_WS('-',MONTH(release_date),YEAR(release_date)) as combinedDates")
    ->groupBy('combinedDates')
    ->orderBy('release_date', 'ASC')
    ->get();

If you are curios on further check the CONCAT_WS left the link that can help clarify it!

Handling GroupBy functionality

GroupBy and Select are kinda like, not the best friends, sorta. It will force you to use all select columns in your GroupBy, which is something you do not wish.

Fortunately you can sort this within Laravel configurations. So:

Head to your config/database.php and locate mysql. Then add modes like below:

'modes' => [
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_AUTO_CREATE_USER',
                'NO_ENGINE_SUBSTITUTION'
            ],

By doing this we remove the restriction to forcefully use all columns in your select in groupBy and we avoid setting strict to FALSE