Read results

The examples use $query = DB::connection('clickhouse')->table('my_table').

Rows

get() returns a Laravel collection of the rows. Each row is a stdClass object, as on Laravel's other connections:

$rows = $query->select('id', 'field_one')->orderBy('id')->get();
// select "id", "field_one" from "my_table" order by "id" asc
// collect([(object) ['id' => 1, 'field_one' => 'a'], (object) ['id' => 2, 'field_one' => 'b'], ...])

$rows[0]->field_one; // 'a'

• Each value has the PHP type of its ClickHouse type, on every ClickHouse version:

  • an integer is an int, and a numeric string above PHP_INT_MAX, as a UInt64 can be;
  • a Float32 or Float64 is a float, also a whole number, and NaN and the infinities are NAN, INF and -INF;
  • a Decimal is a string with every digit and the scale of the column, such as '1.50';
  • an Array, a Map or a Tuple is a PHP array whose values follow the same rules: $row->tags[0].

  See Static analysis for every type.

• To read the rows as associative arrays, with the values of the JSON result, use getStatement()->rows(). See Other formats.

• The Eloquent builder of a model returns models. See Eloquent models.

Static analysis

The rows have the types that Laravel declares, so static analysis tools, such as PHPStan and Larastan, read them correctly: get() returns Collection<int, stdClass>, and first() returns stdClass|null. On a model query, toBase() and getQuery() return QueryBuilder.

The PHPStan extension of the package gives DB::connection('clickhouse') and Schema::connection('clickhouse') the types of the package, and reads the migrations and the schema dumps of ClickHouse connections with the types of the values. See Static analysis.

First row and single values

$query->orderBy('created_at', 'desc')->first();
// select * from "my_table" order by "created_at" desc limit 1
// (object) ['id' => 3, 'field_one' => 'c', ...], or null without a row

$query->where('id', 2)->value('field_one');
// select "field_one" from "my_table" where "id" = 2 limit 1
// 'b'

$query->orderBy('id')->pluck('field_one', 'id');
// select "field_one", "id" from "my_table" order by "id" asc
// collect([1 => 'a', 2 => 'b', 3 => 'c'])

• find($id) reads the row whose id is $id. sole() throws when the query returns no row or more than one.
• first(), value(), find() and sole() add a LIMIT to the builder itself, as in Laravel. Start a new query for a mutation after them.
• implode('field_one', ',') joins the values of a column.

pluck() and the names of the columns

ClickHouse names a column of a joined table with its table, such as users.id, when the query's own table has a column of the same name. Other columns keep their name, such as name. pluck() reads a column by the name that ClickHouse gives it:

$query->select('my_table.id', 'users.name')
    ->anyLeftJoin('users', 'my_table.user_id', '=', 'users.id')
    ->pluck('users.name', 'my_table.id');
// collect([1 => 'Ada', 2 => 'Bob', 3 => 'Ada'])

pluck('field_one as name') and pluck(DB::raw('field_two + 1 as next')) read the alias.

Check if rows exist

$query->where('id', 3)->exists();       // true
$query->where('id', 99)->doesntExist(); // true
// select exists(select * from "my_table" where "id" = 3) as "exists"

• A query that holds INTERSECT or EXCEPT gets where not ignore(*), so that ClickHouse compares all columns. See INTERSECT and EXCEPT in sub-queries.
• A query that calls arrayJoin(), in selectRaw(), withAlias() or an order, is selected from, also in whereExists(): select exists(select * from (...) as "exists_table" where not ignore(*)) as "exists". With the analyzer, ClickHouse 26.3 and 26.8 answer true for exists(select arrayJoin(arr) ...) over an empty array.
• exists() reads the only column of the result, whatever its name. Without the analyzer (enable_analyzer = 0), ClickHouse names it in(1, _subquery1) for a query with a withExpression() or a join.

Count and aggregates

$query->where('field_two', '>', 0)->count();
// select count(*) as "aggregate" from "my_table" where "field_two" > 0
// 2

$query->where('field_two', '>', 0)->max('field_two');
// select max("field_two") as "aggregate" from "my_table" where "field_two" > 0

$query->sum(DB::raw('field_two * id'));
// select sum(field_two * id) as "aggregate" from "my_table"

$query->aggregate('quantile(0.5)', ['field_two']);
// select quantile(0.5)("field_two") as "aggregate" from "my_table"

• count() returns an int on all ClickHouse versions.
• Without rows, sum() returns 0 and avg() returns null. min() and max() return the default of the column type, such as 0 or ''.
• aggregate($function, $columns) takes an aggregate function, such as uniqExact, or a parametric function, such as quantile(0.5), quantiles(0.5, 0.9) or topK(2). The function is written as given. Do not take it from user input.
• average() is the same as avg().

WARNING

The aggregates quote a string column as a column name. sum('field_two * id') fails with UNKNOWN_IDENTIFIER. Give SQL with DB::raw().

Which rows count() counts

count() and the total of paginate() count the rows that get() returns. A query whose select list, order or grouping changes these rows is counted in a sub-query, without its LIMIT and OFFSET:

$query->select('user_id')->groupBy('user_id')->count();
// select count(*) as "aggregate" from (select "user_id" from "my_table" group by "user_id") as "aggregate_table"

$query->select('id as x')->where('x', 3)->count();
// select count(*) as "aggregate" from (select "id" as "x" from "my_table" where "x" = 3) as "aggregate_table"

These queries are counted in a sub-query:

• groupBy(), distinct() without columns, groupLimit(), limitBy(), arrayJoin() and withAlias();
• a select alias or expression, such as selectRaw('arrayJoin(tags) as tag');
• an order that defines an alias, calls arrayJoin(), adds rows with WITH FILL, or limits the rows with a raw LIMIT, LIMIT ... BY, OFFSET or FETCH.

Other notes:

• A grouped query without a select list is counted by its groups: groupBy('user_id')->havingRaw('count() > 1')->count() sends select count(*) as "aggregate" from (select 1 from "my_table" group by "user_id" having count() > 1) as "aggregate_table".
• distinct('user_id')->count() sends Laravel's count(distinct "user_id").
• A union and a query with having() are counted as Laravel counts them: select count(*) as "aggregate" from (...) as "temp_table".
• The settings of timeout(), forceIndex() and ignoreIndex() go on the outer query.

Other aggregates

count($column), sum(), avg(), min(), max() and aggregate() are Laravel's: they remove the select list, and the order of a query without groupBy(). A union keeps the order of its first query, because the order decides, with its limit, which rows that query returns. To aggregate the rows of a grouped query or of a select list, aggregate a sub-query:

DB::connection('clickhouse')->query()
    ->fromSub($query->select('user_id', DB::raw('max(field_two) as m'))->groupBy('user_id'), 'q')
    ->sum('m');
// select sum("m") as "aggregate" from (select "user_id", max(field_two) as m from "my_table" group by "user_id") as "q"

Pagination

$query->orderBy('id')->paginate(15);
// select count(*) as "aggregate" from "my_table"
// select * from "my_table" order by "id" asc limit 15 offset 0

• paginate() gets the total from a count. See Which rows count() counts.
• simplePaginate() reads one row more than a page and does not count.
• Order the query. Without ORDER BY, ClickHouse returns the rows in no fixed order, and pages can overlap.
• paginate(), simplePaginate(), chunk() and first() add a LIMIT after the ORDER BY. ClickHouse refuses it after a raw LIMIT, OFFSET or FETCH in orderByRaw(). Read such a query as a sub-query: DB::connection('clickhouse')->query()->fromSub($query, 'q')->orderBy('id')->paginate().

Chunks

$query->select('id')->orderBy('id')->chunk(2, function (Collection $rows, int $page) {
    foreach ($rows as $row) {
        echo $row->id . "\n";
    }
});
// select "id" from "my_table" order by "id" asc limit 2 offset 0
// select "id" from "my_table" order by "id" asc limit 2 offset 2

• chunk() needs an order, as in Laravel: without one, it throws You must specify an orderBy clause when using this function.
• The callback gets a collection of the rows of a page and the page number, which starts at 1. It stops when the callback returns false.
• The limit() and offset() of the query limit the rows.
• lazy() and lazyById() read in pages too.

Read rows one by one

cursor() reads all rows of the query with one request, and yields them one at a time:

foreach ($query->select('id')->orderBy('id')->cursor() as $row) {
    // (object) ['id' => 1], then (object) ['id' => 2], ...
}

It uses the cursor() of the connection. Its memory stays flat.

Other formats

get() and the other methods of this page read the rows of a JSON result. getStatement($format = null) runs the query with a FORMAT clause, and returns the smi2 Statement. Its rawData() is the output:

$query->select('id', 'field_one')->orderBy('id')->getStatement('CSVWithNames')->rawData();
// select "id", "field_one" from "my_table" order by "id" asc format CSVWithNames
// "\"id\",\"field_one\"\n1,\"a\"\n2,\"b\"\n3,\"c\"\n"

$statement = $query->select('user_id', DB::raw('count() as c'))->groupByRaw('user_id with totals')->getStatement();
$statement->rows();   // [['user_id' => 7, 'c' => '2'], ['user_id' => 8, 'c' => '1']], associative arrays
$statement->totals(); // ['user_id' => 0, 'c' => '3']

• getStatement() takes a case of Oralunal\LaravelClickHouse\Enums\Format, such as Format::Csv, or the name of a ClickHouse output format, such as 'TSVRaw'. The name of a case is matched in any letter case: 'jsoneachrow' gives format JSONEachRow. A name with other characters than letters and digits throws an InvalidArgumentException.
• Without a format, the result is JSON. rows(), totals(), extremes() and statistics() read it.
• A ClickHouse error throws when the query runs. The query is logged as get() logs it.
• The Eloquent builder of a model has getStatement() too. It applies the global scopes of the model.
• For raw SQL with a FORMAT clause, use the connection's selectStatement().