Trouble creating a Doctrine ResultSetMapping for parsing a raw SQL query to a single database table

I am trying to convert a raw SQL command into Doctrine Entities. The sql command is:

        $sql = 'SELECT SUM(IF (SCORE = 1, 1, 0)) AS UPVOTE, SUM(IF (SCORE = -1, 1, 0)) AS DOWNVOTE, BLOCKID, ELEMENTID FROM Voting GROUP BY BLOCKID, ELEMENTID';

Which works and returns from the Voting table:

UPVOTE  DOWNVOTE  BLOCKID  ELEMENTID
1       1         3223     person1
3       2         3223     person2
0       1         3223     person3

I have the following PHP code in my controller file, which is currently returning an empty array:

    $sql = 'SELECT SUM(IF (SCORE = 1, 1, 0)) AS UPVOTE, SUM(IF (SCORE = -1, 1, 0)) AS DOWNVOTE, BLOCKID, ELEMENTID FROM Voting GROUP BY BLOCKID, ELEMENTID';
    $rsm = new ResultSetMapping();
    $rsm->addEntityResult('Concrete\Entity\Vote', 'u');
    $rsm->addFieldResult('u', 'blockid', 'blockid');
    $rsm->addFieldResult('u', 'elementId', 'elementId');
    $rsm->addFieldResult('u', 'score', 'score');
    $rsm->addFieldResult('u', 'id', 'id');
    $rsm->addFieldResult('u', 'ipAddress', 'ipAddress');
    $rsm->addFieldResult('u', 'created_at', 'created_at');

    $rsm->addScalarResult('upvote', 'upvote');
    $rsm->addScalarResult('downvote', 'downvote');

    $query = $this->em->createNativeQuery($sql, $rsm);    
    $results = $query->getResult();
    echo json_encode($results);

I've tried consulting the Doctrine documentation but am having trouble ascertaining what my issue is. I have called addFieldResult on all the columns in my table (such as id, ipAddress, created_at) which don't appear at all in the results - is this required or not?

With the code above I get no errors, just an empty array being returned. Am I missing something or just plain doing this wrong?

Solution

You don't need to use a native query to execute this kind of query. You can rely on CASE expressions to write your conditions. Using the query builder, your query would look like this:

$results = $entityManager->createQueryBuilder()
        ->from('Concrete\Entity\Vote', 'v')
        ->select('v.elementId', 'v.blockId')
        ->addSelect('SUM(CASE WHEN v.score = 1 THEN 1 ELSE 0 END) AS upvote')
        ->addSelect('SUM(CASE WHEN v.score = -1 THEN 1 ELSE 0 END) AS downvote')
        ->groupBy('v.blockId')
        ->addGroupBy('v.elementId')
        ->getQuery()
        ->getResult();

In case you still want to use a native query, you don't need to over do it with your ResultSetMapping if you simply want to retrieve BLOCKID and ELEMENTID from your table. Simply use addScalarResult with the first argument being the name of the field/alias which appears in your SELECT clause and the second argument being the alias you want it to have in your result set. (You can add a third argument to specify the type of the scalar value being retrieved as well, see block_id or upvote below).

With a Vote entity like this one:

/** @ORM\Table(name="votes") */
class Vote
{
    /**
     * @ORM\Column(name="id", type="integer", unique=true)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /** @ORM\Column(name="block_id", type="integer") */
    private $blockId;

    /** @ORM\Column(name="element_id", type="string") */
    private $elementId;

    /** @ORM\Column(name="score", type="integer") */
    private $score;
}

The following native query would output the same result as the query built with the query builder above.

$sql = 'SELECT SUM(IF (SCORE = 1, 1, 0)) AS upvote, SUM(IF (SCORE = -1, 1, 0)) AS downvote, block_id, element_id FROM votes GROUP BY block_id, element_id';
$rsm = new ResultSetMapping();
$rsm->addScalarResult('block_id', 'blockId', 'integer');
$rsm->addScalarResult('element_id', 'elementId');
$rsm->addScalarResult('upvote', 'upvote', 'integer');
$rsm->addScalarResult('downvote', 'downvote', 'integer');

$query = $em->createNativeQuery($sql, $rsm);    
$result = $query->getResult();

The only difference you will get is that the upvote/downvote values will be integers instead of strings since you could specify it here (while you couldn't with the query builder).