WITH statement missed

[sergebezborodov]

Hi there, thank you for the awesome library.

I found $withQueries is not working. The call to buildWithQueries in QueryBuilder::build looks missed. Is it expected behavior or a bug?

thank you

[sanchezzzhak]

hi, you can throw off the sample code, I will try to help you

[sanchezzzhak]

ClickHouse 20.9.3.45 with revision 54439, build id: 2A13FD5201226D52, PID 27247

<?php
// ...
     $command = (new Query())
            ->select(['count()', 'event_date'])
            ->from('stat')
            ->groupBy(['event_date'])
            ->limit(1)
            ->withTotals();

        $result = $command->all();

        var_dump($result,
            $command->getCountAll(),
            $command->createCommand()->getRawSql()
        );

results

array(1) {
  [0]=>
  array(2) {
    ["count()"]=>
    string(1) "1"
    ["event_date"]=>
    string(10) "2020-04-30"
  }
}
int(2)
string(90) "SELECT count(), event_date FROM default.stat GROUP BY event_date  WITH TOTALS  LIMIT 1"

[sergebezborodov]

I mean another WITH statement: https://clickhouse.com/docs/en/sql-reference/statements/select/with/

[sanchezzzhak]

added method withQuery

    $db = \Yii::$app->clickhouse;
    $query = new Query();
    // first argument scalar var or Query object
    $query->withQuery($db->quoteValue('2021-10-05'), 'date1'); 
    $query->select('*');
    $query->from('stat');
    $query->where('event_stat < date1');
    $query->all();

    var_dump($query->createCommand()->getRawSql());

/*
    WITH '2020-07-26' AS date1 SELECT * FROM stat WHERE event_stat < date1
*/

[sergebezborodov]

thank you very much!