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

问题描述

假设我有 Product 实体和 Review 实体附加到产品.是否可以根据 SQL 查询返回的某些结果将字段附加到 Product 实体?就像附加一个 ReviewsCount 字段等于 COUNT(Reviews.ID) 作为 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.

我知道可以在像

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.

推荐答案

您可以映射一个 单列结果到实体字段 - 查看 本机查询 和ResultSetMapping 来实现这一点.举个简单的例子:

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

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

$rsm = new ResultSetMapping;
$rsm->addEntityResult('AppBundleEntityProduct', 'p');
$rsm->addFieldResult('p', 'COUNT(id)', 'reviewsCount');

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

然后在您的 Product 实体中，您将有一个 $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:

/**
 * @ORMColumn(type="integer")
 */
private $reviewsCount;

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

这是聚合字段 Doctrine 文档.问题在于，您实际上是在让 Doctrine 认为您的数据库中有另一列名为 reviews_count，而这正是您不想要的.因此，这仍然可以在不实际添加该列的情况下工作，但是如果您运行 doctrine:schema:update 它将为您添加该列.不幸的是，Doctrine 并没有真正允许虚拟属性，因此另一种解决方案是编写您自己的自定义 hydrator，或者订阅 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 reviewCount 之类的操作，则您不能再在 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.

您也可以使用 ResultSetMappingBuilder 作为开始使用结果集映射.

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屋！