query visits for a specific date

[edrisranjbar]

📌 The issue

How can I query for page views in a specific timestamp? I've read the source code and there is a method called scopeFilter which does the job of filtering queries by period. what i'm trying to achieve is to query for last 7 days (separately) so that I can put the data related to views count and viewers count on chart. so I need every day's analytics for last 7 days.

    public function scopeFilter($query, $period = 'today')
    {
        if (! in_array($period, ['today', 'yesterday'])) {
            [$interval, $unit] = explode('_', $period);

            return $query->where('created_at', '>=', now()->sub($unit, $interval));
        }

        if ($period === 'yesterday') {
            return $query->whereDate('created_at', today()->subDay()->toDateString());
        }

        return $query->whereDate('created_at', today());
    }

🔍 Solution Attempt

I'm trying to do a for loop in order to get last 7 days data from PageView using a query.

for ($i=0; $i<7; $i++) {
    $currentWeekViews[] = PageView::query()->scopes(['filter' => ["day_".$i]])->count();
    $currentWeekViewers[] = PageView::query()->scopes(['filter' => ["day_".$i]])
        ->groupBy('session')
        ->pluck('session')->count();
}

Request

Could you please provide guidance on how to correctly query for page views for each day over the last 7 days using the scopeFilter method or any other recommended approach?

Thank you!

[andreaselia]

You could probably do the "last 7 days" filter and then group by date instead of having to loop over the query times and it would be more efficient that way. What do you think?

[edrisranjbar]

yeah exactly that's what I'm looking for, the current code is not efficient at all. so I think it should be like:

$currentWeekViews = PageView::query()
    ->scopes(['filter' => ["1_week"]])
    ->selectRaw('DATE(created_at) as date, COUNT(*) as views_count')
    ->groupBy('date')
    ->pluck('views_count');

it is working fine but the issue is; if we just started analytics today and we do not have data for last days; we going to get nothing for previous days while we need a zero as a default value.

I'm thinking using a zero-fill method to push zeros till the length of array is 7. is that a good idea here? something like:

$currentWeekViews = PageView::query()->scopes(['filter' => ["1_week"]])
    ->selectRaw('DATE(created_at) as date, COUNT(*) as views_count')
    ->groupBy('date')
    ->pluck('views_count');
while (count($currentWeekViews) < 7) {
    $currentWeekViews[] = 0;
}

$currentWeekViewers = PageView::query()->scopes(['filter' => ["1_week"]])
    ->groupBy('session')
    ->selectRaw('DATE(created_at) as date, COUNT(*) as viewers_count')
    ->groupBy('date')
    ->pluck('viewers_count');
while (count($currentWeekViewers) < 7) {
    $currentWeekViewers[] = 0;
}

📌 The issue

the problem now is that the number that i get using these queries are different than what i get in analytics dashboard. basically the ->count() method and using a raw query COUNT(*) as viewers_count' return different values.

[andreaselia]

Going to close this issue as it's not a bug and more help with a query. I'm not entirely sure I follow what you're after 😅

@tomirons any suggestions here?

[edrisranjbar]

Lets put it in another way: when i'm using this syntax:

$currentWeekViews[] = PageView::query()->scopes(['filter' => ["day_" . $i]])->count();

in a loop for getting last 7 days (i from 0 to 7); i'm getting total count up to that day not total count for that specific day. for example i'm getting 300 views for yesterday and 315 for today while these are total count for up to these dates.

[andreaselia]

Would something like this work? Not tested, but along these lines...

public function scopeFilter($query, $period = 'today')
{
    if (! in_array($period, ['today', 'yesterday'])) {
        [$interval, $unit] = explode('_', $period);

        return $query->where('created_at', '>=', now()->sub($unit, $interval))
            ->orderBy('created_at')
            ->groupBy(DB::raw('DATE(created_at)'));
    }

    if ($period === 'yesterday') {
        return $query->whereDate('created_at', today()->subDay()->toDateString())
            ->orderBy('created_at')
            ->groupBy(DB::raw('DATE(created_at)'));
    }

    return $query->whereDate('created_at', today())
        ->orderBy('created_at')
        ->groupBy(DB::raw('DATE(created_at)'));
}