/**
* Create the summary columns with aggregate calls in select clause and group aggregates query using $groupByKey.
*
* @param string $groupByKey
* @return $this
*/
public function summaryGroupBy($groupByKey)
{
// ReportData is in summary, so set $isDetailed to false
$this->isDetailed = false;
// Get the modelGridQueries starting from an mgq which it has the groupByKey column.
$mgqs = array_start_from($this->gridQuery->getModelGridQueries(), function ($mgq) use($groupByKey) {
return !is_null($mgq->{$groupByKey});
});
// Then add the columns of those mgqs in select clause.
foreach ($mgqs as $mgq) {
// Do not addSelect() if mgq has a static columnsToAddSelect method, since it will be added later.
if (!method_exists($mgq, 'columnsToAddSelect')) {
$this->query->addSelect($mgq->makeSelect());
}
}
/*
* Start using the defaultGroupByKey() method.
* Use the defaultGroupByKey if gridQuery has.
*/
$defaultGroupByKeys = $this->gridQuery->defaultGroupByKey();
// Convert to array for those single groupByKeys
if (!is_array($defaultGroupByKeys)) {
$defaultGroupByKeys = [$defaultGroupByKeys];
}
foreach ($defaultGroupByKeys as $defaultGroupByKey => $groupByColumn) {
// Use the groupByColumn string as the defaultGroupByColumnKey
$defaultGroupByKey = is_int($defaultGroupByKey) ? $groupByColumn : $defaultGroupByKey;
$this->query->addSelect(DB::raw("{$groupByColumn} as {$defaultGroupByKey}"));
// Query group by $defautlGroupByKey
$this->query->groupBy($defaultGroupByKey);
}
// Finally, use the groupByKey to group query for the summary report
$this->query->groupBy($groupByKey);
return $this;
}
protected function checkQueryGroupBy()
{
$groups = $this->query->getQuery()->groups;
$keyGroup = $this->model->getQualifiedKeyName();
if (empty($groups) || !in_array($keyGroup, $groups)) {
$this->query->groupBy($keyGroup);
}
}
public function apply(Query $query, BaseRepository $repository)
{
$meta = false;
if (isset($this->filter['meta'])) {
$meta = $this->filter['meta'];
unset($this->filter['meta']);
}
if (!empty($this->filter)) {
$query->where($this->filter);
}
if (!empty($meta)) {
$table = $query->getModel()->getTable();
$query->join('meta', $table . '.id', '=', 'meta.object_id');
$metaQuery = $query->getQuery()->newQuery();
foreach ($meta as $k => $v) {
$metaQuery->orWhere(['meta.key' => $k, 'meta.value' => $v]);
}
$query->addNestedWhereQuery($metaQuery);
$query->groupBy($table . '.id');
$results = $query->get();
}
}
/**
* Makes the query not repeat the results.
*
* @param \Illuminate\Database\Eloquent\Builder $query
*/
protected function makeGroupBy(Builder $query)
{
$driver = $this->getDatabaseDriver();
if ($driver == 'sqlsrv') {
$columns = $this->getTableColumns();
} else {
$id = $this->getTable() . '.' . $this->primaryKey;
$joins = array_keys($this->getJoins());
foreach ($this->getColumns() as $column => $relevance) {
array_map(function ($join) use($column, $query) {
if (Str::contains($column, $join)) {
$query->groupBy("{$column}");
}
}, $joins);
}
}
$query->groupBy($id);
}
/**
* @param \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder $query
* @return void
*/
public function applyQuery($query)
{
$query->selectRaw($this->getSQLSelect($query->from) . ' as __interval__');
$query->groupBy('__interval__');
}
/**
* @param $key
* @param $value
* @return static
*/
public function groupBy($key)
{
return parent::groupBy($key);
}
/**
* Makes the query not repeat the results.
*
* @param \Illuminate\Database\Eloquent\Builder $query
*/
protected function makeGroupBy(Builder $query)
{
if ($groupBy = $this->getGroupBy()) {
$query->groupBy($groupBy);
} else {
$driver = $this->getDatabaseDriver();
if ($driver == 'sqlsrv') {
$columns = $this->getTableColumns();
} else {
$columns = $this->getTable() . '.' . $this->primaryKey;
}
$query->groupBy($columns);
$joins = array_keys($this->getJoins());
foreach ($this->getColumns() as $column => $relevance) {
array_map(function ($join) use($column, $query) {
if (Str::contains($column, $join)) {
if (Str::startsWith($column, DB::getTablePrefix())) {
$column = substr($column, strlen(DB::getTablePrefix()));
}
$query->groupBy($column);
}
}, $joins);
}
}
}
/**
* Makes the query not repeat the results.
*
* @param \Illuminate\Database\Eloquent\Builder $query
*/
protected function makeGroupBy(Builder $query)
{
if ($groupBy = $this->getGroupBy()) {
$query->groupBy($groupBy);
return $query;
}
$columns = $this->getTable() . '.' . $this->primaryKey;
$query->groupBy($columns);
$joins = array_keys($this->getJoins());
foreach (array_keys($this->getColumns()) as $column) {
array_map(function ($join) use($column, $query) {
if (str_contains($column, $join)) {
$query->groupBy($column);
}
}, $joins);
}
}
