Doctrine2:使用左连接/分页进行限制 - 最佳实践 [英] Doctrine2: Limiting with Left Joins / Pagination - Best Practice

问题描述

我有一个大查询(在我的查询生成器中)和很多左连接.所以我得到带有评论和标签等的文章.假设我有以下 dql:

i have a big query (in my query builder) and a lot of left joins. So i get Articles with their comments and tags and so on. Let's say i have the following dql:

$dql = 'SELECT blogpost, comment, tags 
FROM BlogPost blogpost 
LEFT JOIN blogpost.comments comments
LEFT JOIN blogpost.tags tags';

现在假设我的数据库有 100 多篇博文，但我只想要前 10 篇，但包含这 10 篇的所有评论及其所有标签(如果存在).如果我使用 setMaxResults 它会限制行数.所以我可能会收到前两个帖子，但最后一个缺少一些评论或标签.所以followin不起作用.

Now let's say my database has more than 100 blogposts but i only want the first 10, but with all the comments of those 10 and all their tags, if they exist. If i use setMaxResults it limits the Rows. So i might get the first two Posts, but the last one of those is missing some of it's comments or tags. So the followin doesn't work.

$result = $em->createQuery($dql)->setMaxResults(15)->getResult();

使用几乎没有文档记录的 分页 随学说 2.2 提供的解决方案对我来说也不是很有效，因为它太慢了，我也可以加载所有数据.

Using the barely documented Pagination Solution that ships with doctrine2.2 doesn't really work for me either since it is so slow, i could as well load all the data.

我尝试了 Stackoverflow 文章中的解决方案，但即使该文章仍然缺少最佳实践和提出的解决方案速度非常慢.

I tried the Solutions in the Stackoverflow Article, but even that Article is still missing a Best Practise and the presented Solution is deadly slow.

没有关于如何做到这一点的最佳做法吗?没有人在生产模式下使用 Doctrine2.2 吗?

Isn't there a best practise on how to do this? Is nobody using Doctrine2.2 in Production mode?

推荐答案

使用这样的查询获得正确的结果是有问题的.Doctrine 网站上有一个教程解释了这个问题.

Getting the proper results with a query like this is problematic. There is a tutorial on the Doctrine website explaining this problem.

分页

本教程更多的是关于分页而不是获得前 5 个结果，但总体思路是您需要执行SELECT DISTINCT a.id FROM article a ... LIMIT 5"而不是普通的 SELECT.它比这更复杂一些，但该教程中的最后 2 点应该会让您走上正确的轨道.

The tutorial is more about pagination rather than getting the top 5 results, but the overall idea is that you need to do a "SELECT DISTINCT a.id FROM articles a ... LIMIT 5" instead of a normal SELECT. It's a little more complicated than this, but the last 2 points in that tutorial should put you on the right track.

更新:

这里的问题不是 Doctrine 或任何其他 ORM.问题完全在于数据库能够返回您要求的结果.这就是连接的工作原理.

The problem here is not Doctrine, or any other ORM. The problem lies squarely on the database being able to return the results you're asking for. This is just how joins work.

如果您对查询进行 EXPLAIN，它将为您提供有关正在发生的事情的更深入的答案.最好将结果添加到您的初始问题中.

If you do an EXPLAIN on the query, it will give you a more in depth answer of what is happening. It would be a good idea to add the results of that to your initial question.

根据分页文章中讨论的内容，您似乎需要至少 2 个查询才能获得所需的结果.将 DISTINCT 添加到查询中可能会显着减慢查询速度，但只有在其中有联接时才真正需要它.您可以编写另一个查询，只检索按创建日期排序的前 10 个帖子，而不需要连接.获得这 10 个帖子的 ID 后，使用您的连接和 WHERE blogpost.id IN (...) ORDER BY blogpost.created 执行另一个查询.这种方法应该效率更高.

Building on what is discussed in the Pagination article, it would appear that you need at least 2 queries to get your desired results. Adding DISTINCT to a query has the potential to dramatically slow down your query, but its only really needed if you have joins in it. You could write another query that just retrieves the first 10 posts ordered by created date, without the joins. Once you have the IDs of those 10 posts, do another query with your joins, and a WHERE blogpost.id IN (...) ORDER BY blogpost.created. This method should be much more efficient.

SELECT 
    bp 
FROM 
    Blogpost bp 
ORDER BY 
    bp.created DESC
LIMIT 10

由于您在第一个查询中只关心 ID，因此您可以将 Doctrine 设置为使用 Scalar Hydration.

Since all you care about in the first query are the IDs, you could set Doctrine to use Scalar Hydration.

SELECT 
    bg 
FROM 
    Blogpost bp 
LEFT JOIN 
    bp.comments c 
LEFT JOIN 
    bp.tags t 
WHERE 
    bp.id IN (...) 
ORDER BY 
    bp.created DESC

您也可以使用相关子查询在一个查询中执行此操作.子查询总是不好的神话是不正确的.有时它们比连接更快.您需要进行试验以找出最适合您的解决方案.

You could also probably do it in one query using a correlated subquery. The myth that subqueries are always bad is NOT true. Sometimes they are faster than joins. You will need to experiment to find out what the best solution is for you.

这篇关于Doctrine2:使用左连接/分页进行限制 - 最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！