Propel2 与表连接，然后使用聚合函数分组

Question

我有两个表：food（id，name），review（user_id，food_id，rating）。现在我想将食物表与评论表连接起来，并将虚拟列添加到名称为 avg_rating 的食物表中，这显然会根据评论保存食物的平均评级值。所以我的想法是做这样的事情：

$food = FoodQuery::create()
        ->filterById(15) // constant for testing purposes only
        ->leftJoinWithReview()
        ->withColumn("AVG(review.rating)", "avg_rating")
        ->groupBy("review.rating")
        ->find()

现在在调试器中我看到这个：

result = {PropelRuntimeCollectionObjectCollection} [7]
 index = {array} [1]
 indexSplHash = {array} [1]
 model = "Food"
 fullyQualifiedModel = Food
 formatter = {PropelRuntimeFormatterObjectFormatter} [10]
 data = {array} [1]
  0 = {Food} [34]
   new = false
   deleted = false
   modifiedColumns = {array} [0]
   virtualColumns = {array} [1]
    avg_rating = "5.0000"
   id = 15
   name = "Salát Caesar"
   collReviews = {PropelRuntimeCollectionObjectCollection} [7]
    index = {array} [2]
    indexSplHash = {array} [2]
    model = "Review"
    fullyQualifiedModel = "Review"
    formatter = null
    data = {array} [2]
     0 = {Review} [14]
      new = false
      deleted = false
      modifiedColumns = {array} [0]
      virtualColumns = {array} [0]
      user_id = 1
      food_id = 15
      rating = 3
      aFood = {Food} [34]
      aUser = null
      alreadyInSave = false
      reviewThumbsUpsScheduledForDeletion = null
     1 = {Review} [14]
      new = false
      deleted = false
      modifiedColumns = {array} [0]
      virtualColumns = {array} [0]
      user_id = 3
      food_id = 15
      rating = 5
      aFood = {Food} [34]
      aUser = null
      alreadyInSave = false
      reviewThumbsUpsScheduledForDeletion = null
    *PropelRuntimeCollectionCollection*pluralizer = null
   collReviewsPartial = false
   alreadyInSave = false
   reviewsScheduledForDeletion = null
 *PropelRuntimeCollectionCollecti4

问题是平均评级不正确。在虚拟列中，您可以看到值为"5.0000"avg_rating字段。但是当你看得更低一点时，你实际上可以看到这种食物有 2 条评论，评分分别为 3 和 5，所以平均值应该是"4.0000"。

问题出在哪里？为什么这不能正常工作？

Answer 1

您当前正在复习表上进行左联接。左联接将导致返回多行（每条评论一行）。对于返回的每一行，只有一条评论，因此"平均值"将与该行的评论分数相同。按平均值分组不会做太多事情，它只会对分数完全相同的评论进行分组，这不是您要找的。

您应该先按您的食品 ID 分组。然后，您将能够为每个食品项目获得一行，并具有平均评论分数。

$food = FoodQuery::create()
    ->filterById(15) // constant for testing purposes only
    ->groupById()
    ->leftJoinWithReview()
    ->withColumn("AVG(review.rating)", "avg_rating")
    ->find();