How to filter by month in sql

user_10639425 0
Viewed 117

How to optimize my code using sql queries 

$collection->filter(function ($item) use ($i) {
  return $item->created_at->month == $i->month;
})->count();

I want instead of filter function on a collection to filter in sql so it can be quicker

here is the function : 

$data = [];
    switch ($range) {
        //monthly
        case self::$timeRanges[0]:
            for ($i = Carbon::now()->copy()->subYear()->endOfMonth(); $i <= Carbon::now()->subMonth()->endOfMonth(); $i->addMonths(1)) {
                $data[$i->month] = $collection->filter(function ($item) use ($i) {
                    return $item->created_at->month == $i->month;
                })->count();
            }
            break;
        //weekly
        case self::$timeRanges[1]:
            $collection = $collection->where('created_at', '>=', Carbon::now()->subWeek()->endOfWeek()->subWeeks(5))->where('created_at', '<=', Carbon::now()->subWeek()->endOfWeek());
            for ($i = Carbon::now()->copy()->subWeek()->endOfWeek()->subWeeks(5); $i <= Carbon::now()->copy()->subWeek()->endOfWeek(); $i->addWeeks(1)) {
                $data[$i->weekOfMonth] = $collection->filter(function ($item) use ($i) {
                    return $item->created_at->weekOfYear == $i->weekOfYear;
                })->count();
            }
            break;
    }
    return ($data);

thanks for helping, and have a nice day!

laravel filter mysql php
2 Answers

Just to extend of @Elie's answer, you don't even need to use a raw query. Laravel beautifully caters for date conditions and it is very well documented on the Queries page.

$desiredMonth = 12;

Model::whereMonth('created_at', $desiredMonth)->get();

However, this does not quite answer the question at hand. What we need to do is retrieve all relevant results and then filter by month on the retrieved results. I believe it will be much more efficient and faster to do this by retrieving all results from SQL and then filtering on them as you are, but with less iterative code:

$collection = Model::whereYear(2018)->get();

$months = $collection->groupBy(function ($item, $key) {
    return $item->created_at->month;
});

$months->toArray();

[
    '1' => [[...],[...],[...]],
    '2' => [[...],[...],[...]],
    '3' => [[...],[...],[...]],
    '4' => [[...],[...],[...]],
    '5' => [[...],[...],[...]],
    '6' => [[...],[...],[...]],
    '7' => [[...],[...],[...]],
    '8' => [[...],[...],[...]],
    '9' => [[...],[...],[...]],
    '10' => [[...],[...],[...]],
    '11' => [[...],[...],[...]],
    '12' => [[...],[...],[...]],
]

Additionally if you insist on using SQL to do the filtering, you could do a groupBy:

Model::groupBy(\DB::raw('MONTH(created_at) as month'))->get();

You'll need to do your own testing as to which is faster or at least most efficient. Also some databases do not allow multiple groupings (which we don't have here, but you might want to add) without modifying their config so my personal first prize would be the original approach unless you're working with an enormous data set.

user_26341900

Try this : 

Model::where(\DB::raw('MONTH(created_at)') , $month )->get();
user_45285590
Related