.png)
2 weeks ago
3
Following this article “Dynamic KPIs: Fata Morgana? No, MaravelQL!” I’ll present now how to achieve faster Dynamic KPIs than a traditional database can offer by using a search engine (Elasticsearch with its SQL plugin) coupled with MaravelQL offered by the maravel-rest-wizard lib suite.
First you need to setup the Elasticsearch, create an index with its mappings and populate it.
There are several ways to do it:
- an old plugin for very simple and happy-flow usage
- the official PHP client for Elasticsearch
- you can manually implement a stricter solution if you like, by connecting directly to your database and ingest data into the index.
Assuming you have installed maravel-rest-wizard which requires Maravel/Maravelith version ≥ 10 or Lumen/Laravel version ≥ 8, you can use it the same you would when using MySql or PostgreSql databases with the observation that Elasticsearch does not have relations so, this is the situation of a model/resource without relations.
That means that you can do automatic aggregations and filtering by using MaravelQL without coding a single line of eloquent/query builder logic for this specific model.
Set in your env file:
ELASTICSEARCH_PASSWORD=ELASTICSEARCH_URL=
ELASTICSEARCH_USER=
ELASTICSEARCH_TIMEOUT=60
Required model:
<?phpnamespace App\Models;
use MacropaySolutions\MaravelRestWizard\Models\BaseModel;
class ESExampleIndex extends BaseModel
{
public const RESOURCE_NAME = 'es-examples-index';
protected bool $indexRequiredOnFiltering = false; // very important
protected bool $postfixSubtotals = true; // very important
public const CREATED_AT = 'createdAt';
public const UPDATED_AT = 'updatedAt';
public const SUMMABLE_COLUMNS = [
'number',
];
public const MIN_MAX_ABLE_ADDITIONAL_COLUMNS = [
'createdAt',
'updatedAt',
];
protected $table = 'es_examples_index';
protected $fillable = [
'id',
'number',
'field',
'anotherField',
'createdAt',
'updatedAt',
];
public static function boot(): void
{
parent::boot();
static::creating(function (BaseModel $baseModel): void {
throw new \Exception('Forbidden');
});
static::updating(function (BaseModel $baseModel): void {
throw new \Exception('Forbidden');
});
static::deleting(function (BaseModel $baseModel): void {
throw new \Exception('Forbidden');
});
}
public function isMysqlBelow8(): bool
{
return false;
}
public function isMysqlOver8(): bool
{
return false;
}
public function getColumnsClosures(): array
{
$dateTimeCallback = fn(mixed $v): string => \str_replace(' ', 'T', \trim((string)$v));
return $this->columnsClosures = [
'createdAt' => $dateTimeCallback,
'updatedAt' => $dateTimeCallback,
];
}
}
The getColumnsClosures method is used to tap into the query builder logic to alter the value of the filter just before it is used. In this case it adds the T (if missing) to the datetime fields because Elasticsearch throws error on Y-m-d H:i:s filters, its valid working format being Y-m-d\TH:i:s (example 2025–10–17T00:00:00.000Z value can be filtered by 2025–10–17T00:00:00 and not by 2025–10–17 00:00:00).
The service would look exactly the same as the one for the relational database:
<?phpnamespace App\Services;
use App\Models\ESExampleIndex;
use MacropaySolutions\MaravelRestWizard\Models\BaseModel;
use MacropaySolutions\MaravelRestWizard\Services\BaseResourceService;
class ESExamplesIndexService extends BaseResourceService
{
protected function setBaseModel(): void
{
$this->model = \app(ESExampleIndex::class);
}
}
The controller needs to use the ElasticSearchResourceControllerTrait instead of the ResourceControllerTrait:
<?phpnamespace App\Http\Controllers\ElasticSearch;
use App\Services\ESExamplesIndexService;
use MacropaySolutions\MaravelRestWizard\Http\Controllers\ElasticSearchResourceControllerTrait;
class ESExamplesIndexController extends Controller
{
use ElasticSearchResourceControllerTrait;
public function __construct()
{
$this->init(2000);
}
protected function setResourceService(): void
{
$this->resourceService = \app(ESExamplesIndexService::class);
}
protected function setModelFqnToControllerMap(): void
{
$this->modelFqnToControllerMap = DbCrudMap::ES_MODEL_FQN_TO_CONTROLLER_MAP;
}
}
This will extract the auto generated SQL from the Eloquent Builder returned by the service and call Elasticsearch instead of executing the query in DB.
The response will look like this:
{"columns": [
{
"name": "id",
"type": "long"
},
{
"name": "number",
"type": "long"
},
{
"name": "field",
"type": "text"
},
{
"name": "anotherField",
"type": "text"
},
{
"name": "updatedAt",
"type": "datetime"
},
{
"name": "createdAt",
"type": "datetime"
}
],
"rows": [
[
1,
1,
"...",
"...",
"2025-10-17T10:19:05.000Z",
"2025-03-17T10:20:31.000Z"
]
],
"cursor": "...",
"total_rows": 3
}
// Json Response with 'cursor' set in request for next page:
{
"rows": [
[
2,
5,
"...",
"...",
"2025-10-17T10:19:05.000Z",
"2025-03-17T10:20:31.000Z"
]
],
"cursor": "..."
}
Only cursor pagination is available in Elasticsearch.
simplePaginate=1 can be used on first call if the total_rows is not needed.
limit = 0 can be used for count only.
{"total_rows": 3
}
distincts[]=id&distincts[]=number can be used to restrict the response data. Note that distinct is not available in Elasticsearch and in this scenario it is used as select only. The cardinality from Elasticsearch is not equivalent to distinct.
{"columns": [
{
"name": "id",
"type": "long"
},
{
"name": "number",
"type": "long"
}
],
"rows": [
[
1,
1
]
],
"cursor": "...",
"total_rows": 3
}
// Json Response with 'cursor' set in request for next page:
{
"rows": [
[
2,
5
]
],
"cursor": "..."
}
Accept: text/csv header will return a csv file.
XLS download is not available for Elasticsearch like it is for Sql databases.
Final notes:
By using this approach you avoid timeouts that a traditional database generates on big data volumes no matter how they are indexed.
The response times depend on your server configuration and size of the Elasticsearch index.
You can filter or aggregate by any column.
