SELECT ... WHERE id IN (1, 2, 3)

I can not find the right way to make such kind of query using Model::find()? Can somebody give an advice?

Thanks.

Last time I checked it wasn't there. I did it this way:

$ids = array(1, 2, 3); // list of fields to include into IN( ... )

$conditions = array_fill(0, sizeof($ids), '?'); // fill array with placeholders
array_walk($conditions, function(&$value, $key) { $value = '?' . $key; }); // alter values in array

$conditions = 'id IN (' . implode(', ', $conditions) . ')'; // create "conditions" string "id IN (?0, ?1, ?2)"

$robots = Robots::find(array(
    $conditions,
    "bind" => $ids
)); // search

I think should be more pragmatic way of doing it.

edited Dec '15

I found some unpleasant feature.

$ids = [1, 2, 3];

$userModel  = new User; 
$userModel->getReadConnection()->query('UPDATE users SET last_online = :online WHERE id IN (:ids)', [
    'online' => time(),
    'ids'    => implode(', ', $ids)
])->execute();

Generates SQL

UPDATE users SET last_mailed = '1431602381' WHERE id IN ('1, 2, 3');

But '1, 2, 3' incorrect sql statement. Expected '1', '2', '3' or just 1, 2, 3

PDO does not allow to escape arrays



7.0k
edited May '15

PHP function implode return string. Therefore you have issue.

Try

$userModel->getReadConnection()->query('UPDATE users SET last_online = :online WHERE id IN :ids', [
    'online' => time(),
    'ids'    => '(' .  implode(', ', $ids) . ')'
])->execute();

I used more readable:

$sql = strtr("UPDATE `users` SET `last_online` = ':online' WHERE `id` IN (':ids')", [
    ':online' => $lastOnline,
    ':ids'    => implode("', '", $ids)
]);

$userModel->getReadConnection()->query($sql)->execute();

I have the solution: ^w^

For use:

SELECT name, data FROM config WHERE collection = :collection AND name IN ( :names[] )

with* expandArguments($sql, $args)* function.

Example:

        $names = ['user.role.administrator', 'user.role.anonymous',"user.role.authenticated"];
        $sql = 'SELECT name, data FROM config WHERE collection = :collection AND name IN ( :names[] )';
        $args = [
            'collection' => '',
            'names[]' => $names
        ];

        expandArguments($sql, $args);

        $list = $connection->fetchAll(
            $sql,
            \Phalcon\Db::FETCH_ASSOC,
            $args
        );

The function:

function expandArguments(&$query, &$args)
{
    $modified = false;

    // If the placeholder indicated the value to use is an array,  we need to
    // expand it out into a comma-delimited set of placeholders.
    foreach ($args as $key => $data) {
        $is_bracket_placeholder = substr($key, -2) === '[]';
        $is_array_data = is_array($data);
        if ($is_bracket_placeholder && !$is_array_data) {
            throw new \InvalidArgumentException('Placeholders with a trailing [] can only be expanded with an array of values.');
        } elseif (!$is_bracket_placeholder) {
            if ($is_array_data) {
                throw new \InvalidArgumentException('Placeholders must have a trailing [] if they are to be expanded with an array of values.');
            }
            // Scalar placeholder - does not need to be expanded.
            continue;
        }
        // Handle expansion of arrays.
        $key_name = str_replace('[]', '__', $key);
        $new_keys = array();
        // We require placeholders to have trailing brackets if the developer
        // intends them to be expanded to an array to make the intent explicit.
        foreach (array_values($data) as $i => $value) {
            // This assumes that there are no other placeholders that use the same
            // name.  For example, if the array placeholder is defined as :example[]
            // and there is already an :example_2 placeholder, this will generate
            // a duplicate key.  We do not account for that as the calling code
            // is already broken if that happens.
            $new_keys[$key_name . $i] = $value;
        }

        // Update the query with the new placeholders.
        $query = str_replace($key, implode(', :', array_keys($new_keys)), $query);

        // Update the args array with the new placeholders.
        unset($args[$key]);
        $args += $new_keys;

        $modified = true;
    }

    return $modified;
}

I would like to have this feature in Phalcon apply the expandArguments(&$query, &$args) function when a query is made.

and to have something like this:

$list = $connection->fetchAll(
            'SELECT name, data FROM config WHERE collection = :collection AND name IN ( :names[] )',
            \Phalcon\Db::FETCH_ASSOC,
            [
                 'collection' => '',
                 'names[]' => $names
             ]
);