How to get data of this week?

@tykus Thank you so much!! But I've got just 5 days of week without Friday & Saturday

@Jean_ali so there are no records on either Saturday or Friday?

@tykus No I just got the 5 days without these two days at all

@tykus The response: https://ibb.co/b39w5Bs

@Jean_ali I assume you are limiting it with the whereBetween in your first post? And it's Thursday today and you want data from the future?

@Sinnbeck My query is wrong as I mention, but I need all days of the current week like today is Thursday I need from the beginning of this week to the end!

@Jean_ali so from today and 7 days back? Or always from Sunday and forward?

->whereBetween('created_at', [
                now()->subDays(7),
                now()
            ]) 

@Sinnbeck always from Sunday and forward of this date of week

@Jean_ali then you won't get Friday and Saturday as those are in the future (not until Saturday)

>whereBetween('created_at', [
                now()->startOfWeek(Carbon::SATURDAY),
                now()
            ]) 

@Sinnbeck I've got an idea: get all dates of the current week and then do a query with these dates!

@Jean_ali since it is a small range; you might find something like this easier:

$posts = Post::query()
    ->selectRaw("DAYNAME(created_at) as day, COUNT(id) as number")
    ->groupByRaw("DAYNAME(created_at)")
    ->whereBetween('created_at', [
        now()->startOfWeek(Carbon::SATURDAY),
        now()
    ]) 
    ->pluck('number', 'day');

collect(range(-1, 5))
  ->mapWithKeys(fn ($i) => [jddayofweek($i, 1) => 0])
  ->merge($posts)

@tykus I've got this error: Attempt to read property \"day\" on int

@Jean_ali on what line of code?

Mr @tykus What do you think If I get all dates of the current weekdays like

2022-02-26
2022-02-27
2022-02-28
2022-03-01
2022-03-02
2022-03-03
2022-03-04

Via carbon then do query for these dates?

@Jean_ali how do you think that query should look?

@tykus the code:

$posts = Post::query()
 ->selectRaw("DAYNAME(created_at) as day, COUNT(id) as number")
            ->groupByRaw("DATE(created_at)")
            ->pluck('number', 'day');

        collect(range(-1, 5))
            ->mapWithKeys(fn ($i) => [jddayofweek($i, 1) => 0])
            ->merge($posts);

I got this error:

    "message": "Attempt to read property \"day\" on int",
    "exception": "ErrorException",
    "file": "/Users/ali/Documents/Sites/z-8/vendor/laravel/framework/src/Illuminate/Http/Resources/DelegatesToResource.php",
    "line": 140,

@tykus Maybe using case when something like this:

            ->selectRaw("count(case when status = 'incompleted' then 1 end) as incompleted")
            ->selectRaw("count(case when status = 'completed' then 1 end) as completed")
            ->selectRaw("count(case when status = 'rejected' then 1 end) as rejected")
            ->selectRaw("count(case when status = 'canceled' then 1 end) as canceled");

@Jean_ali typo above fixed DAYNAME, not DATE

$posts = Post::query()
     ->selectRaw("DAYNAME(created_at) as day, COUNT(id) as number")
     ->groupByRaw("DAYNAME(created_at)")
     ->whereBetween('created_at', [now()->startOfWeek(Carbon::SATURDAY), now()])
     ->pluck('number', 'day');

@tykus the response is:

Illuminate\Support\Collection {#1660
  #items: array:3 [
    "Monday" => 2
    "Tuesday" => 1
    "Thursday" => 2
  ]
  #escapeWhenCastingToString: false
}

@Jean_ali okay and now the merge?

collect(range(-1, 5))
            ->mapWithKeys(fn ($i) => [jddayofweek($i, 1) => 0])
            ->merge($posts);

@tykus Yeah the response above with the merge one!

@Jean_ali how are you using the result; can you show the complete code?

EDIT: sorry just realised the structure is wrong:

collect(range(-1, 5))
    ->mapWithKeys(fn ($i) => [jddayofweek($i, 1) => 0])
    ->merge($posts)
    ->map(fn ($count, $day) => (object) ['day' => $day, 'count' => $count])
    ->all();

@tykus The query:

$posts = Post::query()
            ->selectRaw("DAYNAME(created_at) as day, COUNT(id) as number")
            ->groupByRaw("DAYNAME(created_at)")
            ->whereBetween('created_at', [now()->startOfWeek(Carbon::SATURDAY), now()])
            ->pluck('number', 'day');
            
        collect(range(-1, 5))
            ->mapWithKeys(fn ($i) => [jddayofweek($i, 1) => 0])
            ->merge($fingers);

        dd($posts);
return PostVisitResource::collection($posts);

the resource:

class PostVisitResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            "day" => $this->day,
            "number" => $this->number,
        ];
    }
}

@Jean_ali yeah okay - the Resource expects an object see edit above

@tykus Still the same response above! no change

@Jean_ali even with (object) casting?

@tykus Yeah!

@Jean_ali you are merging fingers??? And ultimately passing in the wrong Collection:

$postVisits = collect(range(-1, 5))
            ->mapWithKeys(fn ($i) => [jddayofweek($i, 1) => 0])
            ->merge($posts);
return PostVisitResource::collection($postVisits);

@jean_ali you can array access the Resource:

class PostVisitResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            "day" => $this['day'],
            "number" => $this['number'],
        ];
    }
}

@tykus Thank you so much, I got it, Pls update the answer to the latest one. THANKS