Question 16978 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to get current month record in laravel?

Need to get record of current month but query return the wrong result.There is only one record in db.but i get wrong count for the current month

$data = UserData::select(
    DB::raw("count(phone) as total")
)
    ->whereMonth('creation_date', Carbon::now()->month)
    ->get();
return view('kpidata', compact('data'));

this is result of mysql query
enter image description here

and this is the result i get using laravel queryenter image description here

Tags:

4

Answers


  1. 0

    "whereMonth" compares only the month number not the year

    Option 1:

    UserData:select(DB::raw("count(phone) as total"))
    ->whereBetween('creation_date', 
        [
            Carbon::now()->startOfMonth(), 
            Carbon::now()->endOfMonth()
        ])
    ->get();

    Option 2:

    UserData:select(DB::raw("count(phone) as total"))
    ->whereYear('creation_date', Carbon::now()->year)
    ->whereMonth('creation_date', Carbon::now()->month)
    ->get();
    Login or Signup to reply.
  2. 0

    Here Item is a modal.

    $data = Item::select('*')
            ->whereMonth('created_at', Carbon::now()->month)
            ->get();
   
print_r($data);
    Login or Signup to reply.
  3. 0

    You actually need a full date by using the whereMonth and not just the integer equivalent of the month.

    $data = UserData::whereMonth('creation_date', Carbon::now())->get();

return view('kpidata', compact('data'));

    Also, if you just want to display the recordcount, you can just do:

    $data = UserData::whereMonth('creation_date', Carbon::now())->count();
    Login or Signup to reply.
  4. 0

    Avoid using whereMonth() and similar "helper" methods. They are a dreadful habit and lead to unnecessarily inefficient queries. This may not matter when testing on your local machine, but if you are fortunate enough to get your application into the "wild" and it is successful with 1,000s of concurrent users, it may be the difference between success and failure.

    Your code –

    $data = UserData::select(
    DB::raw("count(phone) as total")
)
    ->whereMonth('creation_date', Carbon::now()->month)
    ->get();

    will result in a query that looks something like –

    select count(phone) as total
from users
where month(creation_date) = :month

    Because of the month() function wrapped around creation_date the server is unable to use any available index on creation_date. It is also unnecessarily executing the function for each row. Read more about Non-SARGable Predicates.

    As already suggested by @Heroherm, you should use whereBetween() –

    $data = UserData::selectRaw('count(phone) as total')
    ->whereBetween('creation_date',
        [
            Carbon::now()->startOfMonth()->format('Y-m-d'),
            Carbon::now()->endOfMonth()->format('Y-m-d')
        ]
    )
    ->get();

    which will result in a query that looks something like –

    select count(phone) as total
from users
where creation_date between :start and :end

    If this does not work, as previously suggested, please use ->dd() in place of ->get() to see what is going on.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search