[FIXED] How to get last months total web page views with Laravel Eloquent

Issue

I have this post_views table

created_at         post_id       ip
==========         =======       =======
01-01-2020            3            127.0.0.1
01-01 2020            5            127.0.0.1
02-01 2020            5            127.0.0.1
03-01 2020            5            222.33.44.55

06-02 2020            3            222.33.44.55
06-02 2020            3            127.0.0.1
10-02 2020            5            33.44.55.66

02-03 2020            3            22.33.65.22
02-03 2020            3            22.33.65.22
02-03 2020            5            11.44.55.66

I need to sum every day visits to get 3 last months total web Page views using this table ,I mean this results:

January= 3 visits
February = 3 visits
March = 2 visits

Solution

Hope something like this will help you.

Inside your Post model:

public function getLastThreeMonthViewsByIP($ip)
{
    $data = [];

    // loop 3 months
    for ($i = -3; $i < 0; $i++)
    {
        $timestamp = strtotime("$i month");
        
        $monthNumber = date('n', $timestamp);

        // from this post
        $result = $this->views()
            // in this month
            ->whereMonth('created_at', $monthNumber)
            // from this ip
            ->where('ip', $ip)
            // group IP
            ->groupBy('ip')
            // count all
            ->selectRaw('count(*) AS total')
            // and return first 
            ->first();

        // if there are any results, add to data
        if ($result)
        {
            $monthName = date('F', $timestamp);

            $data[] = [
                'monthNumber' => $monthNumber,
                'monthName' => $monthName,
                'total' => $result->total,
            ];
        }
    }

    return $data;
}

Answered By - Jonathan Martins