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

Model::find('col IN ()') with bind

Hello,

I have strange issue. I'll start from example:

$collection = Model::find(array(
                'id IN (:ids:)',
                'bind' => array('ids' => '15,17,18')
            ));
var_dump(count($collection));
int 1
$collection = Model::find(array(
                'id IN (15,17,18)'
            ));
var_dump(count($collection));
int 3
$collection = Model::find(array(
                'id IN (:ids:)'
                'bind' => array('ids' => array(15,17,18))
            ));
var_dump(count($collection));
Notice: Array to string conversion in...........
int 0

How to properly bind WHERE col IN () arguments?



98.9k
Accepted
answer
edited Sep '14

Check this:

https://forum.phalcon.io/discussion/2159/phql-binding-array-with-the-in-operator

You can't bind an array as a PDO parameter, making this:

$collection = Model::find(array(
                'id IN (:ids:)'
                'bind' => array('ids' => array(15,17,18))
));

Means this:

$collection = Model::find(array(
                'id IN ("15")'
));

You have to create a placeholder for each parameter:

$collection = Model::find(array(
                'id IN (?0, ?1, ?2)'
                'bind' => array(15,17,18)
));

Has phalcon plans to make easier to bind a lot of params (auto count arguments)? Binding idea is to prevent sql injection, but if i parse my array manualy, then I don't need to bind.



98.9k
edited Oct '15

No, we don't have plans to add it, but if someone wants to implement it in Phalcon 2 it would be great.

Update: it's supported in Phalcon 2.0.4: https://blog.phalcon.io/post/phalcon-2-0-4-released

edited Sep '14

So I created a function to make possible to pass arrays in bind.

class Model extends \Phalcon\Mvc\Model
{
    public static function find($parameters = NULL)
    {
        return parent::find(self::_prepareBinds($parameters));
    }

    /*
     * FROM:
     * 0 => string 'id IN (:ids:)' (length=13)
     * 'bind' => 
     *   array (size=1)
     *     'ids' => 
     *       array (size=4)
     *         0 => int 15
     *         1 => int 17
     *         2 => int 18
     *         3 => int 19
     * 'for_update' => boolean true
     *
     * TO:
     * 0 => &string 'id IN (?0, ?1, ?2, ?3)' (length=22)
     * 'bind' => 
     *   array (size=4)
     *     0 => int 15
     *     1 => int 17
     *     2 => int 18
     *     3 => int 19
     * 'for_update' => boolean true
     */
    private static function _prepareBinds($parameters = NULL)
    {
        // binding is allowed only in array
        if (!is_array($parameters))
            return $parameters;

        // getting conditions from 0 or conditions parameter
        if (!empty($parameters['conditions']))
            $conditions =& $parameters['conditions'];
        elseif (!empty($parameters[0]))
            $conditions =& $parameters[0];
        else
            $conditions = '';

        // finding largest already set placeholder to avoid conflicts
        if (preg_match('/.*\?(\d+)/', $conditions, $matches))
            $i = $matches[1] + 1;
        else
            $i = 0;

        /*
         * check if exists bind and replace all arrays to ?0 ?1 etc
         */
        if (!empty($parameters['bind']))
            foreach ($parameters['bind'] as $key => $binded)
                if (is_array($binded))
                {
                    $placeholders = array();
                    $binds = array();
                    foreach ($binded as $bind)
                    {
                        $placeholders[] = '?'.$i;
                        $parameters['bind'][$i] = $bind;
                        $i++;
                    }
                    unset($parameters['bind'][$key]);
                    $conditions = str_replace(':'.$key.':', implode(', ', $placeholders), $conditions);
                }
        return $parameters;
    }
}
class Entity extends Model
{

}
class EntityController extends \Phalcon\Mvc\Controller
{
    public function indexAction($checks)
    {
        $entityCollection = Entity::find(array(
                'id IN (:ids:) AND entity_id IN (?99, :entities:)',
                'bind' => array('ids' => array(15,17,18,19), 99 => 1, 'entities' => array(1,5,9,7)),
            ));
    }
}

Result:

array (size=3)
  0 => string 'id IN (:ids:) AND entity_id IN (?99, :entities:)' (length=48)
  'bind' => 
    array (size=3)
      'ids' => 
        array (size=4)
          0 => int 15
          1 => int 17
          2 => int 18
          3 => int 19
      99 => int 1
      'entities' => 
        array (size=4)
          0 => int 1
          1 => int 5
          2 => int 9
          3 => int 7
  'for_update' => boolean true

array (size=3)
  0 => &string 'id IN (?100, ?101, ?102, ?103) AND entity_id IN (?99, ?104, ?105, ?106, ?107)' (length=77)
  'bind' => 
    array (size=9)
      99 => int 1
      100 => int 15
      101 => int 17
      102 => int 18
      103 => int 19
      104 => int 1
      105 => int 5
      106 => int 9
      107 => int 7
  'for_update' => boolean true


3.0k

DestinyMKas: thanks for sharing this code, I was missing it very much!

Btw: what's the requirements for this code to be implemented in Phalcon core? Passing an array for a bind parameter cannot be valid, I guess never, so a solution such this to make it work, would be big help. And on the other hand, without it, it's quite cumbersome to pass an array for an IN operator...



347
edited Sep '14

The easier way is to use builder, that returns same objects as find():

$builder = new \Phalcon\Mvc\Model\Query\Builder();
return $builder
    ->from('App\Models\Model')
    ->inWhere('id', $ids)
    ->getQuery()->execute();


3.0k
edited Sep '14

PVGrad, you're right, it works by inWhere well. But I still think that the default binding could be extended with this functionality to be included in core. For IN operations (which is not so rare I think) would solve this array problem quite transparently (and because arrays are still unusable for direct parameter binding, it wouldn't raise problems for current SQL generations).

Be aware of the following behavior.

$collection = Model::query()->inWhere('id', array(1, 2, 3));
echo $collection->count();
// Returns: 3 (correct)

But:

$collection = Model::query()->inWhere('id', array(1, 2, 3))->limit(99);
echo $collection->count();
// Returns: 1 (wrong)

And:

$collection = Model::query()->inWhere('id', array(1, 2, 3))->andWhere('col = :val:', array('val' => '1'));
echo $collection->count();
// Returns: 1 (wrong again)

It seems, that as soon as you start using bound parameters in any part of your query, the inWhere() assignment gets messed up. My educated guess is: This is an bug while handling the binding of parameters, because both limit() and andWhere() are making use of it, as well as several other functions.

Are you using Phalcon 2.0.7?

Be aware of the following behavior.

$collection = Model::query()->inWhere('id', array(1, 2, 3));
echo $collection->count();
// Returns: 3 (correct)

But:

$collection = Model::query()->inWhere('id', array(1, 2, 3))->limit(99);
echo $collection->count();
// Returns: 1 (wrong)

And:

$collection = Model::query()->inWhere('id', array(1, 2, 3))->andWhere('col = :val:', array('val' => '1'));
echo $collection->count();
// Returns: 1 (wrong again)

It seems, that as soon as you start using bound parameters in any part of your query, the inWhere() assignment gets messed up. My educated guess is: This is an bug while handling the binding of parameters, because both limit() and andWhere() are making use of it, as well as several other functions.

edited Aug '15

Are you using Phalcon 2.0.7?

I havn't upgraded yet, but I guess this could still be useful for people using older versions.



712
edited Jan '16

This variant seems to work


find([ 'conditions'=>'id IN ('.implode(',',[1,2,3]).')' ]);

No, we don't have plans to add it, but if someone wants to implement it in Phalcon 2 it would be great.

This feature is natively supported by Phalcon in 2.0.4, https://blog.phalcon.io/post/phalcon-2-0-4-released



40.7k

Hi - what is the syntax for this now? I am trying to bind an array in a find:: and I keep getting "SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"

Code, $status is a model which has a relationship to MyStatus:

$result = $status->getMyStatus(array("conditions"=>"md in (:md:)","bind"=>array("md"=>array(0=>153,1=>199))));

Is it because I am not doing find:: but rather passing to a sub-model?

You can use it like this:

Status::find([
    'conditions' => 'md IN ({md:array})',
    'bind' => [
        'md' => [153, 199],
    ],
]);

Doesn't matter if you use it with find, findFirst or the queryBuilder.

Example can be find here, in the third code block.

Hi - what is the syntax for this now? I am trying to bind an array in a find:: and I keep getting "SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"

Code, $status is a model which has a relationship to MyStatus:

$result = $status->getMyStatus(array("conditions"=>"md in (:md:)","bind"=>array("md"=>array(0=>153,1=>199))));

Is it because I am not doing find:: but rather passing to a sub-model?



712
edited Jun '16

This feature is natively supported by Phalcon in 2.0.4, https://blog.phalcon.io/post/phalcon-2-0-4-released

Would you please provide right syntax example how to use it natively with find, not queryBuilder.

Please read my reply first.

This feature is natively supported by Phalcon in 2.0.4, https://blog.phalcon.io/post/phalcon-2-0-4-released

Would you please provide right syntax example how to use it natively with find, not queryBuilder.



40.7k

Here is how I did it:

$myarr = array (1,5,89,14);
$result = MyModel::find(array(
        "conditions"=>"column in ({myvar:array})",
        "bind"=>array("myvar"=>$myarr)
));

So it works pretty much the normal way except that you have to enclose the bind parameter in curly braces and specify that it's an array.

edited May '17

So simple solution:

$post_id_arr = array(10,15,20,22,30);

$arrStr = implode("\",\"",$post_id_arr);

$posts = MyModel::find(array("id IN (\"$arrStr\")"));

@Ali SadeghipourK, better use bim14 solution. Your example could lead to injection.