Query Builder

The ClickHouse Query Builder extends Laravel's Query Builder with full compatibility for standard operations, plus ClickHouse-specific features like FINAL, ARRAY JOIN, and SETTINGS clauses.

Basic Queries

All standard Laravel Query Builder methods work as expected with the ClickHouse connection.

Selecting Data

php

use Illuminate\Support\Facades\DB;

// Select all columns
DB::connection('clickhouse')->table('events')->get();
// select * from `events`

// Select specific columns
DB::connection('clickhouse')->table('events')->select('id', 'name')->get();
// select `id`, `name` from `events`

// Column alias
DB::connection('clickhouse')->table('events')->select('column as alias')->get();
// select `column` as `alias` from `events`

// Cross-database query
DB::connection('clickhouse')->table('database.table')->get();
// select * from `database`.`table`

// Distinct
DB::connection('clickhouse')->table('events')->select('status')->distinct()->get();
// select distinct `status` from `events`

Where Clauses

php

// Basic where
$query->where('status', 'active');
// where `status` = 'active'

// Where with operator
$query->where('age', '>', 18);
// where `age` > 18

// whereIn / whereNotIn
$query->whereIn('status', ['active', 'pending']);
// where `status` in ('active', 'pending')

$query->whereNotIn('status', ['banned', 'deleted']);
// where `status` not in ('banned', 'deleted')

// whereBetween
$query->whereBetween('age', [18, 65]);
// where `age` between 18 and 65

// whereNull / whereNotNull
$query->whereNull('deleted_at');
// where `deleted_at` is null

$query->whereNotNull('email');
// where `email` is not null

// whereColumn
$query->whereColumn('updated_at', '>', 'created_at');
// where `updated_at` > `created_at`

// whereAll - all columns must match
$query->whereAll(['first_name', 'last_name'], 'like', '%John%');
// where (`first_name` like '%John%' and `last_name` like '%John%')

// whereAny - any column may match
$query->whereAny(['email', 'phone'], 'like', '%search%');
// where (`email` like '%search%' or `phone` like '%search%')

// whereNone - no column should match
$query->whereNone(['email', 'phone'], 'like', '%spam%');
// where not (`email` like '%spam%' or `phone` like '%spam%')

// whereExists
$query->whereExists(function ($query) {
    $query->from('orders')->whereColumn('orders.user_id', 'users.id');
});
// where exists (select * from `orders` where `orders`.`user_id` = `users`.`id`)

// whereRaw
$query->whereRaw('column = ?', ['value']);
// where column = 'value'

Grouping, Ordering & Having

php

// Group by
$query->groupBy('status')->get();
// group by `status`

$query->groupBy('status', 'type')->get();
// group by `status`, `type`

// Order by
$query->orderBy('created_at', 'desc')->get();
// order by `created_at` desc

// Random order (uses ClickHouse's randCanonical())
$query->inRandomOrder()->get();
// order by randCanonical()

// Having
$query->groupBy('status')->having('count', '>', 10)->get();
// group by `status` having `count` > 10

// havingBetween
$query->groupBy('status')->havingBetween('count', [1, 100])->get();
// group by `status` having `count` between 1 and 100

// havingNull / havingNotNull
$query->havingNull('column');
// having `column` is null

// havingRaw
$query->havingRaw('count(*) > ?', [10]);
// having count(*) > 10

Limit & Offset

php

$query->limit(10)->offset(20)->get();
// select * from `table` limit 10 offset 20

LIMIT BY

LIMIT n BY returns at most n rows for each distinct value of the specified columns. It is placed before the LIMIT clause.

php

// Limit 3 rows per user
$query->from('events')->limitBy(3, 'user_id')->get();
// select * from `events` limit 3 by `user_id`

// Limit 1 row per user per day
$query->from('events')->limitBy(1, ['user_id', 'date'])->get();
// select * from `events` limit 1 by `user_id`, `date`

// Combined with LIMIT
$query->from('events')
    ->orderBy('created_at', 'desc')
    ->limitBy(3, 'user_id')
    ->limit(100)
    ->get();
// select * from `events` order by `created_at` desc limit 3 by `user_id` limit 100

Aggregates

php

DB::connection('clickhouse')->table('events')->count();
// select count(*) as aggregate from `events`

DB::connection('clickhouse')->table('events')->min('duration');
// select min(`duration`) as aggregate from `events`

DB::connection('clickhouse')->table('events')->max('duration');
// select max(`duration`) as aggregate from `events`

DB::connection('clickhouse')->table('events')->sum('amount');
// select sum(`amount`) as aggregate from `events`

DB::connection('clickhouse')->table('events')->avg('score');
// select avg(`score`) as aggregate from `events`

DB::connection('clickhouse')->table('events')->exists();
// select exists(select * from `events`) as `exists`

SAMPLE Clause

The SAMPLE clause enables approximate query processing by reading only a fraction of the data. It is placed immediately after the FROM clause.

php

// Sample 10% of rows
$query->from('events')->sample(0.1)->get();
// select * from `events` sample 0.1

// Sample an absolute number of rows
$query->from('events')->sample(1_000_000)->get();
// select * from `events` sample 1000000

// Sample with an offset (shifts the sampling window)
$query->from('events')->sample(0.1, 0.5)->get();
// select * from `events` sample 0.1 offset 0.5

// Combined with WHERE and LIMIT
$query->from('events')
    ->sample(0.1)
    ->where('type', 'click')
    ->limit(1000)
    ->get();
// select * from `events` sample 0.1 where `type` = 'click' limit 1000

Note: The table must use a sampled MergeTree engine (e.g. MergeTree() SAMPLE BY) for SAMPLE to work.

FINAL Clause

The FINAL modifier forces ClickHouse to merge data parts before returning results. This is particularly useful with ReplacingMergeTree, CollapsingMergeTree, and other merge tree engines that perform background merges.

php

DB::connection('clickhouse')->table('events', final: true)->get();
// select * from `events` final

DB::connection('clickhouse')->table('events', final: true)
    ->where('user_id', 1)
    ->get();
// select * from `events` final where `user_id` = 1

Note: The FINAL clause cannot be used with subqueries. Attempting to do so will throw a LogicException.

php

// This will throw a LogicException
DB::connection('clickhouse')->table(
    DB::connection('clickhouse')->table('events'),
    final: true
)->get();

ARRAY JOIN

ClickHouse's ARRAY JOIN clause expands array columns into individual rows -- similar to an UNNEST operation in standard SQL.

Basic Array Join

php

$query->from('events')->arrayJoin('tags')->get();
// select * from `events` array join `tags`

Multiple Columns

php

$query->from('events')->arrayJoin(['tags', 'scores'])->get();
// select * from `events` array join `tags`, `scores`

With Alias

php

$query->from('events')->arrayJoin('tags', 'tag')->get();
// select *, `tag` from `events` array join `tags` as `tag`

With Array of Aliases

php

$query->from('events')->arrayJoin([
    'alias_a' => 'column_a',
    'alias_b' => 'column_b',
    'column_c',
])->get();
// select *, `alias_a`, `alias_b` from `events`
//   array join `column_a` as `alias_a`, `column_b` as `alias_b`, `column_c`

Subquery Array Join

php

$query->from('events')->arrayJoin(
    DB::connection('clickhouse')->table('tags'),
    'tag'
)->get();
// select *, `tag` from `events` array join (select * from `tags`) as `tag`

// Or using arrayJoinSub directly
$query->from('events')->arrayJoinSub(
    DB::connection('clickhouse')->table('tags'),
    'tag'
)->get();
// select *, `tag` from `events` array join (select * from `tags`) as `tag`

Left Array Join

LEFT ARRAY JOIN preserves rows even when the array is empty, producing NULL or default values for the expanded columns.

php

$query->from('events')->leftArrayJoin('tags')->get();
// select * from `events` left array join `tags`

$query->from('events')->leftArrayJoinSub(
    DB::connection('clickhouse')->table('tags'),
    'tag'
)->get();
// select *, `tag` from `events` left array join (select * from `tags`) as `tag`

Note: You cannot mix arrayJoin and leftArrayJoin in the same query. Doing so will throw a LogicException.

Common Table Expressions (WITH)

ClickHouse supports WITH clauses (CTEs) for defining named subqueries and scalar expressions.

Scalar Value

php

$query->withQuery('value', 'alias')->from('events')->get();
// with 'value' as `alias` select * from `events`

Scalar Subquery

php

$subquery = DB::connection('clickhouse')->table('events')->selectRaw('count(*)');

$query->withQuery($subquery, 'total')->from('events')->get();
// with (select count(*) from `events`) as `total` select * from `events`

Raw Expression

php

$query->withQueryRaw('?', 'alias', ['value'])->from('events')->get();
// with 'value' as `alias` select * from `events`

Named Subquery (WITH ... AS)

php

$subquery = DB::connection('clickhouse')->table('events')
    ->where('status', 'active');

$query->withQuerySub($subquery, 'active_events')->from('active_events')->get();
// with `active_events` as (select * from `events` where `status` = 'active')
//   select * from `active_events`

Recursive CTE

php

$subquery = DB::connection('clickhouse')->table('categories');

$query->withQueryRecursive($subquery, 'tree')->from('tree')->get();
// with recursive `tree` as (select * from `categories`) select * from `tree`

ClickHouse-Specific Joins

All standard Laravel join methods work as expected. In addition, this package provides ClickHouse-specific join types:

Method	SQL Type
`join()` / `innerJoin()`	`INNER JOIN`
`leftJoin()`	`LEFT JOIN`
`rightJoin()`	`RIGHT JOIN`
`crossJoin()`	`CROSS JOIN`
`fullJoin()`	`FULL JOIN`
`innerAnyJoin()`	`INNER ANY JOIN`
`leftAnyJoin()`	`LEFT ANY JOIN`
`rightAnyJoin()`	`RIGHT ANY JOIN`
`semiJoin()`	`SEMI JOIN`
`rightSemiJoin()`	`RIGHT SEMI JOIN`
`antiJoin()`	`ANTI JOIN`
`rightAntiJoin()`	`RIGHT ANTI JOIN`
`asofJoin()`	`ASOF JOIN`
`leftAsofJoin()`	`LEFT ASOF JOIN`

Every method has a corresponding *Sub() variant for subquery joins (e.g. innerAnyJoinSub(), semiJoinSub(), leftAsofJoinSub()).

Examples

ANY Join -- returns at most one matching row from the right table:

php

$query->from('orders')
    ->leftAnyJoin('users', 'orders.user_id', '=', 'users.id')
    ->get();
// select * from `orders` left any join `users` on `orders`.`user_id` = `users`.`id`

SEMI Join -- returns rows from the left table that have at least one match:

php

$query->from('users')
    ->semiJoin('orders', 'users.id', '=', 'orders.user_id')
    ->get();
// select * from `users` semi join `orders` on `users`.`id` = `orders`.`user_id`

ASOF Join -- joins on the closest match for a given condition (useful for time-series data):

php

$query->from('trades')
    ->asofJoin('quotes', function ($join) {
        $join->on('trades.symbol', '=', 'quotes.symbol')
             ->on('trades.timestamp', '>=', 'quotes.timestamp');
    })
    ->get();
// select * from `trades` asof join `quotes`
//   on `trades`.`symbol` = `quotes`.`symbol` and `trades`.`timestamp` >= `quotes`.`timestamp`

Subquery Join:

php

$subquery = DB::connection('clickhouse')->table('orders')
    ->select('user_id', DB::raw('count(*) as order_count'))
    ->groupBy('user_id');

$query->from('users')
    ->innerAnyJoinSub($subquery, 'user_orders', function ($join) {
        $join->on('users.id', '=', 'user_orders.user_id');
    })
    ->get();
// select * from `users` inner any join (select `user_id`, count(*) as order_count
//   from `orders` group by `user_id`) as `user_orders`
//   on `users`.`id` = `user_orders`.`user_id`

Set Operations

ClickHouse supports UNION, INTERSECT, and EXCEPT with optional DISTINCT modifiers.

UNION

php

$first = DB::connection('clickhouse')->table('events_2024');
$second = DB::connection('clickhouse')->table('events_2025');

// UNION (deduplicated)
$first->union($second)->get();
// (select * from `events_2024`) union (select * from `events_2025`)

// UNION ALL (keep duplicates)
$first->unionAll($second)->get();
// (select * from `events_2024`) union all (select * from `events_2025`)

// UNION DISTINCT (explicit deduplication)
$first->unionDistinct($second)->get();
// (select * from `events_2024`) union distinct (select * from `events_2025`)

INTERSECT

php

$first = DB::connection('clickhouse')->table('users_a');
$second = DB::connection('clickhouse')->table('users_b');

$first->intersect($second)->get();
// (select * from `users_a`) intersect (select * from `users_b`)

$first->intersectDistinct($second)->get();
// (select * from `users_a`) intersect distinct (select * from `users_b`)

EXCEPT

php

$all = DB::connection('clickhouse')->table('users');
$banned = DB::connection('clickhouse')->table('banned_users');

$all->except($banned)->get();
// (select * from `users`) except (select * from `banned_users`)

$all->exceptDistinct($banned)->get();
// (select * from `users`) except distinct (select * from `banned_users`)

Where Extensions

Empty / Not Empty

ClickHouse's empty() and notEmpty() functions check whether a value is empty (zero-length string, empty array, etc.).

php

$query->whereEmpty('name')->get();
// where empty(`name`)

$query->whereNotEmpty('name')->get();
// where not empty(`name`)

// Or variants
$query->whereEmpty('email')->orWhereEmpty('phone')->get();
// where empty(`email`) or empty(`phone`)

$query->whereNotEmpty('email')->orWhereNotEmpty('phone')->get();
// where not empty(`email`) or not empty(`phone`)

// Multiple columns
$query->whereEmpty(['first_name', 'last_name'])->get();
// where empty(`first_name`) and empty(`last_name`)

These also work in HAVING clauses:

php

$query->groupBy('status')->havingEmpty('name')->get();
// group by `status` having empty(`name`)

$query->groupBy('status')->havingNotEmpty('name')->get();
// group by `status` having not empty(`name`)

$query->groupBy('status')
    ->havingEmpty('name')
    ->orHavingEmpty('email')
    ->get();
// group by `status` having empty(`name`) or empty(`email`)

$query->groupBy('status')
    ->havingNotEmpty('name')
    ->orHavingNotEmpty('email')
    ->get();
// group by `status` having not empty(`name`) or not empty(`email`)

GLOBAL IN / GLOBAL NOT IN

In distributed ClickHouse queries, IN with a subquery is evaluated independently on each shard. GLOBAL IN evaluates the subquery once on the initiator node and broadcasts the result, avoiding repeated execution. Use it when the inner subquery is not sharded or when you need consistent results across shards.

php

// GLOBAL IN with array
$query->from('events')
    ->whereGlobalIn('user_id', [1, 2, 3])
    ->get();
// select * from `events` where `user_id` global in (1, 2, 3)

// GLOBAL NOT IN with array
$query->from('events')
    ->whereGlobalNotIn('user_id', [1, 2, 3])
    ->get();
// select * from `events` where `user_id` global not in (1, 2, 3)

// OR GLOBAL IN / OR GLOBAL NOT IN
$query->from('events')
    ->whereGlobalIn('type', ['click'])
    ->orWhereGlobalIn('type', ['view'])
    ->get();
// select * from `events` where `type` global in ('click') or `type` global in ('view')

$query->from('events')
    ->whereGlobalNotIn('type', ['spam'])
    ->orWhereGlobalNotIn('type', ['bot'])
    ->get();
// select * from `events` where `type` global not in ('spam') or `type` global not in ('bot')

// GLOBAL IN with subquery
$subquery = DB::connection('clickhouse')->table('active_users')->select('id');
$query->from('events')
    ->whereGlobalIn('user_id', $subquery)
    ->get();
// select * from `events` where `user_id` global in (select `id` from `active_users`)

// GLOBAL IN with closure
$query->from('events')
    ->whereGlobalIn('user_id', function ($q) {
        $q->from('active_users')->select('id');
    })
    ->get();
// select * from `events` where `user_id` global in (select `id` from `active_users`)

ClickHouse Date Function Mapping

Laravel's date-based where methods are mapped to ClickHouse functions:

php

$query->whereDate('created_at', '2024-01-01')->get();
// where toDate(`created_at`) = '2024-01-01'

$query->whereDay('created_at', 15)->get();
// where toDayOfMonth(`created_at`) = 15

$query->whereMonth('created_at', 6)->get();
// where toMonth(`created_at`) = 6

$query->whereYear('created_at', 2024)->get();
// where toYear(`created_at`) = 2024

$query->whereTime('created_at', '10:20:30')->get();
// where toTime(`created_at`) = toTime(toDateTime('1970-01-01 10:20:30'))

Laravel Method	ClickHouse Function
`whereDate()`	`toDate()`
`whereDay()`	`toDayOfMonth()`
`whereMonth()`	`toMonth()`
`whereYear()`	`toYear()`
`whereTime()`	`toTime()`

PREWHERE Clause

PREWHERE is a ClickHouse-specific optimisation that filters rows before reading all selected columns. ClickHouse evaluates PREWHERE first, reads only the columns it references, and then applies WHERE on the reduced row set. This can significantly reduce I/O for wide tables.

All where* variants are supported — closures, subqueries, IN, NULL, and raw expressions all work exactly as they do for WHERE.

Basic PREWHERE

php

$query->from('events')->prewhere('type', 'click')->get();
// select * from `events` prewhere `type` = 'click'

$query->from('events')
    ->prewhere('type', 'click')
    ->where('user_id', 42)
    ->get();
// select * from `events` prewhere `type` = 'click' where `user_id` = 42

OR PREWHERE

php

$query->from('events')
    ->prewhere('type', 'click')
    ->orPrewhere('type', 'view')
    ->get();
// select * from `events` prewhere `type` = 'click' or `type` = 'view'

Raw PREWHERE

php

$query->from('events')->prewhereRaw('type = ?', ['click'])->get();
// select * from `events` prewhere type = 'click'

$query->from('events')
    ->prewhereRaw('type = ?', ['click'])
    ->orPrewhereRaw('type = ?', ['view'])
    ->get();
// select * from `events` prewhere type = 'click' or type = 'view'

PREWHERE IN / NOT IN

php

$query->from('events')->prewhereIn('type', ['click', 'view'])->get();
// select * from `events` prewhere `type` in ('click', 'view')

$query->from('events')->prewhereNotIn('type', ['spam', 'bot'])->get();
// select * from `events` prewhere `type` not in ('spam', 'bot')

// Subquery
$subquery = DB::connection('clickhouse')->table('active_types')->select('type');
$query->from('events')->prewhereIn('type', $subquery)->get();
// select * from `events` prewhere `type` in (select `type` from `active_types`)

PREWHERE NULL / NOT NULL

php

$query->from('events')->prewhereNull('deleted_at')->get();
// select * from `events` prewhere `deleted_at` is null

$query->from('events')->prewhereNotNull('published_at')->get();
// select * from `events` prewhere `published_at` is not null

Closures (grouped conditions)

php

$query->from('events')
    ->prewhere(function ($q) {
        $q->where('type', 'click')->orWhere('type', 'view');
    })
    ->where('user_id', 42)
    ->get();
// select * from `events` prewhere (`type` = 'click' or `type` = 'view') where `user_id` = 42

Settings Clause

ClickHouse allows appending SETTINGS to queries for per-query configuration.

php

// Single setting
$query->from('events')
    ->settings('max_rows_to_read', 1000000)
    ->get();
// select * from `events` settings `max_rows_to_read` = 1000000

// Multiple settings via array
$query->from('events')
    ->settings(['max_threads' => 4, 'optimize_read_in_order' => 1])
    ->get();
// select * from `events` settings `max_threads` = 4, `optimize_read_in_order` = 1

// Chaining settings
$query->from('events')
    ->settings('max_threads', 4)
    ->settings('max_rows_to_read', 1000000)
    ->get();
// select * from `events` settings `max_threads` = 4, `max_rows_to_read` = 1000000

Duplicate keys overwrite previous values:

php

$query->from('events')
    ->settings('max_threads', 2)
    ->settings('max_threads', 8)
    ->get();
// select * from `events` settings `max_threads` = 8

Insert, Update, Delete

Insert

Standard insert() works as expected:

php

DB::connection('clickhouse')->table('events')->insert([
    'id' => 1,
    'name' => 'page_view',
]);
// insert into `events` (`id`, `name`) values (1, 'page_view')

// Batch insert
DB::connection('clickhouse')->table('events')->insert([
    ['id' => 1, 'name' => 'page_view'],
    ['id' => 2, 'name' => 'click'],
]);
// insert into `events` (`id`, `name`) values (1, 'page_view'), (2, 'click')

DateTimeInterface values are stored at second precision by default: ClickHouse 25.8 and older reject sub-second content when the target is a second-precision DateTime column, so the builder truncates before sending them. To insert sub-second values into DateTime64 columns, set 'datetime_precision' => 'microsecond' or pass pre-formatted strings — see DateTime and DateTime64 Values for the full rules.

Insert Using an Input Format

Format::Values is the default input format. For large batches, pass Format::JSONEachRow to insert() to send the rows to ClickHouse as a single JSONEachRow request body instead of compiling them into an inline VALUES list. This avoids SQL value escaping and server-side SQL parsing of the row data, which is significantly faster for high row counts:

php

use ClickHouse\Enums\Format;

DB::connection('clickhouse')->table('events')->insert([
    ['id' => 1, 'name' => 'page_view'],
    ['id' => 2, 'name' => 'click'],
], format: Format::JSONEachRow);
// insert into `events` (`id`, `name`) format JSONEachRow
// {"id":1,"name":"page_view"}
// {"id":2,"name":"click"}

It is also available on Eloquent models:

php

Event::insert($rows, format: Format::JSONEachRow);

Both code paths behave like a regular insert() and return a bool.

DateTimeInterface values follow the connection's datetime_precision exactly like the Values path, enums are converted to their value (or name for pure enums), and nested PHP arrays map to ClickHouse Array columns.

Things to keep in mind:

A single row uses column names as string keys. For multiple rows, the outer array uses integer keys; they do not need to be sequential.
A string-keyed array whose values are all associative arrays is ambiguous and rejected. Use array_values() for multiple rows, or wrap a single row in an outer array.
The column list is taken from the first row, and every row must have the same keys — a mismatch throws a LogicException, because ClickHouse would otherwise silently drop unknown keys and default missing ones.
JSONEachRow parses numbers more strictly than VALUES: a fractional float (e.g. 3.5) sent to an integer column throws, where the VALUES path would let ClickHouse coerce it.
The rows are buffered into a single request body; for very large batches, chunk the rows and insert per chunk.

Update

Updates use ClickHouse's ALTER TABLE ... UPDATE syntax:

php

DB::connection('clickhouse')->table('events')
    ->where('id', 1)
    ->update(['name' => 'updated_event']);
// alter table `events` update `name` = 'updated_event' where `id` = 1

Note: Update with joins is not supported and will throw a LogicException. Use joinGet or dictGet functions instead.

Delete

Standard delete uses ALTER TABLE ... DELETE:

php

DB::connection('clickhouse')->table('events')
    ->where('status', 'expired')
    ->delete();
// alter table `events` delete where `status` = 'expired'

Lightweight delete uses the DELETE FROM syntax, which is faster but has different semantics:

php

DB::connection('clickhouse')->table('events')
    ->where('status', 'expired')
    ->delete(lightweight: true);
// delete from `events` where `status` = 'expired'

You can enable lightweight deletes globally via the connection configuration:

php

// config/database.php
'clickhouse' => [
    // ...
    'use_lightweight_delete' => true,
],

Delete with partition restricts the delete operation to a specific partition:

php

DB::connection('clickhouse')->table('events')
    ->where('status', 'expired')
    ->delete(partition: '202401');
// alter table `events` delete in partition '202401' where `status` = 'expired'

Lightweight delete with partition:

php

DB::connection('clickhouse')->table('events')
    ->where('status', 'expired')
    ->delete(lightweight: true, partition: '202401');
// delete from `events` in partition '202401' where `status` = 'expired'

Note: Delete with joins is not supported and will throw a LogicException.

ON CLUSTER

cluster() injects an ON CLUSTER clause into ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE (including lightweight deletes). Use it when running mutations against a Distributed or ReplicatedMergeTree table in a multi-node cluster.

php

// Update on cluster
DB::connection('clickhouse')->table('events')
    ->cluster('my_cluster')
    ->where('status', 'expired')
    ->update(['status' => 'archived']);
// alter table `events` on cluster `my_cluster` update `status` = 'archived' where `status` = 'expired'

// Standard delete on cluster
DB::connection('clickhouse')->table('events')
    ->cluster('my_cluster')
    ->where('status', 'expired')
    ->delete();
// alter table `events` on cluster `my_cluster` delete where `status` = 'expired'

// Lightweight delete on cluster
DB::connection('clickhouse')->table('events')
    ->cluster('my_cluster')
    ->where('status', 'expired')
    ->delete(lightweight: true);
// delete from `events` on cluster `my_cluster` where `status` = 'expired'

// Delete with partition on cluster
DB::connection('clickhouse')->table('events')
    ->cluster('my_cluster')
    ->where('status', 'expired')
    ->delete(partition: '202401');
// alter table `events` on cluster `my_cluster` delete in partition '202401' where `status` = 'expired'

Truncate

php

DB::connection('clickhouse')->table('events')->truncate();
// truncate table `events`

DateTime and DateTime64 Values

How DateTimeInterface values (Carbon, DateTime, …) are rendered is governed by the datetime_precision connection option:

php

use ClickHouse\Enums\DateTimePrecision;

'clickhouse' => [
    // ...
    'datetime_precision' => DateTimePrecision::Second,
    // or, environment-driven, using the enum's string values:
    'datetime_precision' => env('CLICKHOUSE_DATETIME_PRECISION', 'second'),
],

second (default) — DateTimeInterface objects are truncated to Y-m-d H:i:s wherever the driver renders them: query bindings and insert values in every input format. This matches Laravel's behavior on other databases and works in every context on every ClickHouse version, at the cost of sub-second exactness.
microsecond — objects carrying microseconds keep them: comparison bindings are wrapped in toDateTime64(..., 6) for exact semantics, and inserts (Values and JSONEachRow) render microsecond strings. Meant for databases whose date columns are DateTime64.

The rule of thumb: objects follow the option; strings carry their own precision. Values you stringify yourself — a model's $dateFormat, or $carbon->format('Y-m-d H:i:s.u') — are never touched by it. Choosing Format::JSONEachRow is a transport decision, not a precision one, so its objects follow the option like every other path.

The Default: Second Precision

Every inferred rendering is a plain 'Y-m-d H:i:s' literal:

php

$query->where('created_at', '>', now())->get();
// where `created_at` > '2026-08-13 10:00:00'

$query->where('id', 1)->update(['finished_at' => now()]);
// alter table `events` update `finished_at` = '2026-08-13 10:00:00' where `id` = 1

Every context works on every ClickHouse version, and primary-key, MinMax, and partition pruning apply in full. The trade-off: sub-second content is dropped, so a DateTime64 row cannot be re-fetched by equality with the microsecond value that was read from it — switch to microsecond precision when you need that.

Microsecond Precision: Comparisons

A whole-second value still becomes a plain literal; a value carrying microseconds is wrapped in toDateTime64(..., 6), which every ClickHouse version compares exactly against both DateTime and DateTime64 columns:

php

$query->where('created_at', '>', now())->get();
// where `created_at` > toDateTime64('2026-08-13 10:00:00.123456', 6)

Note: The toDateTime64() wrapper is safe against second-precision DateTime columns too. Comparison functions promote both sides to their common supertype (DateTime64) instead of converting the constant to the column type, so no ClickHouse version raises an error — the row values simply compare at full precision. Only the IN set element check is strict; see whereIn() below.

Equality against a sub-second value is exact in both directions: a DateTime64 row can be re-fetched with the very value that was read from it, and a second-precision DateTime row never wrongly matches a sub-second bound.

update() behaves the same way in both its SET values and its WHERE clause — the mutation casts the value to the column type, so a DateTime column truncates the sub-second part and a DateTime64 column keeps it.

Microsecond Precision: whereIn()

ClickHouse builds the IN set by converting each element to the column's type, and that conversion rejects sub-second values against a second-precision DateTime column on every ClickHouse version — as a toDateTime64() expression and as a fractional string alike. whereIn() values carrying microseconds therefore only work when the column is DateTime64; whole-second values work against both column types:

php

$query->whereIn('dt64', [$valueReadFromDt64])->get(); // exact match on every version

$query->whereIn('dt', [now()])->get();
// throws a QueryException (TYPE_MISMATCH) — pass whole-second values instead

Inserting Microseconds

At the default second precision, inserts store DateTimeInterface objects truncated to seconds in every input format — the safe choice, since ClickHouse 25.8 and older reject sub-second content when the target column is a second-precision DateTime, whether it arrives in a VALUES list or a JSONEachRow row. Every channel below keeps microseconds on all supported ClickHouse versions when the target column is DateTime64:

Channel	Usage
`'datetime_precision' => 'microsecond'`	Objects render as microsecond strings in both `Values` and `Format::JSONEachRow` inserts.
Model `$dateFormat`	`protected $dateFormat = 'Y-m-d H:i:s.u';` — date attributes are stringified with microseconds. Only for models whose date columns are all `DateTime64`.
Pre-formatted strings	`->insert(['dt64' => $carbon->format('Y-m-d H:i:s.u')])` — works in both `Values` and `Format::JSONEachRow` inserts; only objects are normalized.

Behavior Summary

For values carrying microseconds (whole-second values are plain 'Y-m-d H:i:s' literals in both modes):

Context	`second` (default)	`microsecond`
`where` comparisons / `whereBetween()`	truncated literal	`toDateTime64(..., 6)` — exact against both column types
`whereIn()`	truncated literal	`toDateTime64(..., 6)` — exact for `DateTime64`, rejected by the server for `DateTime`
`update()` SET / WHERE	truncated literal	`toDateTime64(..., 6)` — cast to the column type
`insert()` objects (default `Values`)	truncated literal	microsecond string — for `DateTime64` columns
`insert()` objects with `Format::JSONEachRow`	truncated string	microsecond string — for `DateTime64` columns
Pre-formatted strings / model `$dateFormat`	kept as written	kept as written

Eloquent date attributes have their own storage format; see Date Precision.

Unsupported Operations

The following methods throw a LogicException when called, as they are not supported by ClickHouse:

Method	Reason
`insertGetId()`	ClickHouse does not support insert get id
`upsert()`	ClickHouse does not support upsert
`lock()`	ClickHouse does not support locking
`useIndex()`	ClickHouse does not support specifying indexes
`forceIndex()`	ClickHouse does not support specifying indexes
`ignoreIndex()`	ClickHouse does not support specifying indexes

Query Builder ​

Basic Queries ​

Selecting Data ​

Where Clauses ​

Grouping, Ordering & Having ​

Limit & Offset ​

LIMIT BY ​

Aggregates ​

SAMPLE Clause ​

FINAL Clause ​

ARRAY JOIN ​

Basic Array Join ​

Multiple Columns ​

With Alias ​

With Array of Aliases ​

Subquery Array Join ​

Left Array Join ​

Common Table Expressions (WITH) ​

Scalar Value ​

Scalar Subquery ​

Raw Expression ​

Named Subquery (WITH ... AS) ​

Recursive CTE ​

ClickHouse-Specific Joins ​

Examples ​

Set Operations ​

UNION ​

INTERSECT ​

EXCEPT ​

Where Extensions ​

Empty / Not Empty ​

GLOBAL IN / GLOBAL NOT IN ​

ClickHouse Date Function Mapping ​

PREWHERE Clause ​

Basic PREWHERE ​

OR PREWHERE ​

Raw PREWHERE ​

PREWHERE IN / NOT IN ​

PREWHERE NULL / NOT NULL ​

Closures (grouped conditions) ​

Settings Clause ​

Insert, Update, Delete ​

Insert ​

Insert Using an Input Format ​

Update ​

Delete ​

ON CLUSTER ​

Truncate ​

DateTime and DateTime64 Values ​

The Default: Second Precision ​

Microsecond Precision: Comparisons ​

Microsecond Precision: whereIn() ​

Inserting Microseconds ​

Behavior Summary ​

Unsupported Operations ​