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

Question

我有两个表:食物(id，名称)，评论(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 = {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中，您可以看到值为"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();