如何使用 Laravel Eloquent 获取上个月的总网页浏览量

Question

我有这个 post_views 表

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

我需要使用此表对每天的访问量进行求和以获得过去 3 个月的总网页浏览量，我的意思是结果：

January= 3 visits

February = 3 visits

March = 2 visits

Answer 1

希望这样的事情能够帮助你。在你的Post模型内部：public function getLastThreeMonthViewsByIP($ip){&nbsp; &nbsp; $data = [];&nbsp; &nbsp; // loop 3 months&nbsp; &nbsp; for ($i = -3; $i < 0; $i++)&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; $timestamp = strtotime("$i month");&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; $monthNumber = date('n', $timestamp);&nbsp; &nbsp; &nbsp; &nbsp; // from this post&nbsp; &nbsp; &nbsp; &nbsp; $result = $this->views()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // in this month&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->whereMonth('created_at', $monthNumber)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // from this ip&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->where('ip', $ip)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // group IP&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->groupBy('ip')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // count all&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->selectRaw('count(*) AS total')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // and return first&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->first();&nbsp; &nbsp; &nbsp; &nbsp; // if there are any results, add to data&nbsp; &nbsp; &nbsp; &nbsp; if ($result)&nbsp; &nbsp; &nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $monthName = date('F', $timestamp);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $data[] = [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'monthNumber' => $monthNumber,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'monthName' => $monthName,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'total' => $result->total,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ];&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; return $data;}

Answer 2

您可以运行下一个 SQL 查询来生成此信息：SELECT month, count(*) AS totalFROM (&nbsp; &nbsp; SELECT DATE_FORMAT(created_at, '%Y-%m') AS month&nbsp; &nbsp; FROM post_views&nbsp; &nbsp; GROUP BY month, ip) AS calculatedGROUP BY month;结果：2020-01, 32020-02, 32020-03, 2要使其与 Laravel 一起使用：$result = DB::raw("&nbsp; &nbsp; SELECT month, count(*) AS total&nbsp; &nbsp; FROM (&nbsp; &nbsp; &nbsp; &nbsp; SELECT DATE_FORMAT(created_at, '%Y-%m') AS month&nbsp; &nbsp; &nbsp; &nbsp; FROM `post_views`&nbsp; &nbsp; &nbsp; &nbsp; WHERE `created_at` >= DATE_FORMAT(now() - interval ? month, '%Y-%m-01')&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; GROUP BY DATE_FORMAT(`created_at`, '%Y-%m'), ip&nbsp; &nbsp; ) AS calculated&nbsp; &nbsp; GROUP BY `month`", [3]); // last 3 monthsdd($result); // to see results希望有帮助！