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

Question

我有两个表:食物(id，名称)，评论(user_id，food_id，评级)。现在我想将食物表与评论表连接起来，并向食物表添加虚拟列，名称为 avg_ rating，这显然会保存基于评论的食物的平均评分值。所以我的想法是做这样的事情:

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

现在在调试器中我看到了这个:

result = {Propel\Runtime\Collection\ObjectCollection} [7]
 index = {array} [1]
 indexSplHash = {array} [1]
 model = "Food"
 fullyQualifiedModel = \Food
 formatter = {Propel\Runtime\Formatter\ObjectFormatter} [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 = {Propel\Runtime\Collection\ObjectCollection} [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
    *Propel\Runtime\Collection\Collection*pluralizer = null
   collReviewsPartial = false
   alreadyInSave = false
   reviewsScheduledForDeletion = null
 *Propel\Runtime\Collection\Collecti4

问题是平均评分不正确。在 virtualColumns 中，您可以看到 avg_ rating 字段的值为“5.0000”。但是当你稍微往下看时，你实际上可以看到这个食物有 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();