Task : to get an answer to the question " Who published the largest number of ideas for the current year in each month ". The output should receive an array with 12 months, where each month contains one user with the number of publications. Something like this:

'0' => ["author_id" => 69, "count" => 2, "month" => 1], '1' => ["author_id" => 44, "count" => 33, "month" => 2], '2' => ["author_id" => 38, "count" => 10, "month" => 3],

or

 '0' => ["author_id" => 69, "count" => 2], '1' => ["author_id" => 44, "count" => 33], '2' => ["author_id" => 38, "count" => 10], ... '11' => ["author_id" => 4, "count" => 41],

Current solution :

 Idea::OnlyMyCompany() ->select(DB::raw('author_id'), DB::raw('count(author_id) as count, MONTH(created_at) month')) ->whereYear('created_at', '=', $year) ->groupBy('author_id', 'month') ->orderBy('month') ->orderBy('count', 'desc') ->get();

Current result :

 '0' => ["author_id" => 69, "count" => 2, "month" => 1], '1' => ["author_id" => 33, "count" => 1, "month" => 1], '2' => ["author_id" => 16, "count" => 1, "month" => 1], '3' => ["author_id" => 44, "count" => 33, "month" => 2], '4' => ["author_id" => 92, "count" => 16, "month" => 2] .....

As you can see, we get all users with their number of publications. And you need only the greatest.

Possible solutions:

  1. Filter the current result in php
  2. Correct SQL query

Please suggest a solution.

    1 answer 1

    If the WHERE clause defines a predicate for filtering rows, then the HAVING clause is applied after grouping. Try using having max.