聚合数据组为Yii2关系

Question

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?

Answer 1

/**
 * @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.