Is it possible to use result of an SQL function as a field in Doctrine?

Question

Assume I have Product entities and Review entities attached to products. Is it possible to attach a fields to a Product entity based on some result returned by an SQL query? Like attaching a ReviewsCount field equal to COUNT(Reviews.ID) as ReviewsCount.

I know it is possible to do that in a function like

public function getReviewsCount() {
    return count($this->Reviews);
}

But I want doing this with SQL to minimize number of database queries and increase performance, as normally I may not need to load hundreds of reviews, but still need to know there number. I think running SQL's COUNT would be much faster than going through 100 Products and calculating 100 Reviews for each. Moreover, that is just example, on practice I need more complex functions, that I think MySQL would process faster. Correct me if I'm wrong.

Answer 1

You can map a single column result to an entity field - look at native queries and ResultSetMapping to achieve this. As a simple example:

use Doctrine\ORM\Query\ResultSetMapping;

$sql = '
    SELECT p.*, COUNT(r.id)
    FROM products p
    LEFT JOIN reviews r ON p.id = r.product_id
';

$rsm = new ResultSetMapping;
$rsm->addEntityResult('AppBundle\Entity\Product', 'p');
$rsm->addFieldResult('p', 'COUNT(id)', 'reviewsCount');

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

Then in your Product entity you would have a $reviewsCount field and the count would be mapped to that. Note that this will only work if you have a column defined in the Doctrine metadata, like so:

/**
 * @ORM\Column(type="integer")
 */
private $reviewsCount;

public function getReviewsCount()
{
    return $this->reviewsCount;
}

This is what is suggested by the Aggregate Fields Doctrine documentation. The problem is here is that you are essentially making Doctrine think you have another column in your database called reviews_count, which is what you don't want. So, this will still work without physically adding that column, but if you ever run a doctrine:schema:update it's going to add that column in for you. Unfortunately Doctrine does not really allow virtual properties, so another solution would be to write your own custom hydrator, or perhaps subscribe to the loadClassMetadata event and manually add the mapping yourself after your particular entity (or entities) load.

Note that if you do something like COUNT(r.id) AS reviewsCount then you can no longer use COUNT(id) in your addFieldResult() function, and must instead use the alias reviewsCount for that second parameter.

You can also use the ResultSetMappingBuilder as a start into using the result set mapping.

My actual suggestion is to do this manually instead of going through all of that extra stuff. Essentially create a normal query that returns both your entity and scalar results into an array, then set the scalar result to a corresponding, unmapped field on your entity, and return the entity.

Answer 2

After detailed investigation I've found there are several ways to do something close to what I wanted including listed in other answers, but all of them have some minuses. Finally I've decided to use CustomHydrators. It seems that properties not managed with ORM cannot be mapped with ResultSetMapping as fields, but can be got as scalars and attached to an entity manually (as PHP allows to attach object properties on the fly). However, result that you get from doctrine remains in the cache. That means properties set in that way may be reset if you make some other query that would contain these entities too.

Another way to do that was adding these field directly to doctrine's metadata cache. I tried doing that in a CustomHydrator:

protected function getClassMetadata($className)
{
    if ( ! isset($this->_metadataCache[$className])) {
        $this->_metadataCache[$className] = $this->_em->getClassMetadata($className);

        if ($className === "SomeBundle\Entity\Product") {
            $this->insertField($className, "ReviewsCount");
        }
    }

    return $this->_metadataCache[$className];
}

protected function insertField($className, $fieldName) {
    $this->_metadataCache[$className]->fieldMappings[$fieldName] = ["fieldName" => $fieldName, "type" => "text", "scale" => 0, "length" => null, "unique" => false, "nullable" => true, "precision" => 0];
    $this->_metadataCache[$className]->reflFields[$fieldName] = new \ReflectionProperty($className, $fieldName);

    return $this->_metadataCache[$className];
}

However, that method also had problems with entities' properties reset. So, my final solution was just to use stdClass to get the same structure, but not managed by doctrine:

namespace SomeBundle;

use PDO;
use Doctrine\ORM\Query\ResultSetMapping;

class CustomHydrator extends \Doctrine\ORM\Internal\Hydration\ObjectHydrator {
    public function hydrateAll($stmt, $resultSetMapping, array $hints = array()) {
        $data = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $result = [];

        foreach($resultSetMapping->entityMappings as $root => $something) {
            $rootIDField = $this->getIDFieldName($root, $resultSetMapping);

            foreach($data as $row) {
                $key = $this->findEntityByID($result, $row[$rootIDField]);

                if ($key === null) {
                    $result[] = new \stdClass();
                    end($result);
                    $key = key($result);
                }

                foreach ($row as $column => $field)
                    if (isset($resultSetMapping->columnOwnerMap[$column]))
                        $this->attach($result[$key], $field, $this->getPath($root, $resultSetMapping, $column));
            }
        }


        return $result;
    }

    private function getIDFieldName($entityAlias, ResultSetMapping $rsm) {
        foreach ($rsm->fieldMappings as $key => $field)
            if ($field === 'ID' && $rsm->columnOwnerMap[$key] === $entityAlias) return $key;

            return null;
    }

    private function findEntityByID($array, $ID) {
        foreach($array as $index => $entity)
            if (isset($entity->ID) && $entity->ID === $ID) return $index;

        return null;
    }

    private function getPath($root, ResultSetMapping $rsm, $column) {
        $path = [$rsm->fieldMappings[$column]];
        if ($rsm->columnOwnerMap[$column] !== $root) 
            array_splice($path, 0, 0, $this->getParent($root, $rsm, $rsm->columnOwnerMap[$column]));

        return $path;
    }

    private function getParent($root, ResultSetMapping $rsm, $entityAlias) {
        $path = [];
        if (isset($rsm->parentAliasMap[$entityAlias])) {
            $path[] = $rsm->relationMap[$entityAlias];
            array_splice($path, 0, 0, $this->getParent($root, $rsm, array_search($rsm->parentAliasMap[$entityAlias], $rsm->relationMap)));
        }

        return $path;
    }

    private function attach($object, $field, $place) {
        if (count($place) > 1) {
            $prop = $place[0];
            array_splice($place, 0, 1);
            if (!isset($object->{$prop})) $object->{$prop} = new \stdClass();
            $this->attach($object->{$prop}, $field, $place);
        } else {
            $prop = $place[0];
            $object->{$prop} = $field;
        }
    }
}

With that class you can get any structure and attach any entities however you like:

$sql = '
    SELECT p.*, COUNT(r.id)
    FROM products p
    LEFT JOIN reviews r ON p.id = r.product_id
';

$em = $this->getDoctrine()->getManager();

$rsm = new ResultSetMapping();
$rsm->addEntityResult('SomeBundle\Entity\Product', 'p');
$rsm->addFieldResult('p', 'COUNT(id)', 'reviewsCount');

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

$em->getConfiguration()->addCustomHydrationMode('CustomHydrator', 'SomeBundle\CustomHydrator');
$results = $query->getResult('CustomHydrator');

Hope that may help someone :)

Answer 3

Yes, it is possible, you need to use QueryBuilder to achieve that:

$result = $em->getRepository('AppBundle:Product')
    ->createQueryBuilder('p')
    ->select('p, count(r.id) as countResult')
    ->leftJoin('p.Review', 'r')
    ->groupBy('r.id')
    ->getQuery()
    ->getArrayResult();

and now you can do something like:

foreach ($result as $row) {
    echo $row['countResult'];
    echo $row['anyOtherProductField'];
}

Answer 4

If you're on Doctrine 2.1+, consider using EXTRA_LAZY associations:

They allow you to implement a method like yours in your entity, doing a straight count on the association instead of retrieving all the entities in it:

/**
* @ORM\OneToMany(targetEntity="Review", mappedBy="Product" fetch="EXTRA_LAZY")
*/
private $Reviews;

public function getReviewsCount() {
    return $this->Reviews->count();
}