How to use Phalcon Model to build this simple SQL query? SELECT ... WHERE concat(....) = ...

Okay, I figured out how to create a vanilla PDO::statement in Phalcon.

$connection = \Phalcon\DI::getDefault()->get('db');
$statement = $connection->prepare('SELECT * FROM user WHERE concat(user_prefix,user_code) = :identification');
$statement->bindValue(':identification', $value, \PDO::PARAM_STR);
$statement->execute();
$result = $statement->fetch(\PDO::FETCH_ASSOC);

It gives me associative array as in vanilla PDO.

However, I like how ORM handles CRUD with the Model class.

Question #2: is there a easy way to construct a Phalcon\Mvc\Model from above statement?

Add on to question #2, I am looking at the document here:

https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql
If Raw SQL queries are common in your application a generic method could be added to your model:

But I can't figure out how to adapt the example code. Can anyone help?

Provided you're not using the Micro framework you can generally access the models manager service and execute a query like

$sql = 'SELECT * FROM user WHERE concat(user_prefix,user_code) = :identification:');
$getUser = $this->getDI()->get('modelsManager')->executeQuery($sql, ['identification' => $value])->getFirst();
if($getUser){
   //found - instance of model
} else {
   //not found
}

Just remember that when you're doing PHQL you reference mapped columns and model names, not literal SQL/NoSQL table names.

Thanks man! I just figured out about the same. PHQL is the only way to do it.

At first I tried to avoid PHQL because of the performance impact. The above statement took 100ms more than straight PDO. 100ms in total 600ms page load time is a lot. However, this is probably the necessary burden to use a framework.