带有SQL变量的SQL查询在Doctrine中不起作用，但如果手动执行，则可以起作用

Question

我有这个查询，如果我手动执行它，它会检索许多结果：

SELECT id, filename, path, parentId, CONCAT(path, filename) full_path
FROM (SELECT * FROM assets WHERE `type` = 'folder' ORDER BY parentId, id) assets_sorted
WHERE FIND_IN_SET(parentId, @pv)
AND LENGTH(@pv := CONCAT(@pv, ',', id))
ORDER BY LOWER(full_path)

当我通过学说的联系来运行这一点时，结果总是空的。

/** @var Connection $con */
$con = $this->getDoctrine()->getConnection();
$folders = $con->executeQuery(
<<<SQL
SELECT id, filename, path, parentId, CONCAT(path, filename) full_path
FROM (SELECT * FROM assets WHERE `type` = 'folder' ORDER BY parentId, id) assets_sorted
WHERE FIND_IN_SET(parentId, @pv)
AND LENGTH(@pv := CONCAT(@pv, ',', id))
ORDER BY LOWER(full_path)
SQL
)->fetchAll();
dump($folders);die;

我已经尝试了一些其他变体，准备好的语句和空白的PDO连接。结果为空。条令将此保留在dev.log中：

[2020-11-09 14:50:38] doctrine.DEBUG:     SELECT id, filename, path, parentId, CONCAT(path, filename) full_path     FROM (SELECT * FROM assets WHERE `type` = 'folder' ORDER BY parentId, id) assets_sorted     WHERE FIND_IN_SET(parentId, @pv)     AND LENGTH(@pv := CONCAT(@pv, ',', id))     ORDER BY LOWER(full_path) [] []

我可以从日志中获取查询，将其粘贴到我的客户端，它就会工作。在我看来，冒号"："或"@&"；。

Answer 1

有了@pv的初始值，它就工作了。代码：

/** @var Connection $con */
$con = $this->getDoctrine()->getConnection();
$con->executeQuery("SET @pv = '0';");
$folders = $con->executeQuery(
<<<SQL
SELECT id, filename, path, parentId, CONCAT(path, filename) full_path
FROM (SELECT * FROM assets WHERE `type` = 'folder' ORDER BY parentId, id) assets_sorted
WHERE FIND_IN_SET(parentId, @pv)
AND LENGTH(@pv := CONCAT(@pv, ',', id))
ORDER BY LOWER(full_path)
SQL
)->fetchAll();

感谢@bill karwin。