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. Database

Parameter Binding in 'Raw' SQL for IN () Syntax, Bug?

Using a string variable like $lvls = '8,9,10' in a query does not work correctly when passed in as a parameter. In the following query, the number of returned rows is incorrect, unless I replace the :levels variable with a hard-coded string.

$sql = "SELECT ... FROM ... WHERE ... AND el.ed_level_id IN (:levels)";

$model = new Content();

$data = $model->getReadConnection()->query($sql, ['levels' => '8,9,10'])->fetchAll();

Is this a bug in Phalcon, or what I'm doing? Replacing :levels with 8,9,10 in the query above results in a different number of rows retrieved.

As a side note, the reason I'm using this kind of query is based on performance. There are joins on six tables, some of which are very large. The same query using the query builder was at least ten times slower.



145.0k
Accepted
answer
edited Jun '17

No, it's not a bug in phalcon. PDO doesn't suppor binding parameter as array. Only PHQL support binding array like this:

$result = Users::find([
    'id IN ({ids:array})',
    'bind' => [
        'ids' => [1,2,3,4,5]
    ]
]);

It was ten times slower because you were working most likely on full models with hydration - this is why it was slower, just work on arrays(toArray method) and select only data you need. I have many times many joins etc and it's fast in phalcon.



3.4k
in reply to Wojciech Ślawski
edited Jun '17

It was ten times slower because you were working most likely on full models with hydration - this is why it was slower, just work on arrays(toArray method) and select only data you need. I have many times many joins etc and it's fast in phalcon.

Are you speaking about this kind of implementation ?

$robots1 = $this->modelsManager->createBuilder()
    ->from('Robots')
    ->where('name = :name:')
    ->andWhere('type = :type:')
    ->getQuery()
    ->execute(array('name' => $name, 'type' => $type))
    ->setHydrateMode(Resultset::HYDRATE_ARRAYS)
    ->toArray();

this sample is from this post : https://forum.phalcon.io/discussion/1084/get-objects-array-instead-of-resultset-object-from-querybuilder-



145.0k

Yea exactly like this, also columns to select only what you need.



3.0k
edited Jun '17

Thanks for your help! I didn't see the answers sooner because I thought I would get an email notification. I wasn't aware of the setHydrateMode(). I guess the IN list issue is a case of being not supported, rather than a bug -- but I'm not sure there's a big difference in that distinction in this case.



145.0k
edited Jun '17

Yea not supported - but in pdo, there is just no such feature in PDO. If you want to use IN and array then it's only possible using PHQL(models).