mysql - Symfony:执行 SELECT 时发生异常...注意:数组到字符串的转换

Question

首先告诉我我对 Symfony 很陌生，因此没有很好的经验。

我有一个 Controller 应该运行一种复杂的查询，今天早上我请求允许运行带有 in 子句的 where 而不是 =.

另外，由于查询比较复杂(在select语句中使用了COALESCE、SUM等)，我不得不手动编写查询，而不是使用 Doctrine QueryBuilder。

所以，假设我之前正常运行的查询的简化版本如下:

$query = "SELECT
    s.*
FROM
    sales AS s
    LEFT JOIN client AS c ON s.client_id = c.id
    LEFT JOIN service_category AS sc ON s.service = sc.id
WHERE
    sc.name = :serviceCategory";

$params = [
    'serviceCategory' => 'Service #1'
];
$conn  = $this->entityManager->getConnection();
$query = $conn->prepare($sql);
$query->execute($params);

return $query->fetchAll();

这是我今天早上所做的改变:

$query = "SELECT
    s.*
FROM
    sales AS s
    LEFT JOIN client AS c ON s.client_id = c.id
    LEFT JOIN service_category AS sc ON s.service = sc.id
WHERE
    sc.name IN (:serviceCategory)";

$params = [
    'serviceCategory' => [
       "Service X",
       "Service Y"
    ]
];
$conn  = $this->entityManager->getConnection();
$query = $conn->prepare($sql);
$query->execute($params);

return $query->fetchAll();

但是，通过进行此更改，我收到错误:

An exception occurred while executing 'SELECT ... ' with params [["Service X", "Service Y"]]:

Notice: Array to string conversion

此外，如果我进入 Symfony Profiler，在 Doctrine 选项卡中，复制可运行查询并在 MySQL 客户端中执行，我会获得正确的信息。

知道为什么我会得到这个结果吗？你看出什么问题了吗？

提前致谢!

Answer 1

您应该提供获取选项。

$query = <<<SQL
SELECT
    s.*
FROM
    sales AS s
    LEFT JOIN client AS c ON s.client_id = c.id
    LEFT JOIN service_category AS sc ON s.service = sc.id
WHERE
    sc.name IN (:serviceCategory)
>>>;

$params = [
    'serviceCategory' => [
       "Service X",
       "Service Y"
    ]
];
$conn  = $this->entityManager->getConnection();
$query = $conn->prepare($sql);

return $query->fetchAll($sql, $params, [\Doctrine\DBAL\Connection::PARAM_STR_ARRAY]);