How to build a worklog table by month?

How do you differentiate years here?

Are worklogs from January 2016 and worklogs from January 2017 both summed-up in the same Table-cell?

@xmarks The view should display only one year at a time, so for example the table above shows only 2017.

It's gonna be a bit hard for me, without making tests, to provide a working answer right away, but I'll try my best.

First of all, I think you should make the transformations in your View in this case. Some of the logic in the Controller and Model. And just query the data from the Database.

projects Table

| fields |
| ------ |
| id |
| name |
| created_at |
| updated_at |

worklogs Table

| fields |
| ------ |
| id |
| project_id |
| log |
| created_at |
| updated_at |

Query

Based on the response here, I think this should work:

$year = $request->year;

$workLogs = Project::leftJoin('worklogs', 'projects.id', '=', 'worklongs.project_id')
    ->whereRaw("(YEAR(worklongs.created_at) =  ?)", [$year])
    ->select(
        'projects.id as id',
        'projects.name as name',
        
        DB::raw('MONTHNAME(worklogs.created_at) as month'),
        DB::raw('COUNT(DISTINCT worklogs.id) as month_total')
    )->orderBy('worklogs.id', 'month')
    ->orderBy(DB::raw('FIELD(MONTH,"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")'))
    ->groupBy('worklogs.id', 'month')->get();

This query will need to be tweaked a little so it shows something when the month_count is 0. This should get data like below:

| id | name | month | month_total |
| ------ | ------ | ------ | ------ |
| 1 | project_1 | January | 5 |
| 1 | project_1 | February | 10 |
| 2 | project_2 | January | 15 |
| 3 | project_3 | December | 4 |

Next, Group them by ID to separate Projects

$projects = $workLogs->groupBy('id');

This will give a collection, groupedBy Projects.

View

<table class="table table-striped table-responsive">
    <thead>
        <tr>
            <th>Project</th>
            <th>January</th>
            <th>February</th>
            <th>March</th>
            <th>April</th>
            <th>May</th>
            <th>June</th>
            <th>July</th>
            <th>August</th>
            <th>September</th>
            <th>October</th>
            <th>November</th>
            <th>December</th>
            <th>Total</th>
        </tr>
    </thead>
    
    <tbody>
    <!-- Treat each $projects Collection as 1-Project -->
        @foreach($projects as $project)
            <tr>
                <th scope="row">{{ $project->first()->name }}</th>
                <!-- Treat the data inside each project as 1-separate Worklog -->
                <!-- Assuming the Query is fixed above - Each month should be iterated -->
                <?php $totalCount = 0; ?>
                @foreach($project->all() as $worklog)
                    <td>{{ $worklog->month_total }}</td>
                    <?php $totalCount += $worklog->month_total; ?>
                @endforeach
                <td>{{ $totalCount }}</td>
            </tr>
        @endforeach
    </tbody>
</table>

Edit: Can't find anything to set a default month_total Count to be 0, if there are no worklogs at all for a given month.

Only solution would be to create a separate Table months, which would store all 12-month names. Then add a join to modify the query. Or join a subQuery as suggested Here

@xmarks That helped me a lot. Thank you very much.