Doctrine - 如何将数组绑定到SQL？

My SQL looks something like this:

我的SQL看起来像这样：

$sql = "select * from user where id in (:userId) and status = :status";

$em = $this->getEntityManager();
$stmt = $em->getConnection()->prepare($sql);
$stmt->bindValue(':userId', $accounts, \Doctrine\DBAL\Connection::PARAM_INT_ARRAY);
$stmt->bindValue(':status', 'declined');
$stmt->execute();

$result = $stmt->fetchAll();

But it returns:

但它返回：

An exception occurred while executing (...)

执行（...）时发生异常

with params [[1,2,3,4,5,6,7,8,11,12,13,14], "declined"]

与params [[1,2,3,4,5,6,7,8,11,12,13,14]，“拒绝”]

Notice: Array to string conversion

注意：数组到字符串转换

I cannot user queryBuilder because my real SQL is more complicated (ex. contains joined select, unions and so on)

我不能使用queryBuilder，因为我的真实SQL更复杂（例如包含连接的选择，联合等）

3 个解决方案

#1

You can't use prepared statements with arrays simply because sql itself does not support arrays. Which is a real shame. Somewhere along the line you actually need to determine if your data contains say three items and emit a IN (?,?,?). The Doctrine ORM entity manager does this for you automatically.

您不能将预准备语句与数组一起使用，因为sql本身不支持数组。这是一个真正的耻辱。沿着这条线的某个地方，您实际需要确定您的数据是否包含三个项并发出一个IN（？，？，？）。 Doctrine ORM实体管理器会自动为您执行此操作。

Fortunately, the DBAL has you covered. You just don't use bind or prepare. The manual has an example: http://doctrine-orm.readthedocs.io/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

幸运的是，DBAL让您满意。你只是不使用绑定或准备。该手册有一个例子：http：//doctrine-orm.readthedocs.io/projects/doctrine-dbal/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

In your case it would look something like:

在你的情况下，它看起来像：

$sql = "select * from user where id in (?) and status = ?";
$values = [$accounts,'declined'];
$types = [Connection::PARAM_INT_ARRAY, \PDO::PARAM_STR];
$stmt = $conn->executeQuery($sql,$values,$types);
$result = $stmt->fetchAll();

The above code is untested but you should get the idea. (Make sure you use Doctrine\DBAL\Connection; for Connection::PARAM_INT_ARRAY)

上面的代码未经测试，但您应该明白这一点。（确保使用Doctrine \ DBAL \ Connection;对于Connection :: PARAM_INT_ARRAY）

#2

If you want to stick to the :param syntax where order does not matter, you have to do a bit of extra work, but I'll show you an easier way to bind the parameters:

如果你想坚持使用：param语法，顺序无关紧要，你必须做一些额外的工作，但我会告诉你一个更简单的方法来绑定参数：

// store all your parameters in one array
$params = array(
    ':status' => 'declined'
);

// then, using your arbitrary array of id's ...
$array_of_ids = array(5, 6, 12, 14);

// ... we're going to build an array of corresponding parameter names
$id_params = array();
foreach ($array_of_ids as $i => $id) {
    // generate a unique name for this parameter
    $name = ":id_$i"; // ":id_0", ":id_1", etc.

    // set the value
    $params[$name] = $id;

    // and keep track of the name
    $id_params[] = $name;
}

// next prepare the parameter names for placement in the query string
$id_params = implode(',', $id_params); // ":id_0,:id_1,..."
$sql = "select * from user where id in ($id_params) and status = :status";

In this case we end up with: "select * from user where id in (:id_0,:id_1,:id_2,:id_3) and status = :status"

在这种情况下，我们最终得到：“select * from user in id in（：id_0，：id_1，：id_2，：id_3）and status =：status”

// now prepare your statement like before...
$stmt = $em->getConnection()->prepare($sql);

// ...bind all the params in one go...
$stmt->execute($params);

// ...and get your results!
$result = $stmt->fetchAll();

This approach will also work with an array of strings.

这种方法也适用于字符串数组。

#3

-1

You need to wrap them in an array

您需要将它们包装在一个数组中

$stmt->bindValue(':userId', array($accounts), array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY));

http://doctrine-dbal.readthedocs.io/en/latest/reference/data-retrieval-and-manipulation.html#list-of-parameters-conversion

edit

编辑

I should have elaborated more. You cannot bind an array like that, dont prepare the sql execute directly as the example in the docs.

我应该详细说明一下。你不能像这样绑定一个数组，不要直接准备sql执行文件中的例子。

$stmt = $conn->executeQuery('SELECT * FROM articles WHERE id IN (?)',
array(array(1, 2, 3, 4, 5, 6)),
array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY));

You cannot bind an array of values into a single prepared statement parameter

您不能将值数组绑定到单个预准备语句参数中

#1