使用mysql用户变量的Doctrine本机查询

This is my native query:

$query = <<<Query
        SET @num = 0;
        SET @type = 0;
        SELECT md.*, od.id AS order_detail_id, od.erc AS od_ec,
              @num := if(@type = od.id, @num + 1, 1) AS row_number,
              @type := od.id AS dummy
        FROM bar md
        LEFT JOIN foobar mb ON md.mbi = mb.id
        LEFT JOIN barfoo od ON od.mbid = mb.id 
        WHERE od.id IN ($where)
        AND (od.status = $statusQueued OR od.status = $statusProcessing)
        GROUP BY md.id
        HAVING row_number <= od.erc
        ORDER BY md.qt ASC
        LIMIT 0, 1000
Query;

$nativeQuery = $this->_em->createNativeQuery($query, $rsm);

When I execute it, PDO gets upset and throws me an error:

[PDOException] SQLSTATE[HY000]: General error

No more informations.

However when I get rid of first two lines (with "SET") the query runs (however returns wrong results, as the variables must be initialised in this case). How can I force PDO to run this correctly? (when run via PMA it works just fine)

It seems that most elegant solution is to leverage MySQL alternative initialisation syntax:

$query = <<<Query
        SELECT md.*, od.id AS order_detail_id, od.erc AS od_ec,
              @num := if(@type = od.id, @num + 1, 1) AS row_number,
              @type := od.id AS dummy
        FROM (SELECT @num := 0, @type := 0) init, bar md
        LEFT JOIN foobar mb ON md.mbi = mb.id
        LEFT JOIN barfoo od ON od.mbid = mb.id 
        WHERE od.id IN ($where)
        AND (od.status = $statusQueued OR od.status = $statusProcessing)
        GROUP BY md.id
        HAVING row_number <= od.erc
        ORDER BY md.qt ASC
        LIMIT 0, 1000
Query;

I ran into the same kind of issue trying to run several queries in one call (in my case it was about using SQL_CALC_FOUND_ROWS and its buddy FOUND_ROWS()).

Doctrine can't handle this. I don't know the official reason, but in my opinion it's probably a security concern (as the EM's calls are then open to basic query injection).

Still in my opinion, the most elegant way of dealing with this is using the EM's transactional mode:

//fetch PDO-ish adapter
$conn = $em->getConnection();
//open an atomic sequence
$conn->beginTransaction();
//do your stuff
$conn->executeQuery('SET @...');
$stmt = $conn->executeQuery('SELECT stuff using @vars');
//commit the atomic sequence
$conn->commit();
//fetch the result, could be useful ;)
$data = $conn->fetchAll();

Hope this helps! (and works in your case...)