File data/src/Model/AggregateModel.php

1:	<?php
2:
3:	declare(strict_types=1);
4:
5:	namespace Atk4\Data\Model;
6:
7:	use Atk4\Data\Exception;
8:	use Atk4\Data\Field;
9:	use Atk4\Data\Field\SqlExpressionField;
10:	use Atk4\Data\Model;
11:	use Atk4\Data\Persistence;
12:	use Atk4\Data\Persistence\Sql\Expression;
13:	use Atk4\Data\Persistence\Sql\MaterializedField;
14:	use Atk4\Data\Persistence\Sql\Query;
15:
16:	/**
17:	* AggregateModel model allows you to query using "group by" clause on your existing model.
18:	* It's quite simple to set up.
19:	*
20:	* $aggregate = new AggregateModel($mymodel);
21:	* $aggregate->setGroupBy(['first', 'last'], [
22:	* 'salary' => ['expr' => 'sum([])', 'type' => 'atk4_money'],
23:	* ];
24:	*
25:	* your resulting model will have 3 fields: first, last, salary
26:	*
27:	* but when querying it will use the original model to calculate the query, then add grouping and aggregates.
28:	*
29:	* If you wish you can add more fields, which will be passed through:
30:	* $aggregate->addField('middle');
31:	*
32:	* If this field exist in the original model it will be added and you'll get exception otherwise. Finally you are
33:	* permitted to add expressions.
34:	*
35:	* @property Model $table
36:	*
37:	* @method Persistence\Sql getPersistence()
38:	* @method Expression expr(string $template, array<int\|string, mixed> $arguments = []) forwards to Persistence\Sql::expr using $this as model
39:	*/
40:	class AggregateModel extends Model
41:	{
42:	public const HOOK_INIT_AGGREGATE_SELECT_QUERY = self::class . '@initAggregateSelectQuery';
43:
44:	/** @var array<int, string\|Expression> */
45:	public $groupByFields = [];
46:
47:	/**
48:	* @param array<string, mixed> $defaults
49:	*/
50:	public function __construct(Model $baseModel, array $defaults = [])
51:	{
52:	if (!$baseModel->issetPersistence() && !$baseModel->getPersistence() instanceof Persistence\Sql) {
53:	throw new Exception('Base model must have Sql persistence to use grouping');
54:	}
55:
56:	$this->table = $baseModel;
57:
58:	// this model should always be read-only and does not have ID field
59:	$this->readOnly = true;
60:	$this->idField = false;
61:
62:	parent::__construct($baseModel->getPersistence(), $defaults);
63:	}
64:
65:	/**
66:	* Specify a single field or array of fields on which we will group model. Multiple calls are allowed.
67:	*
68:	* @param array<int, string\|Expression> $fields
69:	* @param array<string, array<mixed>\|object> $aggregateExpressions Array of aggregate expressions with alias as key
70:	*
71:	* @return $this
72:	*/
73:	public function setGroupBy(array $fields, array $aggregateExpressions = [])
74:	{
75:	$this->groupByFields = array_unique(array_merge($this->groupByFields, $fields));
76:
77:	foreach ($fields as $fieldName) {
78:	if ($fieldName instanceof Expression \|\| $this->hasField($fieldName)) {
79:	continue;
80:	}
81:
82:	$this->addField($fieldName);
83:	}
84:
85:	foreach ($aggregateExpressions as $name => $seed) {
86:	$exprArgs = [];
87:	// if field is defined in the parent model then it can be used in expression
88:	if ($this->table->hasField($name)) {
89:	$exprArgs = [$this->table->getField($name)];
90:	}
91:
92:	$seed['expr'] = $this->table->expr($seed['expr'], $exprArgs);
93:
94:	// convert base model fields to aliases, they are always already materialized as the base model is SQL inner table
95:	foreach ($seed['expr']->args['custom'] as $argK => $argV) {
96:	$seed['expr']->args['custom'][$argK] = new MaterializedField($this->table, $argV);
97:	}
98:
99:	$this->addExpression($name, $seed);
100:	}
101:
102:	return $this;
103:	}
104:
105:	#[\Override]
106:	public function addField(string $name, $seed = []): Field
107:	{
108:	if ($seed instanceof SqlExpressionField) {
109:	return parent::addField($name, $seed);
110:	}
111:
112:	if ($this->table->hasField($name)) {
113:	$innerField = $this->table->getField($name);
114:	$seed['type'] ??= $innerField->type;
115:	$seed['enum'] ??= $innerField->enum;
116:	$seed['values'] ??= $innerField->values;
117:	$seed['caption'] ??= $innerField->caption;
118:	$seed['ui'] ??= $innerField->ui;
119:	}
120:
121:	return parent::addField($name, $seed);
122:	}
123:
124:	#[\Override]
125:	public function action(string $mode, array $args = [])
126:	{
127:	switch ($mode) {
128:	case 'select':
129:	$fields = $args[0] ?? array_unique(array_merge(
130:	$this->onlyFields ?? array_keys($this->getFields()),
131:	array_filter($this->groupByFields, static fn ($v) => !$v instanceof Expression)
132:	));
133:
134:	$query = parent::action($mode, [[]]);
135:	if (isset($query->args['where'])) {
136:	$query->args['having'] = $query->args['where'];
137:	unset($query->args['where']);
138:	}
139:
140:	$this->getPersistence()->initQueryFields($this, $query, $fields);
141:	$this->initQueryGrouping($query);
142:
143:	$this->hook(self::HOOK_INIT_AGGREGATE_SELECT_QUERY, [$query]);
144:
145:	return $query;
146:	case 'count':
147:	$innerQuery = $this->action('select', [[]]);
148:	$innerQuery->reset('field')->field($this->expr('1'));
149:
150:	$query = $innerQuery->dsql()
151:	->field('count(*)', $args['alias'] ?? null)
152:	->table($this->expr('([]) {}', [$innerQuery, '_tc']));
153:
154:	return $query;
155:	case 'field':
156:	case 'fx':
157:	case 'fx0':
158:	return parent::action($mode, $args);
159:	default:
160:	throw (new Exception('AggregateModel model does not support this action'))
161:	->addMoreInfo('mode', $mode);
162:	}
163:	}
164:
165:	protected function initQueryGrouping(Query $query): void
166:	{
167:	foreach ($this->groupByFields as $field) {
168:	if ($field instanceof Expression) {
169:	$expression = $field;
170:	} else {
171:	$expression = new MaterializedField($this->table, $this->table->getField($field));
172:	}
173:
174:	$query->group($expression);
175:	}
176:	}
177:
178:	#[\Override]
179:	public function __debugInfo(): array
180:	{
181:	return array_merge(parent::__debugInfo(), [
182:	'groupByFields' => $this->groupByFields,
183:	]);
184:	}
185:	}
186:

Namespaces

Classes

Traits