Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

princeoo7's avatar

Query filter some time returns null in laravel

I was going through the laravel from scratch episode for archives.

the problem i am facing is that some times for some archive dates, no data is fetch even if the record exist in db.

the query fired is as below as per the dd(DB::getQueryLog());

array:1 [▼
    0 => array:3 [▼
        "query" => "select count(*) as aggregate from `posts` where (`status` = ?) and month(`created_at`) = ? and year(`created_at`) = ?"
        "bindings" => array:3 [▼
        0 => 1
        1 => "12"
        2 => 2019
        ]
        "time" => 0.84
    ]
]

fetching post code :

$posts = Post::latest()->with('author')
            ->where(['status' => 1])
            ->filter(['month' => $request->month, 'year' => $request->year])
            ->paginate(env('RECORD_LIMIT'));

filter code from scope in Post model:

public function scopeFilter($query, $filter)
    {
        if ($month = $filter['month']) {
                $query->whereMonth('created_at', Carbon::parse($month)->month);
        }

        if ($year = $filter['year']) {
                $query->whereYear('created_at', Carbon::parse($year)->year);
         }
    }

where am i going wrong ?

below is the query fired for fetching three records:

array:3 [▼
    0 => array:3 [▼
        "query" => "select count(*) as aggregate from `posts` where (`status` = ?) and month(`created_at`) = ? and year(`created_at`) = ?"
        "bindings" => array:3 [▼
        0 => 1
        1 => "04"
        2 => 2019
        ]
        "time" => 0.68
    ]
    1 => array:3 [▼
        "query" => "select * from `posts` where (`status` = ?) and month(`created_at`) = ? and year(`created_at`) = ? order by `created_at` desc limit 20 offset 0"
        "bindings" => array:3 [▼
        0 => 1
        1 => "04"
        2 => 2019
        ]
        "time" => 0.75
    ]
    2 => array:3 [▼
        "query" => "select * from `users` where `users`.`id` in (13, 29, 64)"
        "bindings" => []
        "time" => 0.66
    ]
]

0 likes
9 replies
chatty's avatar

add return

public function scopeFilter($query, $filter)
    {
        if ($month = $filter['month']) {
                return $query->whereMonth('created_at', Carbon::parse($month)->month);
        }

        if ($year = $filter['year']) {
                return $query->whereYear('created_at', Carbon::parse($year)->year);
         }
    }
1 like
chatty's avatar

Also you still have to return $query in case month and year are missing

public function scopeFilter($query, $filter)
    {
        if ($month = $filter['month']) {
                return $query->whereMonth('created_at', Carbon::parse($month)->month);
        }

        if ($year = $filter['year']) {
                return $query->whereYear('created_at', Carbon::parse($year)->year);
         }
         return $query;
    }
princeoo7's avatar

@RAS1212 - Well that was embarrassing :/ such silly mistake from my side :(

one more thing if possible related to the same topic.

if i use:

->filter(request(['month', 'year']))

instead of

 ->filter(['month' => $request->month, 'year' => $request->year])

I end up with

Undefined index: month

reference video link :

https://youtu.be/n3mKQ0o1Xr4

chatty's avatar

you know what? I would do something like this if all you care is month and year in the filter method

public function scopeFilter($query)
    {
        if ($month = request()->month) {
                return $query->whereMonth('created_at', Carbon::parse($month)->month);
        }

        if ($year = request()->year) {
                return $query->whereYear('created_at', Carbon::parse($year)->year);
         }
         return $query;
    }

so much cleaner

$posts = Post::latest()->with('author')
            ->where(['status' => 1])
            ->filter()
            ->paginate(env('RECORD_LIMIT'));
1 like
chatty's avatar

people make mistakes @princeoo7 please mark it solved if you already got it sorted out

princeoo7's avatar

@RAS1212 - this works but one tiny problem now.

if i have post/may/2018 as the url i expect post from may 2018, but now i am getting all record which have month may and the year condition is skipped i suppose :/

princeoo7's avatar
princeoo7
OP
Best Answer
Level 3

the final solution was the below code:

public function scopeFilter($query)
    {
        if ($month = request('month')) {
                $query->whereMonth('created_at', Carbon::parse($month)->month);
        }

        if ($year = request('year')) {
                $query->whereYear('created_at', $year);
        }
    }

i was parsing the $year also with carbon and that was the issue. we don't need to return any thing as it's part of the query i suppose.

1 like
princeoo7's avatar

@EDOC - it's the same query just as i tried to solve it with the provided solution, i ended up with another error for the same problem. but now it's solved and i have marked the question answered.

Please or to participate in this conversation.