Doctrine Symfony - Use entity mapping with Raw SQL

Question

I have multiple machine attached to a bunch of statistics in my database and I need to retrieve only the most recent statistic of each one.

I used the doctrine entity manager with the following request :

$str = "SELECT s, sa.slug ";
$str .= "FROM AppBundle:Statistique s ";
$str .= "LEFT JOIN AppBundle:StatistiqueAnomalie sa WITH (s.anomalie = sa.num) ";
$str .= "LEFT JOIN AppBundle:Statistique sp WITH (s.numserieMachine = sp.numserieMachine AND s.datecrea < sp.datecrea) ";

$query = $this->getEntityManager()->createQuery($str);

//var_dump($query->getSql());
return $query->getResult();

However this request is very slow (=22sec approx.) so I wanted to replace it with a more optimized one in raw sql:

$str = "SELECT * FROM 
        ( SELECT numserie_machine, MAX( datecrea ) as ma FROM statistique GROUP BY numserie_machine ) as maxv 
        INNER JOIN statistique s on maxv.numserie_machine=s.numserie_machine and maxv.ma=s.datecrea 
        LEFT JOIN statistique_anomalie sa on s.anomalie=sa.num";

$conn = $this->getEntityManager()->getConnection();

$query = $conn->prepare($str);
$query->execute([]);

return $query->fetchAll();

This request works well and with a decent speed but I cannot use the data. Indeed my twig files use doctrine entities instead of an associative array.

Is there a way to map my result into enities ? Or at least, is there a way to execute a similar request with DQL ?

Thank you.

Answer 1

Yes, doctrine offers a way to hydrate results of a native query into entities. See http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/native-sql.html

With NativeQuery you can execute native SELECT SQL statements and map the results to Doctrine entities or any other result format supported by Doctrine.

In order to make this mapping possible, you need to describe to Doctrine what columns in the result map to which entity property. This description is represented by a ResultSetMapping object.

Answer 2

This is an example of a very simple SQL -> entity mapping with doctrine. The idea is to use ResultSeteMapping. The tricky part is to generate SELECT part correctly. Hope it will lead someone on a right path:

class MyAwesomeEntityRepository extends EntityRepository
{
    /*
     * @returns array|MyAwesomeEntity[]
     */
    public function findSomething(): array
    {
        $rsm = $this->createResultSetMappingBuilder('my_alias');
        $select = $rsm->generateSelectClause(['my_alias']);

        $sql = <<<SQL
SELECT $select
FROM MyAwesomeEntity AS my_alias
LIMIT 10;
SQL;

        $query = $this->getEntityManager()->createNativeQuery(
            $sql,
            $rsm
        );

        return $query->getResult();
    }
}

Note: on bad ResultSetMapping config (for example mismatch between SELECT and ResultSetMapping) there won't be any errors and result will be an empty array.