If you use Laravel as a framework and you write a lot of tests you have most likely run into this situation.

The Issue

I recently created a test for one of my controllers and it was throwing an error because I was using DATE_FORMAT() which is not a valid function for SQLite. Most of us use SQLite for testing and MySQL for production. Below is the snippet of code I was using to create an array like this ['November => 2]. I need to group the data into months then get a count of the data for each month in order to display on a chart. The function below works great but fails when you create a test and are using SQLite.

$data = Session::where('user_id', $request->user()->id)
            ->currentYear()
            ->select(
                DB::raw("DATE_FORMAT(session_start_date, '%M') as months"),
                DB::raw("SUM(session_amount) as sum")
            )->groupBy('months')->pluck('sum', 'months');

The Solution

So what do you do when you run into this situation. Well, this is where Laravel Collections come in to save the day.

First, we want to create the collection that we want to work with.

$results = $request->user()->sessions()->currentYear()->get();

Then we want to group the collection by the month and then map the count, sort, and then make it an array.

$data = $results->groupBy(function($item){
            return $item['session_start_date']->monthName;
        })->map(function($item) {
            return $item->count();
        })->sort()->toArray();

The above result will also return ["November" => 2] and the test will pass now because we're not using invalid functions that SQLite doesn't recognize.

Hopefully, this helps someone else out that's running into this issue. If you know of a more efficient way of accomplishing this please let me know in the comments below. Enjoy!