Laravel Eloquent withMedian, withPercentile, median and percentile aggregate functions for PostgeSQL

The package provides several aggregate functions that work in the same way as withAvg(), withMax()

Requirements

• PHP 7.4 - 8.3
• Laravel 8.x - 11.x

Supports:

• PostgreSQL

Installation

You can install the package via composer:

composer require abordage/eloquent-percentile

Usage

Aggregating Related Models

The method withMedian() will place a {relation}_median_{column} attribute on your resulting models:

use App\Models\Post;
 
$posts = Post::withMedian('comments', 'votes')->get();
 
foreach ($posts as $post) {
    echo $post->comments_median_votes;
}

The method withPercentile() will place a {relation}_percentile{percentile*100}_{column} attribute on your resulting models:

use App\Models\Post;
 
$posts = Post::withPercentile('comments', 'votes', 0.85)->get();
 
foreach ($posts as $post) {
    echo $post->comments_percentile85_votes;
}

Retrieving Aggregates

When interacting with Eloquent models, you may also use the percentile and median aggregate methods. As you might expect, these methods return a scalar value instead of an Eloquent model instance:

$median = Comment::where('active', 1)->median('votes');
 
$percentile95 = Comment::where('active', 1)->percentile('votes', 0.95);

Automatic PHPDocs for models

If you are using the ide-helper you can describe the attributes with the Model Hooks. For example:

<?php

namespace App\Support\IdeHelper;

use App\Models\Post;
use Barryvdh\LaravelIdeHelper\Console\ModelsCommand;
use Barryvdh\LaravelIdeHelper\Contracts\ModelHookInterface;
use Illuminate\Database\Eloquent\Model;

class PostHook implements ModelHookInterface
{
    public function run(ModelsCommand $command, Model $model): void
    {
        if (!$model instanceof Post) {
            return;
        }

        $command->setProperty('comments_median_votes', 'float|null', true, false);
        $command->setProperty('comments_percentile80_votes', 'float|null', true, false);
        $command->setProperty('comments_percentile95_votes', 'float|null', true, false);
    }
}

Testing

Before running the tests, rename the phpunit.xml.dist to phpunit.xml and specify your database connection settings:

<php>
    <env name="DB_CONNECTION_POSTGRES" value="pgsql"/>
    <env name="DB_HOST_POSTGRES" value="postgres"/>
    <env name="DB_PORT_POSTGRES" value="5432"/>
    <env name="DB_DATABASE_POSTGRES" value="eloquent_percentile_test"/>
    <env name="DB_USERNAME_POSTGRES" value="default"/>
    <env name="DB_PASSWORD_POSTGRES" value="secret"/>
</php>

Next run:

composer test:all

or

composer test:phpunit
composer test:phpstan
composer test:phpcsf

Changelog

Please see CHANGELOG for more information on what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Security

Please review our security policy on how to report security vulnerabilities.

Feedback

Find a bug or have a feature request? Open an issue, or better yet, submit a pull request - contribution welcome!

Credits

• Pavel Bychko
• All Contributors

Thanks to

The original idea comes from the tailslide-php, so many thanks to its author!

License

The MIT License (MIT). Please see License File for more information.