dsfdfd1211
2015-04-07 22:00
浏览 74
已采纳

使用Symfony Doctrine查询2个表的平均值

First of all, excuse me if my english isn't too good.

I'm using Symfony 2.6 with Doctrine as ORM. The project is running on Apache + MySQL.

I have 2 entities in my DB: book and rating. I'm building a system where the user can rate books from 0 to 100.

My entities:

  • Book: id, other unrelated fields
  • Rating: id, book_id, score, other unrelated fields

Every book can have many ratings but a rating can only be related to a single book (one to many mapping).

My goal is to get the top 5 books ordered by the average score of their ratings.

Example:

  • Book 1: 94.5
  • Book 2: 93.3
  • Book 3: 89.2
  • Book 4: 85.2
  • Book 5: 79.6

I already tried to get the result using a double for loop with the two entities, but according to the Symfony profiler, it took 3.7 seconds and required 1001 queries, wich is higly inefficient. I'm very sure it could be done in a better way using the DQL, so...

¿Which may be the most efficient way to achieve my goal (if possible in a single query)?

Thanks in advance.

EDIT

Thanks to MikO for the push in the right direction. Is required to write the complete entity (like AcmeBundle:Book) in the FROM and JOIN sentences for it to work. Also, you can't use LIMIT in a querybuilder, so after the query is built and before calling results, I had to use setMaxResults(5) in order to retrieve only the top 5 books.

Thanks very much.

图片转代码服务由CSDN问答提供 功能建议

首先,如果我的英语不太好,请原谅。 < 我正在使用Symfony 2.6和Doctrine作为ORM。 该项目在Apache + MySQL上运行。

我的数据库中有2个实体:书籍和评级。 我正在构建一个系统,用户可以将书籍从0到100评级。

我的实体:

  • 书籍:id ,其他不相关的字段
  • 评级:id,book_id,得分,其他不相关的字段

    每本书都可以有很多评级但是评分 只能与一本书(一对多映射)相关。

    我的目标是按照评分的平均分数排序前5本书。

    示例:

    • 第1册:94.5
    • 第2册:93.3
    • 预订 3:89.2
    • 书4:85.2
    • 书5:79.6

      我已经尝试过 使用带有两个实体的double for循环获得结果,但根据Symfony分析器,它需要3.7秒并且需要1001个查询,这是非常低效的。 我非常确定可以使用DQL以更好的方式完成,所以......

      ¿这可能是实现我的目标的最有效方式(如果可能的话 查询)?

      提前致谢。

      编辑

      感谢MikO的推动 方向。 需要在FROM和JOIN语句中编写完整的实体(如AcmeBundle:Book)才能工作。 此外,您不能在查询构建器中使用LIMIT,因此在构建查询之后和调用结果之前,我必须使用setMaxResults(5)才能仅检索前5本书。 < 非常感谢。

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新

  • douhui0975 2015-04-07 23:16
    最佳回答

    I haven't actually tried this, but I'd create a custom repository class for your Book entity, and then I'd write something like this in it:

    public function getTopRatedBooks()
    {
        $em = $this->getEntityManager();
    
        $query = $em->createQuery('
          SELECT book.id, book.name, AVG(rating.score) as avg_rating
          FROM book 
          JOIN rating
          WHERE book.id = rating.book_id
          GROUP BY book.id
          ORDER BY avg_rating DESC
        ');
    
        $result = $query->getResult();
    
        return $result;
    }
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题