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

inconsistent binding in PDO

Hi, I've got serious problem in binding with Phalcon PDO and I'm shocked that noone accually called it a problem yet (or I can't find any topic about it). The problem is inconsistence in binding values. Consider those:

// Will return user with id = 1
User::findFirst([
    'conditions' => 'id = :id:',
    'bind' => ['id' => 1]
]);
// Will produce error "Unknown opcode 58"
User::findFirst([
    'conditions' => 'id = :id',
    'bind' => ['id' => 1]
]);

but when using raw query I have to do it opposite way:

// $this->db is an Phalcon\Db\Adapter\Pdo\Postgresql object
// Will return user with id = 1
$this->db->query("SELECT * FROM user WHERE id = :id", ['id' => 1])->fetchAll());
// Will produce syntax error at or near ":" LINE 1: SELECT * FROM user WHERE id = $1:
$this->db->query("SELECT * FROM user WHERE id = :id:", ['id' => 1])->fetchAll());

I should just know when to use single colon and then double or there is a story behind this strange logic?



77.7k
Accepted
answer

That was a conscious design choice, afaik.

Raw SQL is not the same as PHQL. PHQL will be transpiled to the proper SQL dialect set up by the dev. Considering that, it perfectly makes sense to set PHQL apart from raw queries.

So yes, it's as simple as knowing whether you're writing raw SQL or PHQL.

Indexed parameters differ aswell, not just named ones: Raw SQL will use a single ? to denote an indexed parameter, while PHQL uses explicitly indexed ?0 parameters. The reason for this is that it's easier to use the query builder this way.