CODEWITHSUNDEEP
mysqlsymfony
KodeFor.Me
KodeFor.Me

Reputation: 13511

Symfony: An exception occurred while executing SELECT ... Notice: Array to string conversion

Start by telling I am very new to Symfony and thus not experienced well with it.

I have a controller that should run a kind of complex query, and this morning I requested to allow run a where with in clause instead of =.

In addition, the query because it is complicated (It uses COALESCE, SUM, etc in the select statement), I had to write the query manually and not by using the Doctrine QueryBuilder.

So, let's say, the simplified version of my previous Query that worked properly is the following:

$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();

And this is the change I did this morning:

$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();

But, by doing this change, I get the error:

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

Notice: Array to string conversion

In addition, if I go to Symfony Profiler, in the Doctrine tab, and copy the runnable query and executed in the MySQL client, I get the right information.

Any idea why I have this result? Do you see anything wrong?

Thanks in advance!

Upvotes: 0

Views: 589

Answers (1)

tolgakaragol
tolgakaragol

Reputation: 583

You should give fetch options. 

$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]);

Upvotes: 1

Related Questions