I can not find the right way to make such kind of query using Model::find()? Can somebody give an advice?
Thanks.
|
Sep '16 |
9 |
6245 |
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 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
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
]
);