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

Question

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)?

Rodmar Zavala · Accepted Answer · 2019-03-25 20:37:27Z

1

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[]

answered Mar 25, 2019 at 20:37

Rodmar Zavala

1376 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related