Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Mysql string function on simple query


I have a database of actors and I want to make a simple paginator of them by first letter. So I want to get all possible first letters from database. SQL looks this:

SELECT LEFT(name, 1) FROM `actor` GROUP BY LEFT(name, 1) ORDER BY LEFT(name, 1)

But when I try to do:

$letterCollection = Actor::find([
    'columns' => 'LEFT(name, 1)',
    'group' => 'LEFT(name, 1)',
    'order' => 'LEFT(name, 1)',

I get:

Fatal error: Uncaught Phalcon\Mvc\Model\Exception: Syntax error, unexpected token LEFT, near to '(name, 1) FROM [App\common\models\Actor] GROUP BY [LEFT(name], [1)] ORDER BY LEFT(name, 1)', when parsing: SELECT LEFT(name, 1) FROM [App\common\models\Actor] GROUP BY [LEFT(name], [1)] ORDER BY LEFT(name, 1) (101)

Any ideas?


i am no expert with dialects, but as any other ORM you need a dialect, cuz ORM doesn't have this function immplemented

I think I need to escape native mysql function. Do you know how?

I tried several ways to escape by doing this:

$letterCollection = Actor::find([
            'columns' => '[LEFT(name, 1)]',
            'group' => '[LEFT(name, 1)]',
            'order' => '[LEFT(name, 1)]'

But it didn't helped


You need to add extension dialect. PHQL doesn't know what LEFT means. for example