Your Location is: Home > Php

SQL query with SQL variables does not work in Doctrine but works if executed manually

From: Male View: 4704 Rawburner 

Question

I have this query, which retrieves many results if I execute it manually:

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)

When I run this through Doctrine connection, then the result is always empty.

/** @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;

I have tried some other variations with prepared statement and blank PDO-Connection. Results are empty. Doctrine leaves this in 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) [] []

I can take the query from log, paste it into my client and it will work. It seems to me that there is an issue with the colon ":" or the "@".

Best answer

With an initial value for @pv it worked. Code:

/** @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();

Thanks to @bill-karwin.