PHP code example of ptx / clickhouse-builder

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

ptx / clickhouse-builder example snippets

$server = new Tinderbox\Clickhouse\Server('127.0.0.1', '8123', 'default', 'user', 'pass');
$serverProvider = (new Tinderbox\Clickhouse\ServerProvider())->addServer($server);

$client = new Tinderbox\Clickhouse\Client($serverProvider);
$builder = new Builder($client);

$builder->select('column', 'column2', 'column3 as alias');
$builder->select(['column', 'column2', 'column3 as alias']);
$builder->select(['column', 'column2', 'column3' => 'alias']);

$builder->select(function ($column) {
    $column->name('time')->sumIf('time', '>', 10);
});

$builder->select(function ($column) {
    $column->as('alias') //or ->name('alias') in this case
    ->query()
    ->select('column')
    ->from('table');
});

$1 = $builder->select(function ($column) {
         $column->as('alias') //or ->name('alias') in this case
            ->query(function ($query) {
                $query->select('column')->from('table');
            })
});
$2 = $builder->select(function ($column) {
         $column->as('alias') //or ->name('alias') in this case
            ->query($builder->select('column')->from('table'));
});

$builder->select('column')->from('table', 'alias');

$builder->from(function ($from) {
    $from->query()->select('column')->from('table');
});

$builder->from(function ($from) {
    $from->query(function ($query) {
        $query->select('column')->from('table');
    });
});

$builder->from(function ($from) {
    $from->query($builder->select('column')->from('table'));
});

$builder->from($builder->select('column')->from('table'));

$builder->select('column')->from('table')->sample(0.1);

$builder->from('table')->join('another_table', 'any', 'left', ['column1', 'column2'], true, 'alias');

$builder->from('table')->join(function ($join) {
    $join->query()->select('column1', 'column2')->from('table2');
}, 'any', 'left', ['column1', 'column2']);

$builder->from('table')->join($builder->select('column1', 'column2')->from('table2'), 'any', 'left', ['column1', 'column2']);

$builder->from('table')->anyLeftJoin('table', ['column']);
$builder->from('table')->allLeftJoin('table', ['column']);
$builder->from('table')->allInnerJoin('table', ['column']);
$builder->from('table')->anyInnerJoin('table', ['column']);

$buulder->from('table')->leftJoin('table', 'any', ['column']);
$buulder->from('table')->innerJoin('table', 'all', ['column']);

$builder->from('test')->arrayJoin('someArr');
$builder->from('test')->leftArrayJoin('someArr');

$builder->addFile(new TempTable('numbersTable', 'numbers.tsv', ['number' => 'UInt64'], Format::TSV));

$builder->table(raw('numbers(0,1000)')->whereIn('number', 'numbersTable')->get();

$builder->from('table')->where('column', '=', 'value');
$builder->from('table')->where('column', 'value');

$builder->from('table')->where(function ($query) {
    $query->where('column1', 'value')->where('column2', 'value');
});

$builder->from('table')->where(function ($query) {
    $query->select('column')->from('table');
})

$builder->from('table')->where('column', 'IN', function ($query) {
    $query->select('column')->from('table');
});

$builder->where();
$builder->orWhere();

$builder->whereRaw();
$builer->orWhereRaw();

$builder->whereIn();
$builder->orWhereIn();

$builder->whereGlobalIn();
$builder->orWhereGlobalIn();

$builder->whereGlobalNotIn();
$builder->orWhereGlobalNotIn();

$builder->whereNotIn();
$builder->orWhereNotIn();

$builder->whereBetween();
$builder->orWhereBetween();

$builder->whereNotBetween();
$builder->orWhereNotBetween();

$builder->whereBetweenColumns();
$builder->orWhereBetweenColumns();

$builder->whereNotBetweenColumns();
$builder->orWhereNotBetweenColumns();

$builder->whereDict('dict', 'attribute', 'key', '=', 'value');

$builder->whereDict('dict', 'attribute', [new Identifier('column'), 'string value'], '=', 'value');

$builder->from('table')->select('column', raw('count()'))->groupBy('attribute');

$builder->from('table')->orderBy('column', 'asc', 'fr');

$builder->orderByAsc('column');
$builder->orderByDesc('column');

$builder->from('table')->limitBy(1, 'column1', 'column2');

$builder->from('table')->limit(10, 100);

$builder->from('table')->unionAll(function($query) {
    $query->select('column1')->from('table');
})->unionAll($builder->select('column2')->from('table'));

$builder->from('table')->asyncWithQuery(function($query) {
    $query->from('table');
});
$builder->from('table')->asyncWithQuery($builder->from('table'));
$builder->from('table')->asyncWithQuery()->from('table');

    'providers' => [
        ...
        \Ptx\ClickhouseBuilder\Integrations\Laravel\ClickhouseServiceProvider::class,
        ...
    ]

$app->register(\Ptx\ClickhouseBuilder\Integrations\Laravel\ClickhouseServiceProvider::class);

'connections' => [
    'clickhouse' => [
        'driver' => 'clickhouse',
        'host' => 'ch-00.domain.com',
        'port' => '',
        'database' => '',
        'username' => '',
        'password' => '',
        'options' => [
            'timeout' => 10,
            'protocol' => 'https'
        ]
    ]
]

DB::connection('clickhouse')->query();

'connections' => [
    'clickhouse' => [
        'driver' => 'clickhouse',
        'servers' => [
            [
                'host' => 'ch-00.domain.com',
                'port' => '',
                'database' => '',
                'username' => '',
                'password' => '',
                'options' => [
                    'timeout' => 10,
                    'protocol' => 'https'
                ]
            ],

            [
                'host' => 'ch-01.domain.com',
                'port' => '',
                'database' => '',
                'username' => '',
                'password' => '',
                'options' => [
                    'timeout' => 10,
                    'protocol' => 'https'
                ]
            ]
        ]
    ]
]

'connections' => [
    'clickhouse' => [
        'driver' => 'clickhouse',
        'clusters' => [
            'cluster-name' => [
                [
                    'host' => '',
                    'port' => '',
                    'database' => '',
                    'username' => '',
                    'password' => '',
                    'options' => [
                        'timeout' => 10,
                        'protocol' => 'https'
                    ]
                ],

                [
                    'host' => '',
                    'port' => '',
                    'database' => '',
                    'username' => '',
                    'password' => '',
                    'options' => [
                        'timeout' => 10,
                        'protocol' => 'https'
                    ]
                ]
            ]
        ]
    ]
]

'connections' => [
    'clickhouse' => [
        'driver' => 'clickhouse',
        'servers' => [
            [
                'host' => 'ch-00.domain.com',
                'port' => '',
                'database' => '',
                'username' => '',
                'password' => '',
                'options' => [
                    'timeout' => 10,
                    'protocol' => 'https',
                    'tags' => [
                        'tag'
                    ],
                ],
            ],
            [
                'host' => 'ch-01.domain.com',
                'port' => '',
                'database' => '',
                'username' => '',
                'password' => '',
                'options' => [
                    'timeout' => 10,
                    'protocol' => 'https'
                ],
            ],
        ],
    ],
]

DB::connection('clickhouse')->using('ch-01.domain.com')->select(...);

DB::connection('clickhouse')->usingRandomServer()->select(...);

DB::connection('clickhouse')->onCluster('test')->select(...);

DB::connection('clickhouse')->usingServerWithTag('tag')->select(...);

sql
SELECT sumIf(`time`, time > 10)