-
Notifications
You must be signed in to change notification settings - Fork 43
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
"with totals" and rows_before_limit_at_least implementation #10
Comments
done; $sql = 'SELECT
idsite,
sum(impressions) AS impressions
FROM stat
GROUP BY idsite
WITH TOTALS
LIMIT 10';
$command = $clickhouse->createCommand($sql);
$result = $command->queryAll();
var_dump($command->meta()); // columns meta info array (columnName, dataType)
var_dump($command->totals()); // result WITH TOTALS
var_dump($command->data()); // get rows data
var_dump($command->rows()); // rows count current result
var_dump($command->countAll()); // rows count before limit at least, easy pagination
var_dump($command->extremes()); // extremes values
var_dump($command->statistics()); // stat query or //...
$command = $clickhouse->createCommand($sql);
$result = $command->data();
$total = $command->countAll(); |
great job men!
public function rows()
{
$this->ensureQueryExecuted();
return $this->_rows;
}
public function ensureQueryExecuted()
{
if(true !== $this->_is_result) {
throw new DbException('Query was not executed yet');
}
} it's just my opinion we can discuss it:)
because currently, we cannot use query builder with "with totals" syntax at all. we must write raw sql :( |
$q = (new \kak\clickhouse\Query())->from('stat')
->withTotals()
->where(['event_date' => '2017-05-01' , 'user_id' => 5 ])
->offset(2)
->limit(1);
$command = $q->createCommand();
$result = $command->queryAll();
$total = $command->countAll();
var_dump($result); // result data
var_dump($total); // result WITH TOTALS
// -----
// all array result data meta totlas etc...
$result = (new \kak\clickhouse\Query())
->from('stat')
->withTotals()
->all();
var_dump($result); |
checked latest "fix; re" commit. you've refactored the code and I guess had broken Query::all() standard interface. This functions must return an array of rows. All developers expect this behaviour. one of the ways to fix it is add an additional parameter to withTotal($returnMetaData=false) but a better way is to keep withTotal() as it is. but create additional function withMetaData() (new Query()) (new Query()) in this case data will NOT be included to results array |
(new Query())
->withTotal()
->withMetaData()
->all() result all data current verson Array
(
[meta] => Array
(
[0] => Array
(
[name] => user_id
[type] => Int32
)
)
[data] => Array
(
[0] => Array
(
[user_id] => 2
)
[1] => Array
(
[user_id] => 2
)
[2] => Array
(
[user_id] => 2
)
[3] => Array
(
[user_id] => 2
)
[4] => Array
(
[user_id] => 2
)
)
[rows] => 5
[countAll] => 39
[totals] => Array
(
[user_id] => 0
)
[statistics] => Array
(
[elapsed] => 8.1887E-5
[rows_read] => 39
[bytes_read] => 156
)
[extremes] =>
) is use only withTotal (new Query())
->withTotal(false)
->all() result
So? |
About ->withTotal() and ->withMetaData() again. Some misunderstanding here, probably I elaborated my idea not enough. The current behavior is still incorrect in my opinion. adding ->withTotal() to a query must NOT change a result format. Currently, you must execute 2 queries: 1st to fetch rows, 2nd to get totals but only one query is really needed. for example. $db = Yii::$app->clickhouse;
$cmd = (new Query())
->select(['iddevice', new Expression('sum(impressions) as impressions')])
->from('stat')
->groupBy('iddevice');
$res = $cmd->all($db); it returns rows
query with totals $cmd = (new Query())
->select(['iddevice', new Expression('sum(impressions) as impressions')])
->from('stat')
->groupBy('iddevice')
->withTotals();
$res = $cmd->all($db); it must return the same results
the difference is only in generated SQL. in the 1st case sql-query was generated without "with totals" in the 2nd case sql contains "with totals". After 1st query $cmd->totals() should return null, but after 2nd query $cmd->totals() should return:
about "->withMetaData()"... maybe remove it at all. or rename somehow. |
fixed $cmd = (new Query())
->select(['iddevice, sum(impressions) as impressions'])
->from('stat')
->withTotals()
->groupBy('iddevice');
$result = $cmd->all();
echo "=== result\n";
var_dump($result);
echo "=== meta\n";
var_dump($cmd->meta());
echo "=== extremes\n";
var_dump($cmd->extremes());
echo "=== totals\n";
var_dump($cmd->totals());
echo "=== rows\n";
var_dump($cmd->rows());
echo "=== countAll\n";
var_dump($cmd->countAll());
|
almost perfect :) I guess we should rename functions to getMeta(), getTotals() & etc |
I agree it is necessary to rename.. |
Let's imagine you need to show some stats in a table. The last row of the table must contain Total values. The table must have pagination as well.
Clickhouse allows to fetch all needed data in one query. for example
as result you will get this json:
...
"totals":
{
"idsite": 0,
"impressions": "73886972"
},
"rows": 10,
"rows_before_limit_at_least": 226,
...
Consider to implement these very useful features.
The text was updated successfully, but these errors were encountered: