Value-binding is not working as expected. Why?

Question

I am using Symfony 3.4.6 + Doctrine - these are my main "tools".

Two identical queries written in MySQL give different results. Why? I don't know. Hope someone will help me.

First (working version):

$connection = $this->getEntityManager()->getConnection();
        $result = $connection->fetchAll('
        SELECT *
        FROM payment_transaction
        LEFT JOIN `user` ON `user`.`id` = payment_transaction.from_id
        WHERE payment_transaction.to_id = :user
              AND payment_transaction.type IN ("' . implode('", "', $type) . '")
              AND payment_transaction.registered_at >= :from
              AND payment_transaction.registered_at <= :to
        ', [
            "user" => $user->getId(),
            "from" => $from->format("Y-m-d H:i:s"),
            "to" => $to->format("Y-m-d H:i:s"),
        ]);

Second (gives incorrect results):

$connection = $this->getEntityManager()->getConnection();
        $result = $connection->fetchAll('
        SELECT *
        FROM payment_transaction
        LEFT JOIN `user` ON `user`.`id` = payment_transaction.from_id
        WHERE payment_transaction.to_id = :user
              AND payment_transaction.type IN (:types)
              AND payment_transaction.registered_at >= :from
              AND payment_transaction.registered_at <= :to
        ', [
            "user" => $user->getId(),
            "from" => $from->format("Y-m-d H:i:s"),
            "to" => $to->format("Y-m-d H:i:s"),
            "types" => '"' . implode('", "', $type) . '"',
        ]);

Third (gives incorrect results):

$connection = $this->getEntityManager()->getConnection();
        $result = $connection->fetchAll('
        SELECT *
        FROM payment_transaction
        LEFT JOIN `user` ON `user`.`id` = payment_transaction.from_id
        WHERE payment_transaction.to_id = :user
              AND payment_transaction.type IN (:types)
              AND payment_transaction.registered_at >= :from
              AND payment_transaction.registered_at <= :to
        ', [
            "user" => $user->getId(),
            "from" => $from->format("Y-m-d H:i:s"),
            "to" => $to->format("Y-m-d H:i:s"),
            "types" => "\"" . implode("\", \"", $type) . "\"",
        ]);

Fourth (gives an error - Array to string conversion):

$connection = $this->getEntityManager()->getConnection();
        $result = $connection->fetchAll('
        SELECT *
        FROM payment_transaction
        LEFT JOIN `user` ON `user`.`id` = payment_transaction.from_id
        WHERE payment_transaction.to_id = :user
              AND payment_transaction.type IN (:types)
              AND payment_transaction.registered_at >= :from
              AND payment_transaction.registered_at <= :to
        ', [
            "user" => $user->getId(),
            "from" => $from->format("Y-m-d H:i:s"),
            "to" => $to->format("Y-m-d H:i:s"),
            "types" => $types,
        ]);

If I am replacing :types with the results generated by implode function - second and third queries work just fine.

Where is the problem - IN? implode? Binding?

Thank you.

UPDATE (22:29)

Latest code snippet:

public function history(User $user, \DateTime $from, \DateTime $to,
                            array $type = [PaymentTransaction::PAYMENT_TRANSACTION_TYPE_SYSTEM,
                                PaymentTransaction::PAYMENT_TRANSACTION_TYPE_PARTNERSHIP,
                                PaymentTransaction::PAYMENT_TRANSACTION_TYPE_PERSONAL]): array
    {
        $connection = $this->getEntityManager()->getConnection();
        $result = $connection->fetchAll('
        SELECT payment_transaction.*, `user`.`first_name` as `user_first_name`, `user`.`last_name` as `user_last_name`, `user`.`email` as `user_email`
        FROM payment_transaction
        LEFT JOIN `user` ON `user`.`id` = payment_transaction.from_id
        WHERE payment_transaction.to_id = :user
              AND payment_transaction.type IN (:types)
              AND payment_transaction.registered_at >= :from
              AND payment_transaction.registered_at <= :to
        ORDER BY payment_transaction.id DESC
        ', [
            "user" => $user->getId(),
            "from" => $from->format("Y-m-d H:i:s"),
            "to" => $to->format("Y-m-d H:i:s"),
            "types" => $type,
        ], [
            \PDO::PARAM_INT,
            \PDO::PARAM_STR,
            \PDO::PARAM_STR,
            Connection::PARAM_STR_ARRAY,
        ]);

        return $result;
    }

And it still gives me an error:

' with params [134, ["system"], "2018-02-28 21:24:47", "2018-03-30 21:24:47"]:
Notice: Array to string conversion

UPDATE (22:50)

The query is working after replacing placeholders with ? signs. The question still remains - WHY it's not working with :name-placeholders?

Answer 1

Solved by replacing PDO-types with DBAL-types.

[
            "userId" => $user->getId(),
            "types" => $type,
            "from" => $from->format("Y-m-d H:i:s"),
            "to" => $to->format("Y-m-d H:i:s"),
        ], [
            "userId" => Type::INTEGER,
            "types" => Type::SIMPLE_ARRAY,
            "from" => Type::STRING,
            "to" => Type::STRING,
        ]

Thanks to Ocramius from GitHub: https://github.com/doctrine/doctrine2/issues/7166

Answer 2

'... AND type IN (:types) ...' // sql query

To archive it by using DBAL API you need to bind the array of element as is:

"types" => $types, // $types = ['A', 'B', 'C'] or [1, 2, 3]

passing as a third argument the type of this:

Connection::PARAM_STR_ARRAY // or ::PARAM_INT_ARRAY for integer list

Example:

use Doctrine\DBAL\Connection; // don't forget this

// ...

$connection = $this->getEntityManager()->getConnection();
$result = $connection->fetchAll('
    SELECT *
    FROM payment_transaction
    LEFT JOIN `user` ON `user`.`id` = payment_transaction.from_id
    WHERE payment_transaction.to_id = :user
          AND payment_transaction.type IN (:types)
          AND payment_transaction.registered_at >= :from
          AND payment_transaction.registered_at <= :to
    ', [
        "user" => $user->getId(),
        "from" => $from->format("Y-m-d H:i:s"),
        "to" => $to->format("Y-m-d H:i:s"),
        "types" => $types,
    ], [
        \PDO::PARAM_INT,
        \PDO::PARAM_STR,
        \PDO::PARAM_STR,
        Connection::PARAM_STR_ARRAY,
    ]);