Generate statistics in Laravel

Question

I am beginner in Laravel. I use in my project Laravel 5.8.

I have schema:

Schema::create('statistics', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->bigInteger('company_id')->unsigned();
    $table->foreign('company_id')->references('id')->on('companies')->onDelete('cascade');
    $table->text('agent')->nullable();
    $table->date('date')->nullable();
    $table->ipAddress('ip');
    $table->bigInteger('user_id')->default(0);
    $table->bigInteger('quest_id')->default(0);
    $table->string('browser', 70)->nullable();
    $table->string('platform', 70)->nullable();
    $table->string('language', 12)->nullable();
    $table->engine = "InnoDB";
    $table->charset = 'utf8mb4';
    $table->collation = 'utf8mb4_unicode_ci';
});

I get statistics from this function:

public function generateStatistics(string $dateFrom, string $dateTo, int $id)
{
    return Statistics::whereBetween('date', [$dateFrom, $dateTo])->where('user_id', $id)->get();
}

$statisticsTotal = $this->frontendRepository->generateStatistics($dateFrom, $dateTo, $request->user()->id);
$statisticsResultsTotal = [];

$period = CarbonPeriod::create($dateFromInput, '1 day', $dateToInput);
foreach ($period as $dt) {
    $date = $dt->format("Y-m-d");
    $count = 0;
    $count2 = 0;
    foreach ($statisticsTotal as $stat){
        if ($stat->date == $date){
            $count = $count + 1;
        }
    }
    array_push($statisticsResultsTotal, "$date|$count");
};

This code generate statistics. It's work fine. Now I need generate unique visit statistics Unique = unique IP.

How can I make it?

user_6935763 · Answer

If you want to go on with the same logic you have, a simple groupBy will suffice.

You can generate another function for it like this:

public function generateUniqueStatistics(string $dateFrom, string $dateTo, int $id)
{
    return Statistics::whereBetween('date', [$dateFrom, $dateTo])->where('user_id', $id)->groupBy('ip')->get();
}

This way you can just change the calling to:

$statisticsTotal = $this->frontendRepository->generateUniqueStatistics($dateFrom, $dateTo, $request->user()->id);

and rest code will just work with it as its in the same format.

Update

I thought it was obvious but, just to be clear, in order for a groupBy to run without having the select columns in it, you will need to run your mySql in non strict mode.

Inside your config/database.php file, you will find an array connections, under which there is a key mysql, set its property strict as false.

If you do not want to do that and keep going with strict mysql, just give the columns name you are interested in to select, and do not include id, as id is not relevant here when selecting the statistics grouping by ip.

So for example, let's say you need company_id and agent, you can do like this:

return Statistics::whereBetween('date', [$dateFrom, $dateTo])->where('user_id', $id)->select('company_id','agent','ip')->groupBy('ip')->get();

I hope it helps

Generate statistics in Laravel

1 Answers

Update