Invalid aggregate function: none

[sqwk]

Using ReportMetric::AGGREGATE_NONE as per https://docs.octobercms.com/4.x/extend/dashboards/data-sources.html#registering-metrics produces the error Invalid aggregate function: none

Some background: I am trying to display durations (time between a start_time and an end_time) on a widget using the new DataSource interface. Since aliases cannot be summed the following does not work:

        $this->registerMetric(new ReportMetric(
            self::METRIC_ENTRY_DURATION,
            'entry_duration',
            'Recorded Entry Duration',
            ReportMetric::AGGREGATE_SUM
        ));

        (...)

        $reportQueryBuilder->onConfigureQuery(
            function(Builder $query, ReportDimension $dimension, array $metrics) {
                $query->addSelect([
                    Db::raw('TIMESTAMPDIFF(SECOND, start_time, end_time) / 3600 AS entry_duration')
                ]);
            }
        );

as that will add SUM(entry_duration) to the query.

Instead I figured one could skip the aggregate function and do the sum as part of the query manually:

        $this->registerMetric(new ReportMetric(
            self::METRIC_ENTRY_DURATION,
            'id',
            'Recorded Entry Duration',
            ReportMetric::AGGREGATE_NONE
        ));

        (...)

        $reportQueryBuilder->onConfigureQuery(
            function(Builder $query, ReportDimension $dimension, array $metrics) {
                $query->addSelect([
                    Db::raw('SUM(TIMESTAMPDIFF(SECOND, start_time, end_time) / 3600) AS oc_metric_entry_duration')
                ]);
            }
        );

however, that produces the error above.

[sqwk]

class TimeDataSource extends ReportDataSourceBase
{

    const METRIC_ENTRY_DURATION = 'entry_duration';

    public function __construct()
    {
        $this->registerDimension(new ReportDimension(
            ReportDimension::CODE_DATE,
            'CAST(end_time as DATE)',
            'Date'
        ));

        $this->registerMetric(new ReportMetric(
            self::METRIC_ENTRY_DURATION,
            'SUM(TIMESTAMPDIFF(SECOND, start_time, end_time) / 3600)',
            'Recorded Entry Duration (hours)',
            ReportMetric::AGGREGATE_NONE
        ));
    }

    protected function fetchData(ReportFetchData $data): ReportFetchDataResult
    {
        $reportQueryBuilder = new ReportDataQueryBuilder(
            'minakari_time_entries',
            $data->dimension,
            $data->metrics,
            $data->orderRule,
            $data->dimensionFilters,
            $data->limit,
            $data->paginationParams,
            $data->groupInterval,
            $data->hideEmptyDimensionValues,
            $data->dateStart,
            $data->dateEnd,
            $data->startTimestamp,
            null,
            'end_time',
            $data->totalsOnly
        );

        return $reportQueryBuilder->getFetchDataResult($data->metricsConfiguration);
    }
}

The above works, but only if one removes the method validateDbObjectName to allow direct non-column objects (return before the strlen/pregmatch check) and creates AGGREGATE_NONE as follows:

    private function aggregateFunctionToSql(string $function): string
    {
        switch ($function) {
              // ...
              case ReportMetric::AGGREGATE_NONE:
                return '%1$s';
                break;
              // ...
        }
    }

Having the AGGREGATE_NONE implemented would be nice. Having a built in time/duration mode that takes start_time and end_time fields would be nicer.

Somewhat related question; is it possible to group a dimension by another dimension, for example date by user_id?

[daftspunk]

I assume you're using PostgreSQL. Perhaps the column should be end_time::DATE and then validateDbObjectName can be improved to allow this?

Not sure regarding the user_id dimension, open to suggestions.