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

Hello,

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?



80.4k

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

https://docs.phalconphp.com/hr/3.3/db-layer#dialects

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



134.1k
Accepted
answer

You need to add extension dialect. PHQL doesn't know what LEFT means. https://forum.phalconphp.com/discussion/16363/where-yearcurrenttimestamp-how-to-do-this#C48907 for example