cphalcon: [2.1.x][Bug] PHQL subquery cant bind params for it- only for "array" type

Should be resolved as of #13480

Thank you again Cameron

This is still an issue.

@sergeyklay Please add this to the 2.1 milestone as this is very serious for those affected by it and its a regression of relied upon behaviour.

This is affecting me as well. I’m dead in the water.

CREATE TABLE `hats` (
  `id` int(11) NOT NULL,
  `name` varchar(25) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `hats` (`id`, `name`) VALUES
(1, 'sombrero'),
(2, 'fedora'),
(3, 'tophat'),
(4, 'baseball'),
(5, 'panama');

ALTER TABLE `hats`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `hats`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

CREATE TABLE `colors` (
  `id` int(11) NOT NULL,
  `name` varchar(25) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `colors` (`id`, `name`) VALUES
(1, 'green'),
(2, 'blue'),
(3, 'red'),
(4, 'black'),
(5, 'white');

ALTER TABLE `colors`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `colors`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

CREATE TABLE `hats_colors` (
  `id` int(11) NOT NULL,
  `hats_id` int(11) NOT NULL,
  `colors_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `hats_colors` (`id`, `hats_id`, `colors_id`) VALUES
(1, 1, 3),
(2, 1, 4),
(3, 2, 4),
(4, 3, 5),
(5, 4, 2);

ALTER TABLE `hats_colors`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `hats_colors`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

<?php

class Hats extends Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->hasManyToMany(
            "id",
            "HatsColors",
            "hats_id",
            "colors_id",
            "Colors",
            "id",
            [
                'alias' => 'colors'
            ]
        );
    }
}

<?php

class Colors extends Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->hasManyToMany(
            "id",
            "HatsColors",
            "colors_id",
            "hats_id",
            "Hats",
            "id",
            [
                'alias' => 'hats'
            ]
        );
    }
}

<?php

class HatsColors extends Phalcon\Mvc\Model
{
    public function initialize()
    {
    }
}

        $phql = "SELECT Hats.* FROM Hats JOIN Colors WHERE Colors.name IN ({colors:array})";
        $query = new Phalcon\Mvc\Model\Query($phql, $this->getDI());
        $this->response->appendContent('<p>' . $query->getSql()['sql'] . '</p>');

        $hats = $query->execute([
            'colors' => ['red', 'green', 'black']
        ]);

Bind value is required for array type placeholder: colors

edit UPDATE: I’ve been able to get this to work by binding the variables first before the execute:

        $query->setBindParams([
            'colors' => ['red', 'green', 'black']
        ]);
        $hats = $query->execute();

Hi there and thank you for comment. I check now, so:

• phpinfo : Phalcon version is : 2.1.0r on PHP 5.6.3.
• ArticleMultimedia::find() - work with all cases (test situations):

        $check = \Models\Multimedia::find([
            'conditions' => 'type={type:str} AND (subtype IN ({subtype:array})) AND parent_id IN (SELECT \Models\Articles.id FROM \Models\Articles WHERE \Models\Articles.id IN ({articles:array}))',
            'bind' => [
                'type' => 'Models\Articles',
                'subtype' => ['youtube', 'video'],
                'articles' => [1,2,3,4,5,6,7]
            ]
        ]);

Still next code did not work - get exception for a missing bind value:

        $prepare = [
          'articles' => [1, 2, 3, 4, 5, 6, 10, 11]
        ];

        $phql = "SELECT * FROM Models\Multimedia WHERE parent_id IN (
          SELECT Models\Articles.id FROM Models\Articles WHERE Models\Articles.id IN ({articles:array})
        )";

        $query = $this->modelsManager->createQuery($phql);
        $elements = $query->execute($prepare);

And that is it 😕 Thank you. Wish u luck!