Run custom query within View template

Need Help
1

I would like to run a query containg “SELECT ST_AsGeoJSON(linestring)”, and I think I can’t use the fundamentals of CakePHP for this (e.g. like ->findFirst() ).
Is it still possible to run a query from a view template? Since $this is an AppView, not having the query as a helper?

2

You shouldn’t ever be running any query from within a view template. What do you mean by “having the query as a helper”? That doesn’t sound right at all. Queries (custom or otherwise) should be run in the controller, with the results set as a variable that the view can display.

3

As @Zuluru said, you should execute the query in the model/controller layer, and pass the results to the view.

You have to use custom sql functions

$query->select([
    'linestring' => $query->func()->ST_AsGeoJSON([
        'Users.linestring' => 'identifier',
    ]),
]);

If you are use complicated SQL without user data, you can use Raw Expressions

$query->select([
    'linestring' =>  $query->newExpr()->add('ST_AsGeoJSON(linestring)'),
    // Or...
    'linestring' =>  $query->newExpr('ST_AsGeoJSON(linestring)'),
]);