是否可以将SQL函数的结果用作Doctrine中的字段? [英] Is it possible to use result of an SQL function as a field in Doctrine?

问题描述

假设我有Product个实体和Review个实体附加到产品上.是否可以根据SQL查询返回的某些结果将字段附加到Product实体?就像附加一个等于COUNT(Reviews.ID) as ReviewsCount的ReviewsCount字段一样.

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);
}

但是我想使用SQL来最大程度地减少数据库查询的次数并提高性能，因为通常我可能不需要加载数百条评论，但仍然需要知道那里的数目.我认为运行SQL的COUNT会比遍历100个产品并为每个产品计算100条评论快得多.而且，这只是示例，在实践中我需要更复杂的功能，我认为MySQL的处理速度更快.如果我错了，请纠正我.

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.

推荐答案

您可以映射

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();

然后在您的产品实体中，您将有一个$reviewsCount字段，并且计数将被映射到该字段.请注意，只有在Doctrine元数据中定义了列的情况下，这才起作用:

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;
}

这是汇总字段学说文档.问题在于，您实际上是在使Doctrine认为您的数据库中还有一个名为reviews_count的列，这是您不想要的.因此，无需物理添加该列仍然可以使用，但是如果您运行doctrine:schema:update，它将为您添加该列.不幸的是，Doctrine并没有真正允许使用虚拟属性，因此另一种解决方案是编写您自己的自定义水化器，或者订阅loadClassMetadata事件，并在加载特定实体后亲自手动添加映射.

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.

请注意，如果您执行类似COUNT(r.id) AS reviewsCount的操作，则您将无法再在addFieldResult()函数中使用COUNT(id)，而必须对第二个参数使用别名reviewsCount.

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.

这篇关于是否可以将SQL函数的结果用作Doctrine中的字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！