CODEWITHSUNDEEP
phplaravellaravel-5
triole
triole

Reputation: 49

Unique results from the Laravel database 5.8

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->string('url_address', 160)->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();
    }

This function returns all the results from the database to me and works correctly. I need to redo this function to display unique results. Unique result = unique ip on the selected day.

How to do it?

Upvotes: 0

Views: 106

Answers (2)

mEscolar
mEscolar

Reputation: 29

What do you mean by unique? get the list of distinct IPs on that range? this would be like this:

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

EDIT: I saw that the thing you want is not the IPs values just the count for the statistics.

Transforming to MYSQL should be something like this (This maybe helps you to your code):

SELECT COUNT(id), ip
FROM statistics
WHERE user_id = {{id}} AND date BETWEEN {{dateFrom}} AND {{dateTo}}
GROUP BY ip;

So this will return the number of request grouped by IP. The SUM of all counts will give you the total of request, the count of IPs will give you the total of UNIQUE request.

To Laravel:

$requestPerIp = Statistics::whereBetween('date', [$dateFrom, $dateTo])
         ->where('user_id', $id)
         ->select('ip', DB::raw('count(*) as total'))
         ->groupBy('ip')
         ->get();

For the total unique request just:

$numberOfUniqueRequest = count($requestPerIp);

Upvotes: 0

Alex Guerrero
Alex Guerrero

Reputation: 229

you need to add a dictinct ip

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

or you can group by ip

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

Upvotes: 1

Related Questions