CODEWITHSUNDEEP
sqlsymfonydoctrine
Zatla00
Zatla00

Reputation: 499

find objects instead of arrays from native SQL request with doctrine (Symfony)

I'm trying to get some some results from a leaderboard to be served in a JSON format.

My actual query, which is working is as follows:

...
$pos = 25 //some position
$cnx->beginTransaction();
        $cnx->executeQuery('SET @rownum := 0');
        $sql = "
        SELECT * FROM (
            SELECT @rownum := @rownum + 1 AS rank, id, name, birthdate, gamesplayed,country,city
            FROM board  ORDER BY score DESC
        ) as result 
        WHERE result.rank >= (" . $pos . "-10)  and result.rank <= (" . $pos . "+10)
        ORDER BY rank
        ";

$preresults = $cnx->executeQuery($sql);
$cnx->commit();

$results = $preresults->fetchAll();

The request works, but I lost all fields types (cast), all values are in string format.

How to get values in proper type like if I use dql (objects)?

Upvotes: 0

Views: 169

Answers (1)

Rodmar Zavala
Rodmar Zavala

Reputation: 137

you need to use the ResultSetMappingBuilder class like this:

$rsm = new ResultSetMappingBuilder($this->entityManager);
$rsm->addRootEntityFromClassMetadata('App:YourEntity', 'y');
$sql = "SELECT * FROM (
        SELECT @rownum := @rownum + 1 AS rank, id, name, birthdate, gamesplayed,country,city
        FROM board  ORDER BY score DESC
    ) as result 
    WHERE result.rank >= (" . $pos . "-10)  and result.rank <= (" . $pos . "+10)
    ORDER BY rank";
$query = $this->entityManager->createNativeQuery($sql, $rsm);

return $query->getResult();

That will return an array of YourEntity[]

Upvotes: 1

Related Questions