### Install Clickhouse Builder via Composer

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Use Composer to install the Clickhouse Builder package. Ensure you are running PHP 7.1 or higher.

```bash
composer require the-tinderbox/clickhouse-builder
```

--------------------------------

### Asynchronous Query Execution in ClickHouse Builder

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Execute queries asynchronously using asyncWithQuery. This allows for parallel execution of multiple queries, with results aggregated upon calling get().

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

```php
$builder->from('table')->asyncWithQuery($builder->from('table'));
```

```php
$builder->from('table')->asyncWithQuery()->from('table');
```

--------------------------------

### Initialize Clickhouse Client and Builder

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Instantiate the Clickhouse client and query builder. This requires setting up a Server object and a ServerProvider.

```php
$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);
```

--------------------------------

### SQL for Sample Clause

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

This SQL query shows the output with a SAMPLE clause applied.

```sql
SELECT `column` FROM `table` SAMPLE 0.1
```

--------------------------------

### Initialize Clickhouse Builder

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Configure the server connection and initialize the builder for standalone usage.

```php
<?php

use Tinderbox\Clickhouse\Server;
use Tinderbox\Clickhouse\ServerProvider;
use Tinderbox\Clickhouse\Client;
use Tinderbox\ClickhouseBuilder\Query\Builder;

// Create server connection
$server = new Server('127.0.0.1', '8123', 'default', 'user', 'password');
$serverProvider = (new ServerProvider())->addServer($server);

// Initialize client and builder
$client = new Client($serverProvider);
$builder = new Builder($client);

// Execute a simple query
$result = $builder
    ->select('id', 'name', 'created_at')
    ->from('users')
    ->where('active', '=', 1)
    ->limit(100)
    ->get();

// Access results
foreach ($result->rows as $row) {
    echo $row['name'] . PHP_EOL;
}
```

--------------------------------

### Execute Queries with ClickHouseBuilder

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Methods for initializing the builder and selecting specific servers, clusters, or tagged nodes.

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

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

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

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

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

--------------------------------

### SQL Equivalent for Select Columns

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

This SQL query demonstrates the output of the various select column methods.

```sql
SELECT `column`, `column2`, `column3` AS `alias`
```

--------------------------------

### Apply Sample Clause

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Use the sample method to apply a sampling rate to the query. This is useful for performance optimization on large datasets.

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

--------------------------------

### Apply Sample Expression

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Use the SAMPLE clause for approximate query processing on large datasets.

```php
<?php

// Sample 10% of data
$builder->select('*')->from('events')->sample(0.1);
// SELECT * FROM `events` SAMPLE 0.1

// Sample with other clauses
$builder
    ->select('user_id', raw('count() as cnt'))
    ->from('page_views')
    ->sample(0.01)
    ->groupBy('user_id')
    ->orderByDesc('cnt')
    ->limit(100)
    ->get();
// SELECT `user_id`, count() as cnt FROM `page_views` SAMPLE 0.01 GROUP BY `user_id` ORDER BY `cnt` DESC LIMIT 100
```

--------------------------------

### Select with Subquery using Query Builder Instance

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Alternative methods to select a subquery as a column, either by passing a closure that configures a query or by passing a pre-built query builder instance.

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

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

--------------------------------

### Add Temporary Table for Selection

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Use `addFile` with `TempTable` to specify a local file as a temporary table for filtering large datasets in `whereIn` clauses. Ensure `addFile` is called before `whereIn` for automatic detection.

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

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

--------------------------------

### Execute Queries via Laravel Database Facade

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Perform standard queries, server-specific routing, pagination, and retrieve execution statistics using the DB facade.

```php
// Usage in Laravel
use Illuminate\Support\Facades\DB;

// Get builder instance
$builder = DB::connection('clickhouse')->query();

// Query using table method
$users = DB::connection('clickhouse')
    ->table('users')
    ->where('active', 1)
    ->orderByDesc('created_at')
    ->limit(100)
    ->get();

// Select specific server
DB::connection('clickhouse')->using('ch-02.domain.com')->table('events')->get();

// Use random server for load balancing
DB::connection('clickhouse')->usingRandomServer()->table('events')->get();

// Use server with specific tag
DB::connection('clickhouse')->usingServerWithTag('replica')->table('events')->get();

// Select cluster
DB::connection('clickhouse')->onCluster('analytics_cluster')->table('events')->get();

// Pagination support
$paginated = DB::connection('clickhouse')
    ->table('events')
    ->where('date', '>=', '2024-01-01')
    ->paginate(page: 1, perPage: 50);

// Get first result
$firstUser = DB::connection('clickhouse')
    ->table('users')
    ->where('email', 'user@example.com')
    ->first();

// Get query statistics
$result = DB::connection('clickhouse')->table('events')->get();
$stats = DB::connection('clickhouse')->getLastQueryStatistic();
echo "Rows read: " . $stats->getRowsRead();
echo "Execution time: " . $stats->getTime();
```

--------------------------------

### Helper Functions for JOIN Types

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Utilize helper methods for common join types like anyLeftJoin, allLeftJoin, allInnerJoin, and anyInnerJoin. These simplify the syntax for specific join configurations.

```php
$builder->from('table')->anyLeftJoin('table', ['column']);
```

```php
$builder->from('table')->allLeftJoin('table', ['column']);
```

```php
$builder->from('table')->allInnerJoin('table', ['column']);
```

```php
$builder->from('table')->anyInnerJoin('table', ['column']);
```

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

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

--------------------------------

### Perform Join Operations in ClickHouseBuilder

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Use various join methods to combine tables with support for USING, ON clauses, and subqueries. Global joins are available for distributed queries.

```php
<?php

// Basic ANY LEFT JOIN with USING
$builder->from('orders')->anyLeftJoin('customers', ['customer_id']);
// SELECT * FROM `orders` ANY LEFT JOIN `customers` USING `customer_id`

// ALL LEFT JOIN with multiple columns
$builder->from('orders')->allLeftJoin('order_items', ['order_id', 'product_id']);
// SELECT * FROM `orders` ALL LEFT JOIN `order_items` USING `order_id`, `product_id`

// ANY INNER JOIN
$builder->from('users')->anyInnerJoin('orders', ['user_id']);
// SELECT * FROM `users` ANY INNER JOIN `orders` USING `user_id`

// ALL INNER JOIN
$builder->from('products')->allInnerJoin('categories', ['category_id']);
// SELECT * FROM `products` ALL INNER JOIN `categories` USING `category_id`

// GLOBAL JOIN for distributed queries
$builder->from('local_table')->join('remote_table', 'any', 'left', ['id'], true);
// SELECT * FROM `local_table` GLOBAL ANY LEFT JOIN `remote_table` USING `id`

// Join with alias
$builder->from('orders')->join('customers', 'any', 'left', ['customer_id'], false, 'c');
// SELECT * FROM `orders` ANY LEFT JOIN `customers` AS `c` USING `customer_id`

// Join with ON clause using closure
$builder->from('orders')->anyLeftJoin(function ($join) {
    $join->table('customers')->on('orders.customer_id', '=', 'customers.id');
});
// SELECT * FROM `orders` ANY LEFT JOIN `customers` ON `orders`.`customer_id` = `customers`.`id`

// Join with subquery
$builder->from('orders')->allInnerJoin(function ($join) {
    $join->query()
        ->select('customer_id', raw('sum(amount) as total'))
        ->from('payments')
        ->groupBy('customer_id');
    $join->addUsing('customer_id');
});
// SELECT * FROM `orders` ALL INNER JOIN (SELECT `customer_id`, sum(amount) as total FROM `payments` GROUP BY `customer_id`) USING `customer_id`

// Multiple joins
$builder->from('orders')
    ->anyLeftJoin('customers', ['customer_id'])
    ->anyLeftJoin('products', ['product_id']);
// SELECT * FROM `orders` ANY LEFT JOIN `customers` USING `customer_id` ANY LEFT JOIN `products` USING `product_id`
```

--------------------------------

### Insert Data into Temporary Memory Table

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Use the `values` and `format` methods along with the `into_memory_table` helper to insert data from a file into a temporary table with the Memory engine. This is useful for temporary data manipulation before queries.

```php
$builder->table('test')->values('test.tsv')->format(Format::TSV);

into_memory_table($builder, [
    'date' => 'Date',
    'userId' => 'UInt64'
]);
```

--------------------------------

### Create ClickHouse Table

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Create tables programmatically in ClickHouse, specifying engine, order by clause, and column definitions. Options for 'if not exists' and cluster creation are available.

```php
// Create table
$builder->createTable('events', 'MergeTree ORDER BY (event_date, event_id)', [
    'event_id' => 'UInt64',
    'event_date' => 'Date',
    'event_type' => 'String',
    'user_id' => 'UInt32',
    'payload' => 'String'
]);
// CREATE TABLE events (event_id UInt64, event_date Date, event_type String, user_id UInt32, payload String) ENGINE = MergeTree ORDER BY (event_date, event_id)
```

```php
// Create table if not exists
$builder->createTableIfNotExists('logs', 'MergeTree ORDER BY timestamp', [
    'timestamp' => 'DateTime',
    'level' => 'Enum8(\'debug\'=1, \'info\'=2, \'warning\'=3, \'error\'=4)',
    'message' => 'String'
]);
```

```php
// Create table on cluster
$builder->onCluster('my_cluster')->createTable('distributed_events', 'Distributed(my_cluster, default, events, rand())', [
    'event_id' => 'UInt64',
    'event_date' => 'Date'
]);
```

--------------------------------

### Use Raw SQL Expressions

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Inject raw SQL into select, from, where, and order by clauses to bypass standard escaping. Useful for ClickHouse-specific functions and table functions.

```php
<?php

// Raw function in select
$builder->select('user_id', raw('count() as total'), raw('uniq(session_id) as unique_sessions'))
    ->from('events')
    ->groupBy('user_id');
// SELECT `user_id`, count() as total, uniq(session_id) as unique_sessions FROM `events` GROUP BY `user_id`

// Raw table reference (table functions)
$builder->from(raw('numbers(0, 100)'))->get();
// SELECT * FROM numbers(0, 100)

// Raw in WHERE
$builder->from('events')->whereRaw("toYYYYMM(event_date) = 202401");
// SELECT * FROM `events` WHERE toYYYYMM(event_date) = 202401

// Raw in ORDER BY
$builder->from('logs')->orderByRaw('toHour(timestamp) DESC');
// SELECT * FROM `logs` ORDER BY toHour(timestamp) DESC

// Combining raw with regular methods
$builder
    ->select('date', raw('sum(amount) as daily_total'))
    ->from('transactions')
    ->groupBy('date')
    ->having(raw('daily_total'), '>', 10000);
```

--------------------------------

### Insert Multiple Files Asynchronously

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Utilize `insertFiles` to insert data from multiple local files into a ClickHouse table asynchronously. This is efficient for large batch insertions.

```php
$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)
```

--------------------------------

### Simple Limit Clause in ClickHouse Builder

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Use the limit method for standard SQL LIMIT clause. The first argument is the offset and the second is the number of rows.

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

```sql
SELECT * FROM `table` LIMIT 100, 10
```

--------------------------------

### Specify Table and Alias in FROM Clause

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Define the table to query from and optionally provide an alias for the table using the from method.

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

--------------------------------

### Order By Aliases in ClickHouse Builder

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Convenience methods orderByAsc and orderByDesc are available for simple ascending or descending order by clauses.

```php
$builder->orderByAsc('column');
```

```php
$builder->orderByDesc('column');
```

--------------------------------

### Configure ClickHouse Database Connections

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Define ClickHouse connections in config/database.php for single servers, multiple server pools, or clusters.

```php
<?php

// config/database.php - Single server configuration
'connections' => [
    'clickhouse' => [
        'driver' => 'clickhouse',
        'host' => 'ch-server.domain.com',
        'port' => '8123',
        'database' => 'default',
        'username' => 'default',
        'password' => '',
        'options' => [
            'timeout' => 10,
            'protocol' => 'https'
        ]
    ]
]

// config/database.php - Multiple servers configuration
'connections' => [
    'clickhouse' => [
        'driver' => 'clickhouse',
        'servers' => [
            [
                'host' => 'ch-01.domain.com',
                'port' => '8123',
                'database' => 'default',
                'username' => 'user',
                'password' => 'pass',
                'options' => ['timeout' => 10, 'protocol' => 'https']
            ],
            [
                'host' => 'ch-02.domain.com',
                'port' => '8123',
                'database' => 'default',
                'username' => 'user',
                'password' => 'pass',
                'options' => ['timeout' => 10, 'protocol' => 'https', 'tags' => ['replica']]
            ]
        ]
    ]
]

// config/database.php - Cluster configuration
'connections' => [
    'clickhouse' => [
        'driver' => 'clickhouse',
        'clusters' => [
            'analytics_cluster' => [
                ['host' => 'ch-shard1-01.domain.com', 'port' => '8123', 'database' => 'analytics'],
                ['host' => 'ch-shard1-02.domain.com', 'port' => '8123', 'database' => 'analytics'],
            ]
        ]
    ]
]
```

--------------------------------

### Execute Asynchronous Queries

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Perform multiple queries concurrently using closures or builder instances. Results are returned as an array indexed by the order of execution.

```php
<?php

// Async queries with closure
$result = $builder
    ->from('events')
    ->where('date', '2024-01-01')
    ->asyncWithQuery(function ($query) {
        $query->from('events')->where('date', '2024-01-02');
    })
    ->asyncWithQuery(function ($query) {
        $query->from('events')->where('date', '2024-01-03');
    })
    ->get();

// Results are returned as array indexed by query order
$day1Results = $result[0];
$day2Results = $result[1];
$day3Results = $result[2];

// Async with builder instances
$query1 = $builder->newQuery()->from('table1')->where('id', 1);
$query2 = $builder->newQuery()->from('table2')->where('id', 2);

$results = $builder
    ->from('table0')
    ->asyncWithQuery($query1)
    ->asyncWithQuery($query2)
    ->get();

// Chain method returns new builder for adding to async queue
$builder->from('base_table')
    ->asyncWithQuery()
    ->from('other_table')
    ->where('status', 'active');
```

--------------------------------

### Use Temporary Tables for Filtering

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Utilize temporary tables to filter large datasets efficiently. Add temporary tables from files and reference them in WHERE or PREWHERE clauses.

```php
// Add temporary table for filtering
$builder->addFile(new TempTable('user_ids', '/path/to/user_ids.tsv', ['id' => 'UInt64'], Format::TSV));

// Use temporary table in WHERE IN
$result = $builder
    ->select('*')
    ->from('events')
    ->whereIn('user_id', 'user_ids')  // References the temp table
    ->get();
// SELECT * FROM `events` WHERE `user_id` IN `user_ids`
```

```php
// PREWHERE with temporary table
$builder->addFile(new TempTable('allowed_ids', '/path/to/ids.csv', ['id' => 'UInt64'], Format::CSV));
$builder->from('large_table')->preWhereIn('id', 'allowed_ids')->get();
```

```php
// Create memory table helper
$builder->table('temp_data')->values('/path/to/data.tsv')->format(Format::TSV);
into_memory_table($builder, [
    'id' => 'UInt64',
    'value' => 'String'
]);
// Creates Memory table, inserts data, available for queries
```

--------------------------------

### Select Columns in Clickhouse Builder

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Specify columns to select using string arguments, arrays of strings, or associative arrays for aliasing. The builder translates these into SQL SELECT statements.

```php
$builder->select('column', 'column2', 'column3 as alias');
```

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

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

--------------------------------

### Combine Queries with UNION ALL

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Merge results from multiple queries using the unionAll method. Supports closures and builder instances for subqueries.

```php
<?php

// Union with closure
$builder->from('orders_2023')
    ->select('id', 'amount', 'created_at')
    ->unionAll(function ($query) {
        $query->select('id', 'amount', 'created_at')->from('orders_2024');
    });
// SELECT `id`, `amount`, `created_at` FROM `orders_2023` UNION ALL SELECT `id`, `amount`, `created_at` FROM `orders_2024`
```

```php
// Union with builder instance
$query2024 = $builder->newQuery()
    ->select('user_id', raw('count() as cnt'))
    ->from('events_2024')
    ->groupBy('user_id');

$builder->from('events_2023')
    ->select('user_id', raw('count() as cnt'))
    ->groupBy('user_id')
    ->unionAll($query2024);
```

```php
// Multiple unions
$builder->from('table1')
    ->unionAll(function ($q) { $q->from('table2'); })
    ->unionAll(function ($q) { $q->from('table3'); });
// SELECT * FROM `table1` UNION ALL SELECT * FROM `table2` UNION ALL SELECT * FROM `table3`
```

--------------------------------

### SQL for Array JOIN Operations

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

These SQL queries show the output of ARRAY JOIN and LEFT ARRAY JOIN operations.

```sql
SELECT * FROM `test` ARRAY JOIN `someArr`
```

```sql
SELECT * FROM `test` LEFT ARRAY JOIN `someArr`
```

--------------------------------

### Union ALL Operation in ClickHouse Builder

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Combine results from multiple queries using the unionAll method. It accepts a closure or a builder instance.

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

```sql
SELECT * FROM `table` UNION ALL SELECT `column1` FROM `table` UNION ALL SELECT `column2` FROM `table`
```

--------------------------------

### SQL for FROM Clause with Subquery

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

This SQL query demonstrates a SELECT statement where the FROM clause uses a subquery.

```sql
SELECT * FROM (SELECT `column` FROM `table`)
```

--------------------------------

### Select Columns with Aliases and Expressions

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Define columns to retrieve using strings, arrays, closures, or subqueries.

```php
<?php

// Select specific columns
$builder->select('column1', 'column2', 'column3');
// SELECT `column1`, `column2`, `column3`

// Select with array syntax
$builder->select(['column1', 'column2']);
// SELECT `column1`, `column2`

// Select with aliases
$builder->select('column1 as alias1', 'column2 as alias2');
// SELECT `column1` AS `alias1`, `column2` AS `alias2`

// Select with key-value alias syntax
$builder->select(['column1' => 'alias1', 'column2' => 'alias2']);
// SELECT `column1` AS `alias1`, `column2` AS `alias2`

// Add columns to existing select
$builder->select('column1')->addSelect('column2', 'column3');
// SELECT `column1`, `column2`, `column3`

// Select with closure for complex expressions
$builder->select(function ($column) {
    $column->name('time')->sumIf('time', '>', 10);
});
// SELECT sumIf(`time`, time > 10)

// Select with subquery
$builder->select(function ($column) {
    $column->as('total')
        ->query()
        ->select(raw('count()'))
        ->from('orders');
})->from('customers');
// SELECT (SELECT count() FROM `orders`) AS `total` FROM `customers`
```

--------------------------------

### Configure ClickHouse Connection

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Define ClickHouse connection settings in config/database.php for single servers, multiple servers, clusters, and tagged servers.

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

```php
'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'
                ]
            ]
        ]
    ]
]
```

```php
'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'
                    ]
                ]
            ]
        ]
    ]
]
```

```php
'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'
                ],
            ],
        ],
    ],
]
```

--------------------------------

### Register Service Provider in Lumen

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Register the ClickhouseServiceProvider in bootstrap/app.php.

```php
$app->register(\Tinderbox\ClickhouseBuilder\Integrations\Laravel\ClickhouseServiceProvider::class);
```

--------------------------------

### JOIN with Subquery

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Perform joins with subqueries by passing a closure or a query builder instance as the table argument. This allows joining on the results of another query.

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

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

--------------------------------

### SQL for JOIN Operation

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

This SQL query demonstrates a LEFT JOIN operation with a specified alias and USING clause.

```sql
SELECT * FROM `table` GLOBAL ANY LEFT JOIN `another_table` AS `alias` USING `column1`, `column2`
```

--------------------------------

### Define From Clause

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Specify the source table, including support for FINAL modifiers, subqueries, and table functions.

```php
<?php

// Basic from
$builder->select('*')->from('users');
// SELECT * FROM `users`

// From with alias
$builder->from('users', 'u');
// SELECT * FROM `users` AS `u`

// From with FINAL modifier (for CollapsingMergeTree)
$builder->from('users', 'u', true);
// SELECT * FROM `users` AS `u` FINAL

// Chain final() method
$builder->from('events')->final();
// SELECT * FROM `events` FINAL

// From with subquery using closure
$builder->from(function ($from) {
    $from->query()->select('id', 'name')->from('users')->where('active', 1);
})->as('active_users');
// SELECT * FROM (SELECT `id`, `name` FROM `users` WHERE `active` = 1) AS `active_users`

// From with builder instance as subquery
$subquery = $builder->newQuery()->select('id')->from('products')->where('stock', '>', 0);
$builder->from($subquery);
// SELECT * FROM (SELECT `id` FROM `products` WHERE `stock` > 0)

// Remote table function
$builder->from(function ($from) {
    $from->remote('192.168.1.100:9000', 'database', 'table');
});
// SELECT * FROM remote('192.168.1.100:9000', database, table)

// Merge table function
$builder->from(function ($from) {
    $from->merge('logs', '^log_\d{4}_\d{2}$');
});
// SELECT * FROM merge(logs, '^log_\d{4}_\d{2}$')
```

--------------------------------

### Build PREWHERE Conditions

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Use PREWHERE for optimized filtering on MergeTree tables. PREWHERE clauses are executed before reading other columns.

```php
<?php

// Basic PREWHERE
$builder->from('events')->preWhere('event_type', '=', 'click');
// SELECT * FROM `events` PREWHERE `event_type` = 'click'

// PREWHERE with OR
$builder->from('logs')->preWhere('level', 'error')->orPreWhere('level', 'warning');
// SELECT * FROM `logs` PREWHERE `level` = 'error' OR `level` = 'warning'

// PREWHERE IN
$builder->from('events')->preWhereIn('user_id', [1, 2, 3, 4, 5]);
// SELECT * FROM `events` PREWHERE `user_id` IN (1, 2, 3, 4, 5)

// PREWHERE BETWEEN
$builder->from('logs')->preWhereBetween('timestamp', ['2024-01-01', '2024-01-31']);
// SELECT * FROM `logs` PREWHERE `timestamp` BETWEEN '2024-01-01' AND '2024-01-31'

// Combined PREWHERE and WHERE (PREWHERE runs first)
$builder
    ->from('events')
    ->preWhere('date', '>=', '2024-01-01')
    ->where('user_id', '=', 123)
    ->get();
// SELECT * FROM `events` PREWHERE `date` >= '2024-01-01' WHERE `user_id` = 123
```

--------------------------------

### Perform Count Queries

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Retrieve row counts with optional filtering and grouping. Use getCountQuery() to inspect the generated SQL before execution.

```php
<?php

// Simple count
$count = $builder->table('users')->count();
// SELECT count() as `count` FROM `users`

// Count with conditions
$activeUsers = $builder
    ->table('users')
    ->where('status', 'active')
    ->where('created_at', '>=', '2024-01-01')
    ->count();

// Count with grouping (returns count of groups)
$categoryCount = $builder
    ->table('products')
    ->groupBy('category_id')
    ->count();
// Returns number of distinct categories

// Get count query for inspection
$countQuery = $builder->table('events')->where('type', 'click')->getCountQuery();
echo $countQuery->toSql();
// SELECT count() as `count` FROM `events` WHERE `type` = 'click'
```

--------------------------------

### Build WHERE Conditions

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Construct various WHERE clauses using standard operators, IN, BETWEEN, nested closures, subqueries, and raw expressions.

```php
<?php

// Basic equality
$builder->from('users')->where('status', '=', 'active');
// SELECT * FROM `users` WHERE `status` = 'active'

// Shorthand equality (operator defaults to =)
$builder->from('users')->where('status', 'active');
// SELECT * FROM `users` WHERE `status` = 'active'

// Comparison operators
$builder->from('orders')->where('amount', '>', 1000)->where('quantity', '<=', 10);
// SELECT * FROM `orders` WHERE `amount` > 1000 AND `quantity` <= 10

// OR conditions
$builder->from('users')->where('role', 'admin')->orWhere('role', 'moderator');
// SELECT * FROM `users` WHERE `role` = 'admin' OR `role` = 'moderator'

// IN operator (automatic when value is array)
$builder->from('users')->where('status', ['active', 'pending']);
// SELECT * FROM `users` WHERE `status` IN ('active', 'pending')

// whereIn / whereNotIn
$builder->from('products')->whereIn('category_id', [1, 2, 3]);
// SELECT * FROM `products` WHERE `category_id` IN (1, 2, 3)

$builder->from('products')->whereNotIn('category_id', [4, 5]);
// SELECT * FROM `products` WHERE `category_id` NOT IN (4, 5)

// GLOBAL IN for distributed queries
$builder->from('users')->whereGlobalIn('country_id', [1, 2, 3]);
// SELECT * FROM `users` WHERE `country_id` GLOBAL IN (1, 2, 3)

// BETWEEN
$builder->from('orders')->whereBetween('created_at', ['2024-01-01', '2024-12-31']);
// SELECT * FROM `orders` WHERE `created_at` BETWEEN '2024-01-01' AND '2024-12-31'

// NOT BETWEEN
$builder->from('products')->whereNotBetween('price', [100, 500]);
// SELECT * FROM `products` WHERE NOT ( `price` BETWEEN 100 AND 500 )

// BETWEEN with columns
$builder->from('events')->whereBetweenColumns('event_time', ['start_time', 'end_time']);
// SELECT * FROM `events` WHERE `event_time` BETWEEN `start_time` AND `end_time`

// Nested conditions with closure
$builder->from('users')->where(function ($query) {
    $query->where('role', 'admin')->orWhere('role', 'moderator');
})->where('active', 1);
// SELECT * FROM `users` WHERE (`role` = 'admin' OR `role` = 'moderator') AND `active` = 1

// Subquery in WHERE
$builder->from('orders')->where('customer_id', 'IN', function ($query) {
    $query->select('id')->from('customers')->where('vip', 1);
});
// SELECT * FROM `orders` WHERE `customer_id` IN (SELECT `id` FROM `customers` WHERE `vip` = 1)

// Raw WHERE expression
$builder->from('events')->whereRaw("toDate(created_at) = '2024-01-15'");
// SELECT * FROM `events` WHERE toDate(created_at) = '2024-01-15'
```

--------------------------------

### Limit and Paginate Query Results

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Control the number of returned rows using limit, take, and limitBy. Supports offset and ClickHouse-specific LIMIT BY clause.

```php
<?php

// Simple limit
$builder->from('users')->limit(10);
// SELECT * FROM `users` LIMIT 10
```

```php
// Limit with offset
$builder->from('users')->limit(10, 20);  // 10 rows starting from offset 20
// SELECT * FROM `users` LIMIT 20, 10
```

```php
// Alias: take()
$builder->from('products')->take(5);
// SELECT * FROM `products` LIMIT 5
```

```php
// LIMIT BY - limit per group (ClickHouse specific)
$builder->from('events')->limitBy(3, 'user_id');
// SELECT * FROM `events` LIMIT 3 BY `user_id`
```

```php
// LIMIT BY multiple columns
$builder->from('logs')->limitBy(5, 'server_id', 'log_level');
// SELECT * FROM `logs` LIMIT 5 BY `server_id`, `log_level`
```

```php
// Combined LIMIT BY and LIMIT
$builder->from('events')
    ->select('user_id', 'event_type', 'created_at')
    ->orderByDesc('created_at')
    ->limitBy(3, 'user_id')
    ->limit(100);
// SELECT ... LIMIT 3 BY `user_id` LIMIT 100
```

--------------------------------

### Dictionary WHERE Clause

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Shows how to use the `whereDict` method to filter based on dictionary attributes. It supports simple keys and complex keys represented as tuples.

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

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

--------------------------------

### FROM Clause with Subquery

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Use a closure or a pre-built query builder instance to specify a subquery in the FROM clause. This allows for complex data sourcing.

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

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

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

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

--------------------------------

### Grouped WHERE Clauses

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Shows how to group multiple WHERE conditions using a closure. Conditions within the closure are combined with AND. If a `from` clause is specified within the closure, it creates a subquery.

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

--------------------------------

### Generate and Manage SQL Queries

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Compile queries to SQL strings without execution or prepare asynchronous query operations.

```php
<?php

// Get SQL string
$sql = $builder
    ->select('id', 'name')
    ->from('users')
    ->where('active', 1)
    ->orderByAsc('name')
    ->limit(10)
    ->toSql();

echo $sql;
// SELECT `id`, `name` FROM `users` WHERE `active` = 1 ORDER BY `name` ASC LIMIT 10

// Get async SQL queries
$builder->from('table1')->asyncWithQuery(function ($q) {
    $q->from('table2');
});

$asyncSqls = $builder->toAsyncSqls();
// Returns array with query strings and files for each async query

$asyncQueries = $builder->toAsyncQueries();
// Returns array of Query objects
```

--------------------------------

### Register Service Provider in Laravel

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Add the ClickhouseServiceProvider to the providers array in config/app.php.

```php
    'providers' => [
        ...
        \Tinderbox\ClickhouseBuilder\Integrations\Laravel\ClickhouseServiceProvider::class,
        ...
    ]
```

--------------------------------

### Insert Single File into ClickHouse Table

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Use the `insertFile` method to insert data from a single local file into a specified ClickHouse table.

```php
$builder->table('test')->insertFile(['date', 'userId'], 'test.tsv', Format::TSV);
```

--------------------------------

### Perform ARRAY JOIN Operations

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Use ARRAY JOIN to expand array columns in ClickHouse, with support for left joins and filtering.

```php
<?php

// Basic ARRAY JOIN
$builder->from('events')->arrayJoin('tags');
// SELECT * FROM `events` ARRAY JOIN `tags`

// LEFT ARRAY JOIN (includes rows with empty arrays)
$builder->from('events')->leftArrayJoin('tags');
// SELECT * FROM `events` LEFT ARRAY JOIN `tags`

// ARRAY JOIN with selection
$builder
    ->select('event_id', 'tags')
    ->from('events')
    ->arrayJoin('tags')
    ->where('tags', '=', 'important')
    ->get();
// SELECT `event_id`, `tags` FROM `events` ARRAY JOIN `tags` WHERE `tags` = 'important'
```

--------------------------------

### Build HAVING Conditions

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Filter results after grouping using the HAVING clause.

```php
<?php

// Basic HAVING
$builder->from('orders')
    ->select('customer_id', raw('count() as order_count'))
    ->groupBy('customer_id')
    ->having('order_count', '>', 10);
// SELECT `customer_id`, count() as order_count FROM `orders` GROUP BY `customer_id` HAVING `order_count` > 10

// HAVING with OR
$builder->from('products')
    ->select('category_id', raw('avg(price) as avg_price'))
    ->groupBy('category_id')
    ->having('avg_price', '>', 100)
    ->orHaving('avg_price', '<', 10);
// ... HAVING `avg_price` > 100 OR `avg_price` < 10

// HAVING IN
$builder->from('orders')
    ->select('status', raw('count() as cnt'))
    ->groupBy('status')
    ->havingIn('status', ['completed', 'shipped']);
// ... HAVING `status` IN ('completed', 'shipped')

// HAVING BETWEEN
$builder->from('sales')
    ->select('product_id', raw('sum(quantity) as total'))
    ->groupBy('product_id')
    ->havingBetween('total', [100, 1000]);
// ... HAVING `total` BETWEEN 100 AND 1000
```

--------------------------------

### Query ClickHouse Dictionaries with whereDict

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Use the whereDict method to query ClickHouse dictionaries for filtering. Supports simple and composite keys.

```php
<?php

// Simple dictionary lookup
$builder->from('orders')->whereDict('geo_dict', 'country_name', 'country_id', 'USA');
// SELECT dictGetString('geo_dict', 'country_name', 'country_id') as `country_name` FROM `orders` WHERE `country_name` = 'USA'
```

```php
// Dictionary with composite key
$builder->from('orders')->whereDict('product_dict', 'name', ['category_id', 'product_id'], '=', 'Widget');
// SELECT dictGetString('product_dict', 'name', tuple('category_id', 'product_id')) as `name` FROM `orders` WHERE `name` = 'Widget'
```

```php
// Multiple dictionary conditions
$builder->from('events')
    ->whereDict('user_dict', 'username', 'user_id', '=', 'john')
    ->orWhereDict('user_dict', 'email', 'user_id', 'LIKE', '%@example.com');
```

--------------------------------

### SQL for Subquery as Column

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

This SQL query illustrates the result of selecting a subquery aliased as 'alias'.

```sql
SELECT  (SELECT `column` FROM `table) as `alias`
```

--------------------------------

### Sort Query Results with orderBy

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Sort results using orderByAsc, orderByDesc, orderBy, and orderByRaw. Supports collation for locale-specific sorting.

```php
<?php

// Ascending order
$builder->from('users')->orderByAsc('name');
// SELECT * FROM `users` ORDER BY `name` ASC
```

```php
// Descending order
$builder->from('products')->orderByDesc('price');
// SELECT * FROM `products` ORDER BY `price` DESC
```

```php
// Generic orderBy method
$builder->from('events')->orderBy('created_at', 'desc');
// SELECT * FROM `events` ORDER BY `created_at` DESC
```

```php
// With collation for locale-specific sorting
$builder->from('users')->orderByDesc('name', 'ru');
// SELECT * FROM `users` ORDER BY `name` DESC COLLATE 'ru'
```

```php
// Multiple order clauses
$builder->from('products')
    ->orderByDesc('category')
    ->orderByAsc('name');
// SELECT * FROM `products` ORDER BY `category` DESC, `name` ASC
```

```php
// Raw order expression
$builder->from('events')->orderByRaw('toDate(created_at) DESC, id ASC');
// SELECT * FROM `events` ORDER BY toDate(created_at) DESC, id ASC
```

--------------------------------

### Select with Column Closure for Complex Expressions

Source: https://github.com/the-tinderbox/clickhousebuilder/blob/master/README.md

Use a closure with the select method to define complex column expressions, such as aggregated functions with conditions. The closure receives a Column instance for configuration.

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

--------------------------------

### Group Query Results with groupBy

Source: https://context7.com/the-tinderbox/clickhousebuilder/llms.txt

Aggregate results by one or more columns using groupBy and addGroupBy. Supports array syntax for multiple columns.

```php
<?php

// Single column grouping
$builder->from('orders')
    ->select('status', raw('count() as count'))
    ->groupBy('status');
// SELECT `status`, count() as count FROM `orders` GROUP BY `status`
```

```php
// Multiple columns
$builder->from('sales')
    ->select('year', 'month', raw('sum(amount) as total'))
    ->groupBy('year', 'month');
// SELECT `year`, `month`, sum(amount) as total FROM `sales` GROUP BY `year`, `month`
```

```php
// Add to existing group by
$builder->from('events')
    ->select('date', 'category', 'subcategory', raw('count()'))
    ->groupBy('date')
    ->addGroupBy('category', 'subcategory');
// SELECT ... GROUP BY `date`, `category`, `subcategory`
```

```php
// Group by with array syntax
$builder->from('logs')->groupBy(['level', 'source']);
// SELECT * FROM `logs` GROUP BY `level`, `source`
```