one23/laravel-clickhouse

Laravel Clickhouse Eloquent

dev-master 2024-06-28 12:30 UTC

Requires

• the-tinderbox/clickhouse-builder: 6.1.0

Requires (Dev)

• php: ^8.2|^8.3
• illuminate/contracts: ^v10|^v11
• illuminate/database: ^v10|^v11
• illuminate/pagination: ^v10|^v11
• illuminate/support: ^v10|^v11
• laravel/pint: ^v1.6
• phpstan/phpstan: ^1.4.7
• phpunit/phpunit: ^v10|^v11
• symfony/var-dumper: *

Suggests

• laravel/framework: ^v10|^v11

MIT

• laravel
• eloquent
• clickhouse

README

Install

composer require one23/laravel-clickhouse

Add provider to bootstrap/providers.php

return [
    //...
    One23\LaravelClickhouse\ClickhouseServiceProvider::class,
    //...
];

Add connection to config/database.php

return [
    'connections' => [
        //...
        'clickhouse' => [
            'driver' => 'clickhouse',
            'host' => env('CLICKHOUSE_HOST', 'localhost'),
            'port' => env('CLICKHOUSE_PORT', 8123),
            'database' => env('CLICKHOUSE_DATABASE'),
            'username' => env('CLICKHOUSE_USERNAME'),
            'password' => env('CLICKHOUSE_PASSWORD'),
            'options' => [
                'timeout' => 15,
                'protocol' => 'http',
            ],
        ],
        //...
    ]
];

Create model. Example:

use One23\LaravelClickhouse\Database\Eloquent\Model;

class StatisticDaily extends Model
{
    protected $table = 'mv_statistic_daily';

    protected $casts = [
        'date' => 'date',
        'link_id' => 'int',
        'cnt' => 'int',
        'uniq_ip' => 'int',
    ];

    protected $primaryKey = null;

    //
}

Use model. Example:

Clickhouse\StatisticDaily::query()
    ->where('date', '=', $dt)
    ->delete();

Clickhouse\StatisticDaily::query()
    ->where('link_id', '=', $OLink->getId())
    ->whereBetween('date', [
        $from->startOfDay()->toDateString(),
        $to->endOfDay()->toDateString(),
    ])
    ->groupBy('date')
    ->selectRaw(implode(', ', [
        '`date`',
        'SUM(`cnt`) AS `cnt`',
        'SUM(`cnt_uniq_ip`) AS `cnt_uniq_ip`',

        'SUM(`cnt_uniq`) AS `cnt_uniq`',
        'SUM(`cnt_mobile`) AS `cnt_mobile`',
        'AVG(`avg_latency`) AS `avg_latency`',
        'MIN(NULLIF(`min_latency`, 0)) AS `min_latency`',
        'MAX(`max_latency`) AS `max_latency`',
        'AVG(`quantile_latency`) AS `quantile_latency`',
    ]))
    ->get();

Clickhouse\StatisticDaily::query()
    ->whereIn('link_id', $linkIds)
    ->whereBetween('date', [
        $this->from
            ->toDateString(),
        $this->to
            ->toDateString(),
    ])
    ->selectRaw(implode(', ', [
        'SUM(`statistic_daily`.`cnt`) as `cnt`',
        'SUM(`statistic_daily`.`cnt_uniq_ip`) as `cnt_uniq_ip`',
    ]))
    ->first();

Todo

• Tests
• Deep where's
• Rewrite Grammar
• ...

Security

If you discover any security related issues, please email [e-mail] instead of using the issue tracker.

License

MIT