聚合数据组为Yii2关系

In my Product model I have the following two methods; getOrderFeedback() is normal relation returning an ActiveQuery and getTotalAndAveFeedback() which returns an array of aggregate data for a given Product record.

/**
 * @return \yii\db\ActiveQuery
 */
public function getOrderFeedback()
{
    return $this->hasMany(OrderFeedback::className(), ['productID' => 'ID']);
}

/**
 * @return array
 */
public function getTotalAndAveFeedback()
{
    return $this->getOrderFeedback()
        ->select(['COUNT(*) AS num',
                  'AVG(rating) AS avg',
                  'FLOOR(AVG(rating)) AS full',
                  'MOD(AVG(rating), 1) AS decimal_portion',
                  '5 - FLOOR(AVG(rating)) - CEILING(MOD(AVG(rating),1)) AS empty'])->one();
}

When I have a group of Products and iterate over them to gather the totalAndAveFeedback for a single Product, the ActiveQuery is triggered causing a lazy style reading from the db. Something like the following

$my_products = Product::find()->with(['supplier', 'location'])
               ->where(['published' => 1])
               ->all();

# SELECT * FROM product WHERE published = 1; 
# Find all products
# SELECT * FROM supplier WHERE ID IN (s1, s2, s3);
# Eagerly load supplier
# SELECT * FROM location WHERE ID IN (l1, l2, l3, l4);
# Eagerly load location

foreach ($my_products as $product) {
   echo $product->supplier->supplier_name; 
   # supplier object available from eager loading
   echo $product->location->title;
   # location object available from eager loading
   echo $product->totalAndAveFeedback->decimal_portion; 
   # requires db access to 'lazy load' data for each product record
   # SELECT COUNT(*) AS num, AVG(rating) AS avg, FLOOR(AVG(rating)) AS full, MOD(AVG(rating), 1) AS decimal_portion, 5 - FLOOR(AVG(rating)) - CEILING(MOD(AVG(rating),1)) AS empty FROM `order_feedback` WHERE `productID`=pID
}

Is there a way to treat getTotalAndAveFeedback() as relation to allow a joinWith() / with() on a Product ActiveQuery so the data can be eagerly loaded for each Product and available in the same manner as the Supplier or Location relations?

I've tried removing the call to one() in getTotalAndAveFeedback() to allow the method to be treated as a relation, but the totalAndAveFeedback attribute of the Product records were all empty arrays where I'd expect an object with attributes such as decimal_portion as if I'd called a simple relation like Supplier.

Is this possible in Yii2?

dqwd71332
dqwd71332 如果您追加productID列以在getTotalAndAveFeedback()方法中选择数组,它将起作用。但是在急切加载时,您将获得SELECTCOUNT(*)ASnum,AVG(rating)ASavg,FLOOR(AVG(rating))ASfull,MOD(AVG(rating),1)ASdecimal_portion,5-FLOOR(AVG(评级))-CEILING(MOD(AVG(评级),1))AS空FROMorder_feedbackWHEREproductIDIN(1,2,3...100),所以这个查询的结果将不是你想要的
3 年多之前 回复

1个回答



  / ** 

  • @return \ yii \ db \ ActiveQuery
  • /
    npublic function getTotalAndAveFeedback()\ n {
    return $ this-> getOrderFeedback()

    • > select(['productID','COUNT(*)AS num', 'AVG(rating)AS avg', 'FLOOR( AVG(等级))AS满', 'MOD(AVG(等级),1)AS decimal_portion', '5 - FLOOR(AVG(等级)) - CEILING(MOD(AVG(等级),1)) AS empty'])
    • &gt; groupBy(['productID']); } </ code> </ pre>

    正如@ Pa3Py6aka指出的那样,数组的原因是 如果在没有选择 productID </ code>的情况下加入关系时为空,则返回的 OrderFeedback </ code>和 Product </ code>之间没有链接。</ p> \ n

    通过将 groupBy(['productID'])</ code>方法调用添加到 ActiveQuery </ code>,每个 OrderFeedbacks </ code>都被热切地加载并且 可以访问单个 Product </ code>对象。</ p>
    </ d 四>

展开原文

原文

/**
 * @return \yii\db\ActiveQuery
 */
public function getTotalAndAveFeedback()
{
    return $this->getOrderFeedback()
        ->select(['productID', 'COUNT(*) AS num',
                  'AVG(rating) AS avg',
                  'FLOOR(AVG(rating)) AS full',
                  'MOD(AVG(rating), 1) AS decimal_portion',
                  '5 - FLOOR(AVG(rating)) - CEILING(MOD(AVG(rating),1)) AS empty'])
        ->groupBy(['productID']);
}

As @Pa3Py6aka pointed out, the reason the array was empty when joining on the relation was without selecting the productID there was no link between the returned OrderFeedbacks and the Product.

By adding the groupBy(['productID']) method call to the ActiveQuery each the OrderFeedbacks are eagerly loaded and accessible to the individualProduct objects.

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐