We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

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?



85.5k

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.phalcon.io/hr/3.3/db-layer#dialects



26.3k

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



26.3k

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



145.0k
Accepted
answer

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