How to retrieve weekly data from database using Laravel and MySQL

Other Questions MySQL
1

I’m working on a Laravel project where I need to fetch records from my database grouped by week instead of month.

Currently I have this working code that pulls monthly data using Carbon:

$orders = \DB::table('orders') 
        ->where('user_id', $user->id) 
        ->where(\DB::raw('month(date_created)'), Carbon::now()->month)
        ->get();
        $user->ordersTotal = $orders->count();

This works perfectly for monthly reports. However, I need to modify this to get weekly data instead. I attempted to change it like this:

   ->where(\DB::raw('week(date_created)'), Carbon::now()->week)

But Laravel throws an error saying Unknown getter ‘week’. How can I properly filter my database records to get data for the current week? What’s the correct way to handle weekly date filtering in Laravel with MySQL?

2

Raw SQL works but kills Laravel’s elegance. I’d go with Carbon’s diffInWeeks() plus a date filter instead. Try ->whereRaw('DATEDIFF(NOW(), date_created) < 7') for reliable last 7 days. WEEK() and YEARWEEK() are tricky - they use calendar weeks, but you probably want rolling 7-day windows. I had a project where marketing said ‘this week’s signups’ but meant ‘last 7 days.’ Calendar weeks made Monday reports look terrible. Watch out for timezone issues too if you’re multi-region. MySQL’s NOW() uses server timezone, Carbon uses app timezone. Creates nasty bugs that only show up in production.

3

Here’s a different take - skip wrestling with MySQL date functions and automate this data collection instead.

Right now, every report load hits your database with date calculations. With decent traffic, those queries pile up quick.

I built something similar for our analytics dashboard. Instead of real-time queries, I set up a Latenode automation that runs hourly. It pulls data using whatever date logic I need (weekly, monthly, custom periods), processes everything, and dumps clean results into a summary table.

Now my Laravel code just grabs pre-calculated numbers. No more Carbon date math, no MySQL YEARWEEK headaches, no performance hits.

The automation handles edge cases automatically - week boundaries, timezones, different week numbering. When someone asks for “trailing 28 days” or “this week vs same week last quarter”, I just tweak the workflow instead of rewriting queries.

You also get scheduling, error handling, and data validation built-in. Way more reliable than crossing your fingers that date queries work across different scenarios.

Your current solution’s fine for now, but automating these repetitive data tasks saves tons of debugging headaches down the road.

4

Use MySQL’s YEARWEEK function instead - ->where(\DB::raw('YEARWEEK(date_created)'), \DB::raw('YEARWEEK(NOW())')). It handles year boundaries way better than week() which gets wonky around January.

5

Carbon doesn’t have a week property - you need weekOfYear instead.

Here’s the fix:

$orders = \DB::table('orders') 
        ->where('user_id', $user->id) 
        ->where(\DB::raw('week(date_created)'), Carbon::now()->weekOfYear)
        ->get();
        $user->ordersTotal = $orders->count();

But honestly, manual date queries get messy quick. You start with weekly reports, then someone wants quarterly, then fiscal year stuff, then timezone headaches.

I automated this whole thing with Latenode. Built a workflow that runs on schedule, grabs data with whatever date logic I need, and feeds it straight to Laravel via API. No more fighting Carbon methods or MySQL date functions.

Best part? I can switch between weekly, monthly, or custom periods without touching Laravel code. Just tweak the workflow in Latenode’s visual editor.

Your approach works for now, but automating these data tasks saves major headaches down the road.

6

Both answers work, but here’s another approach.

If you’re pulling weekly data regularly, you’re probably building a dashboard or reporting system. This query will run often - maybe multiple times per page load.

Don’t hit your database every time someone wants weekly stats. I set up automated pipelines that pre-calculate these metrics. Every night, my Latenode workflow runs the heavy database queries, processes weekly/monthly/quarterly numbers, and dumps them into a summary table.

Now my Laravel app just reads pre-computed results instead of doing complex date math on live data. Page loads dropped from 2-3 seconds to under 200ms.

The workflow handles edge cases automatically - weeks spanning months, holiday periods, rolling 7-day windows vs calendar weeks.

When business asks for “last 4 weeks” or “same week last year” comparisons, I just add another step to existing automation instead of rewriting Laravel queries.

7

Had this exact problem last year building analytics for an e-commerce client. The YEARWEEK approach is solid, but MySQL’s week numbering can bite you - it starts weeks on Sunday or Monday depending on your mode setting. Learned this the hard way when client reports were off by a day. Add the mode parameter: YEARWEEK(date_created, 1) forces Monday as week start. Also throw in a year check if you’re dealing with historical data - week 1 of January can belong to the previous year in some counting systems. Performance tip: if you’re running this frequently, index your date_created column. Made a huge difference in query speed once we hit thousands of orders.

8

use whereBetween with carbon’s startOfWeek() and endOfWeek() - much cleaner than raw SQL. try ->whereBetween('date_created', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()]). way easier to read and debug.