How to use mysql variables in doctrine

Question

https://stackoverflow.com/a/12772507/1507546

I want to execute this query through doctrine but I'm getting the error below

[Doctrine\DBAL\Driver\PDOException] SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@counter := 0' at line 1

Here is my code

$sql = <<<S
    SET @counter = 0; 
    Select sub.orderid,sub.value,(@counter := @counter +1) as counter
    FROM
    (
        select orderid, 
          round(sum(unitprice * quantity),2) as value
        from order_details
        group by orderid
    ) sub
    order by 2 desc
    limit 10
S;

stmt = $this->_em->getConnection()->prepare($sql);

$stmt->execute();

return $stmt->fetchAll(AbstractQuery::HYDRATE_ARRAY);

Answer 1

Most sql APIs don't allow multiple statements without extra configuration. You'll need to pass them in as separate statements:

$this->_em->getConnection()->exec("SET @counter = 0"); // May need tweaking, I'm not familiar with Doctrine
$sql = <<<S
    Select sub.orderid,sub.value,(@counter := @counter +1) as counter
    FROM
    (
        select orderid, 
          round(sum(unitprice * quantity),2) as value
        from order_details
        group by orderid
    ) sub
    order by 2 desc
    limit 10
S;

stmt = $this->_em->getConnection()->prepare($sql);