Solve the 'Missing columns' problem when laravel uses clickhouse query

The following column Laravel Tutorial will introduce to you the "DB::Exception: Missing columns" problem caused by using clickhouse query in laravel. I hope it will be helpful to everyone!

Use clickhouse Special attention: you cannot write like this!

   $where = [];
    if($cookieId) {
        $where['cookie_id'] = $cookieId;
    }        
    if($host) {
        $where['host'] = $host;
    }        
    if($uri) {
        $where['uri'] = $uri;
    }
    $builder = DB::connection('clickhouse')
        ->table((new AccessLogs)->getTable())
        ->where($where);
    if(!empty($startTime)) {
        $builder->where('create_time', '>=', $startTime);
    }
    if(!empty($endTime)) {
        $builder->where(&#39;create_time&#39;, &#39;<=&#39;, $endTime);
    }

When querying with multiple conditions, the $where array will be treated as a field in SQL, resulting in DB::Exception: Missing columns: '2022-09-27 13:00:49' '2022 -09-27 16:00:49' error while processing query.

Optimize like this:

   $builder = DB::connection('clickhouse')
        ->table((new AccessLogs)->getTable());
    if(!empty($cookieId)) {
        $builder->where('cookie_id', $cookieId);
    }        
    if(!empty($host)) {
        $builder->where('host', $host);
    }        
    if(!empty($uri)) {
        $builder->where('uri', $uri);
    }
    if(!empty($startTime)) {
        $builder->where('create_time', '>=', $startTime);
    }
    if(!empty($endTime)) {
        $builder->where(&#39;create_time&#39;, &#39;<=&#39;, $endTime);
    }

can query correctly.

One more thing to mention: when querying on the command line, use single quotes for parameter values. When using double quotes, the parameter value will also be treated as a field:

The correct operation is:

select * from access_log where create_time >= ‘2022-09-27 13:00:49’ and create_time <= ‘2022-09-27 16:00:49’ order by create_time desc limit 10;

The above is the detailed content of Solve the 'Missing columns' problem when laravel uses clickhouse query. For more information, please follow other related articles on the PHP Chinese website!