PHPackages monikon22/clickhouse-builder - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. monikon22/clickhouse-builder ActiveLibrary[Database & ORM](/categories/database) monikon22/clickhouse-builder ============================ Clickhouse sql query builder 1161PHP Since Oct 28Pushed 6mo agoCompare [ Source](https://github.com/monikon22/ClickhouseBuilder)[ Packagist](https://packagist.org/packages/monikon22/clickhouse-builder)[ RSS](/packages/monikon22-clickhouse-builder/feed)WikiDiscussions master Synced 1mo ago READMEChangelogDependenciesVersions (1)Used By (0) Clickhouse Query Builder ======================== [](#clickhouse-query-builder) [![Build Status](https://camo.githubusercontent.com/5aaf9f6d06ae733260648d8bcc660706e5d4ff110f6e2919892e0dfdc5366fb1/68747470733a2f2f7472617669732d63692e6f72672f7468652d74696e646572626f782f436c69636b686f7573654275696c6465722e7376673f6272616e63683d6d6173746572)](https://travis-ci.org/the-tinderbox/ClickhouseBuilder) [![Coverage Status](https://camo.githubusercontent.com/fd6a2b1a782a1158d764f3caa70e67d7459a81394c03729b212da4afe56d2c52/68747470733a2f2f636f766572616c6c732e696f2f7265706f732f6769746875622f7468652d74696e646572626f782f436c69636b686f7573654275696c6465722f62616467652e7376673f6272616e63683d6d6173746572)](https://coveralls.io/github/the-tinderbox/ClickhouseBuilder?branch=master) Requirements ============ [](#requirements) `php 7.1+` Install ======= [](#install) Via composer ``` composer require the-tinderbox/clickhouse-builder ``` Usage ===== [](#usage) For working query builder we must previously instantiate and pass in constructor `the-tinderbox/clickhouse-php-client`. ``` $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); ``` After that we can build and perform sql queries. ### Select columns [](#select-columns) ``` $builder->select('column', 'column2', 'column3 as alias'); $builder->select(['column', 'column2', 'column3 as alias']); $builder->select(['column', 'column2', 'column3' => 'alias']); ``` All this calls will be transformed into next sql: ``` SELECT `column`, `column2`, `column3` AS `alias` ``` Also, as a column we can pass closure. In this case in closure will be passed instance of Column class, inside which we can setup column how we want. This can be useful for difficult expressions with many functions, subqueries and etc. ``` $builder->select(function ($column) { $column->name('time')->sumIf('time', '>', 10); }); ``` Will be compiled in: ``` SELECT sumIf(`time`, time > 10) ``` ``` $builder->select(function ($column) { $column->as('alias') //or ->name('alias') in this case ->query() ->select('column') ->from('table'); }); ``` Will be compiled in: ``` SELECT (SELECT `column` FROM `table) as `alias` ``` Same behavior can be also achieved by any of the following approaches: ``` $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')); }); ``` **Notice! Functions on columns is not stable and under development.** ### From [](#from) ``` $builder->select('column')->from('table', 'alias'); ``` Produce the following query: ``` SELECT `column` FROM `table` as `alias` ``` Also can be passed closure or builder as argument for performing sub query. ``` $builder->from(function ($from) { $from->query()->select('column')->from('table'); }); ``` ``` SELECT * FROM (SELECT `column` FROM `table`) ``` or ``` $builder->from(function ($from) { $from->query(function ($query) { $query->select('column')->from('table'); }); }); ``` or ``` $builder->from(function ($from) { $from->query($builder->select('column')->from('table')); }); ``` or ``` $builder->from($builder->select('column')->from('table')); ``` It is all variants of the same sql query which was listed above. ### Sample coefficient [](#sample-coefficient) ``` $builder->select('column')->from('table')->sample(0.1); ``` ``` SELECT `column` FROM `table` SAMPLE 0.1 ``` I think there no need for additional words) ### Joins [](#joins) ``` $builder->from('table')->join('another_table', 'any', 'left', ['column1', 'column2'], true, 'alias'); ``` ``` SELECT * FROM `table` GLOBAL ANY LEFT JOIN `another_table` AS `alias` USING `column1`, `column2` ``` For performing subquery as first argument you can pass closure or builder. ``` $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']); ``` ``` SELECT * FROM `table` ANY LEFT JOIN (SELECT `column1`, `column2` FROM `table2`) USING `column1`, `column2` ``` Also there are many helper functions with hardcoded arguments, like strict or type and they combinations. ``` $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']); ``` You can use array join as well. ``` $builder->from('test')->arrayJoin('someArr'); $builder->from('test')->leftArrayJoin('someArr'); ``` ``` SELECT * FROM `test` ARRAY JOIN `someArr` SELECT * FROM `test` LEFT ARRAY JOIN `someArr` ``` ### Temporary tables usage [](#temporary-tables-usage) There are some cases when you need to filter f.e. users by their ids, but amount of ids is huge. You can store users ids in local file, upload it to server and use it as temporary table. Read more about local files [here](https://github.com/the-tinderbox/ClickhouseClient) in section `Using local files`. #### Select [](#select) You should pass instance of `TempTable` with declared table structure to attach file to query. ``` $builder->addFile(new TempTable('numbersTable', 'numbers.tsv', ['number' => 'UInt64'], Format::TSV)); $builder->table(raw('numbers(0,1000)')->whereIn('number', 'numbersTable')->get(); ``` **If you want tables to be detected automatically, call `addFile` method before calling `whereIn`.** You can use local files in `whereIn`, `prewhereIn`, `havingIn` and `join` statements of query builder. #### Insert [](#insert) If you want to insert file or files into Clickhouse, you could use `insertFile` and `insertFiles` methods. ``` $builder->table('test')->insertFile(['date', 'userId'], 'test.tsv', Format::TSV); ``` Or you can pass batch of files into `insertFiles` method and all of them will be inserted asynchronously. ``` $builder->table('test')-insertFiles(['date', 'userId'], [ 'test-1.tsv', 'test-2.tsv', 'test-3.tsv', 'test-4.tsv', 'test-5.tsv', 'test-6.tsv', 'test-7.tsv', ], Format::TSV) ``` Also, you can use helper and insert data to temporary table with engine Memory. ``` $builder->table('test')->values('test.tsv')->format(Format::TSV); into_memory_table($builder, [ 'date' => 'Date', 'userId' => 'UInt64' ]); ``` Helper will drop temporary table with name `test` and creates table with declared structure, engine Memory and inserts data from `test.tsv` file into just created table. It's helpful if you want to fill some table with data to execute query and then drop it. ### Prewhere, where, having [](#prewhere-where-having) All example will be about where, but same behavior also is for prewhere and having. ``` $builder->from('table')->where('column', '=', 'value'); $builder->from('table')->where('column', 'value'); ``` ``` SELECT * FROM `table` WHERE `column` = 'value' ``` All string values will be wrapped with single quotes. If operator is not provided `=` will be used. If operator is not provided and value is an array, then `IN` will be used. ``` $builder->from('table')->where(function ($query) { $query->where('column1', 'value')->where('column2', 'value'); }); ``` ``` SELECT * FROM `table` WHERE (`column1` = 'value' AND `column2` = 'value') ``` If in the first argument was passed closure, then all wheres statements from inside will be wrapped with parenthesis. But if on that builder (inside closure) will be specified `from` then it will be transformed into subquery. ``` $builder->from('table')->where(function ($query) { $query->select('column')->from('table'); }) ``` ``` SELECT * FROM `table` WHERE (SELECT `column` FROM `table`) ``` Almost same is for value parameter, except wrapping into parenthesis. Any closure or builder instance passed as value will be converted into subquery. ``` $builder->from('table')->where('column', 'IN', function ($query) { $query->select('column')->from('table'); }); ``` ``` SELECT * FROM `table` WHERE `column` IN (SELECT `column` FROM `table`) ``` Also you can pass internal representation of this statement and it will be used. I will no talk about this with deeper explanation because its not preferable way to use this. Like joins there are many helpers with hardcoded parameters. ``` $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(); ``` Also there is method to make where by dictionary: ``` $builder->whereDict('dict', 'attribute', 'key', '=', 'value'); ``` ``` SELECT dictGetString('dict', 'attribute', 'key') as `attribute` WHERE `attribute` = 'value' ``` If you want to use complex key, you may pass an array as `$key`, then array will be converted to tuple. By default all strings will be escaped by single quotes, but you may pass an `Identifier` instance to pass for example column name: ``` $builder->whereDict('dict', 'attribute', [new Identifier('column'), 'string value'], '=', 'value'); ``` Will produce: ``` SELECT dictGetString('dict', 'attribute', tuple(`column`, 'string value')) as `attribute` WHERE `attribute` = 'value' ``` ### Group By [](#group-by) Works like select. ``` $builder->from('table')->select('column', raw('count()'))->groupBy('attribute'); ``` Final query will be like: ``` SELECT `column`, count() FROM `table` GROUP BY `attribute` ``` ### Order By [](#order-by) ``` $builder->from('table')->orderBy('column', 'asc', 'fr'); ``` *In the example above, third argument is optional* ``` SELECT * FROM `table` ORDER BY `column` ASC COLLATE 'fr' ``` Aliases: ``` $builder->orderByAsc('column'); $builder->orderByDesc('column'); ``` For column there are same behaviour like in select method. ### Limit [](#limit) There are two types of limit. Limit and limit n by. Limit n by: ``` $builder->from('table')->limitBy(1, 'column1', 'column2'); ``` Will produce: ``` SELECT * FROM `table` LIMIT 1 BY `column1`, `column2` ``` Simple limit: ``` $builder->from('table')->limit(10, 100); ``` Will produce: ``` SELECT * FROM `table` LIMIT 100, 10 ``` ### Union ALL [](#union-all) In `unionAll` method can be passed closure or builder instance. In case of closure inside will be passed builder instance. ``` $builder->from('table')->unionAll(function($query) { $query->select('column1')->from('table'); })->unionAll($builder->select('column2')->from('table')); ``` ``` SELECT * FROM `table` UNION ALL SELECT `column1` FROM `table` UNION ALL SELECT `column2` FROM `table` ``` ### Performing request and getting result. [](#performing-request-and-getting-result) After building request you must call `get()` method for sending request to the server. Also there has opportunity to make asynchronous requests. Its works almost like `unionAll`. ``` $builder->from('table')->asyncWithQuery(function($query) { $query->from('table'); }); $builder->from('table')->asyncWithQuery($builder->from('table')); $builder->from('table')->asyncWithQuery()->from('table'); ``` This callings will produce the same behavior. Two queries which will be executed asynchronous. Now, if you call `get()` method, as result will be returned array, where numeric index correspond to the result of request with this number. ### Prepared Statements (Parametrized Queries) [](#prepared-statements-parametrized-queries) The query builder supports prepared statements for secure and efficient query execution with dynamic values. This protects against SQL injection and allows ClickHouse to optimize repeated queries. #### Basic Usage [](#basic-usage) The simplest approach is to pass PHP values directly with automatic type inference: ``` // Simple comparison with automatic type inference $builder->from('users') ->where('age', '>', 18) ->where('status', '=', 'active') ->get(); ``` #### Using Parameter Class [](#using-parameter-class) For explicit control over parameter names and types: ``` use Tinderbox\ClickhouseBuilder\Query\Parameter; $builder->from('users') ->where('id', '=', new Parameter('user_id', 42, 'UInt32')) ->bind('user_id', 42, 'UInt32') ->get(); ``` #### Automatic Type Inference [](#automatic-type-inference) The builder automatically detects ClickHouse types from PHP values: ``` $builder->where('count', '>', 100); // UInt16 $builder->where('price', '>=', 99.99); // Float64 $builder->where('name', '=', 'John'); // String $builder->where('active', '=', true); // UInt8 $builder->where('tags', 'IN', ['tag1', 'tag2']); // Array(String) ``` #### Setting Multiple Parameters [](#setting-multiple-parameters) ``` $builder->from('orders') ->where('total', '>=', 100) ->where('status', 'IN', ['pending', 'processing']) ->where('created_at', '>=', '2024-01-01') ->setParameters([ 'min_total' => 100, 'statuses' => ['pending', 'processing'], 'start_date' => '2024-01-01', ], [ 'min_total' => 'Float64', 'statuses' => 'Array(String)', 'start_date' => 'String', ]) ->get(); ``` #### Security Benefits [](#security-benefits) Prepared statements provide protection against SQL injection: ``` // ❌ UNSAFE - Do not do this: $userId = 123; // or from user input $builder->whereRaw("user_id = $userId"); // ✅ SAFE - Use prepared statements: $builder->where('user_id', '=', $userId); // or $builder->where('user_id', '=', new Parameter('uid', $userId, 'UInt32')); ``` For comprehensive documentation about prepared statements, see [PREPARED\_STATEMENTS.md](./PREPARED_STATEMENTS.md). ### Integrations [](#integrations) #### Laravel or Lumen < 5.5 [](#laravel-or-lumen--55) You can use this builder in Laravel/Lumen applications. **Laravel** In `config/app.php` add: ``` 'providers' => [ ... \Tinderbox\ClickhouseBuilder\Integrations\Laravel\ClickhouseServiceProvider::class, ... ] ``` **Lumen** In `bootstrap/app.php` add: ``` $app->register(\Tinderbox\ClickhouseBuilder\Integrations\Laravel\ClickhouseServiceProvider::class); ``` Connection configures via `config/database.php`. Example with alone server: ``` 'connections' => [ 'clickhouse' => [ 'driver' => 'clickhouse', 'host' => 'ch-00.domain.com', 'port' => '', 'database' => '', 'username' => '', 'password' => '', 'options' => [ 'timeout' => 10, 'protocol' => 'https' ] ] ] ``` Get a new builder: ``` DB::connection('clickhouse')->query(); ``` or ``` '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' ] ] ] ] ] ``` Example with cluster: ``` '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' ] ] ] ] ] ] ``` Example with server with tag: ``` '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' ], ], ], ], ] ``` Choose server without cluster: ``` DB::connection('clickhouse')->using('ch-01.domain.com')->select(...); ``` Or execute each new query on random server: ``` DB::connection('clickhouse')->usingRandomServer()->select(...); ``` Choose cluster: ``` DB::connection('clickhouse')->onCluster('test')->select(...); ``` Use server with tag: ``` DB::connection('clickhouse')->usingServerWithTag('tag')->select(...); ``` You can use both `servers` and `clusters` config directives and choose on which server query should be executed via `onCluster` and `using` methods. If you want to choose server outside cluster, you should just call `onCluster(null)` and then call `using` method. You can call `usingRandomServer` and `using` methods with selected cluster or not. ### Health Score 22 — LowBetter than 22% of packages Maintenance49 Moderate activity, may be stable Popularity10 Limited adoption so far Community17 Small or concentrated contributor base Maturity13 Early-stage or recently created project Bus Factor2 2 contributors hold 50%+ of commits How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** — Project age, version count, PHP version support, and release stability. ### Community Maintainers ![](https://www.gravatar.com/avatar/9856a8b08d5173746e77620d151c23f29c093889d8dec729d80b2cf778c1cff2?d=identicon)[monikon22](/maintainers/monikon22) --- Top Contributors [![FacedSID](https://avatars.githubusercontent.com/u/11896844?v=4)](https://github.com/FacedSID "FacedSID (24 commits)")[![evsign](https://avatars.githubusercontent.com/u/7965780?v=4)](https://github.com/evsign "evsign (19 commits)")[![monikon22](https://avatars.githubusercontent.com/u/55911809?v=4)](https://github.com/monikon22 "monikon22 (18 commits)")[![vitalcrazz](https://avatars.githubusercontent.com/u/6044393?v=4)](https://github.com/vitalcrazz "vitalcrazz (4 commits)")[![rez1dent3](https://avatars.githubusercontent.com/u/5111255?v=4)](https://github.com/rez1dent3 "rez1dent3 (2 commits)")[![romanpravda](https://avatars.githubusercontent.com/u/59819062?v=4)](https://github.com/romanpravda "romanpravda (2 commits)")[![astro2049](https://avatars.githubusercontent.com/u/45759373?v=4)](https://github.com/astro2049 "astro2049 (2 commits)")[![pavemaksim](https://avatars.githubusercontent.com/u/29757628?v=4)](https://github.com/pavemaksim "pavemaksim (1 commits)")[![suvorovis](https://avatars.githubusercontent.com/u/34533360?v=4)](https://github.com/suvorovis "suvorovis (1 commits)")[![tachigami](https://avatars.githubusercontent.com/u/4858247?v=4)](https://github.com/tachigami "tachigami (1 commits)")[![tianhe1986](https://avatars.githubusercontent.com/u/19562319?v=4)](https://github.com/tianhe1986 "tianhe1986 (1 commits)")[![dikopylov](https://avatars.githubusercontent.com/u/38421064?v=4)](https://github.com/dikopylov "dikopylov (1 commits)")[![agolovenkin](https://avatars.githubusercontent.com/u/2268899?v=4)](https://github.com/agolovenkin "agolovenkin (1 commits)")[![gitlog](https://avatars.githubusercontent.com/u/1937028?v=4)](https://github.com/gitlog "gitlog (1 commits)")[![leo108](https://avatars.githubusercontent.com/u/1551716?v=4)](https://github.com/leo108 "leo108 (1 commits)") ### Embed Badge ![Health badge](/badges/monikon22-clickhouse-builder/health.svg) ``` [![Health](https://phpackages.com/badges/monikon22-clickhouse-builder/health.svg)](https://phpackages.com/packages/monikon22-clickhouse-builder) ``` ### Alternatives [doctrine/orm Object-Relational-Mapper for PHP 10.2k285.3M6.2k](/packages/doctrine-orm)[jdorn/sql-formatter a PHP SQL highlighting library 3.9k115.1M102](/packages/jdorn-sql-formatter)[illuminate/database The Illuminate Database package. 2.8k52.4M9.3k](/packages/illuminate-database)[mongodb/mongodb MongoDB driver library 1.6k64.0M543](/packages/mongodb-mongodb)[ramsey/uuid-doctrine Use ramsey/uuid as a Doctrine field type. 90340.3M209](/packages/ramsey-uuid-doctrine)[reliese/laravel Reliese Components for Laravel Framework code generation. 1.7k3.4M16](/packages/reliese-laravel) PHPackages © 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)