Doctrine Symfony - Use entity mapping with Raw SQL

user_8085705 0
Viewed 5097

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.

symfony doctrine-orm doctrine php
2 Answers

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.

user_13734600
Related