教义2：限制左连接/分页 - 最佳实践 [英] Doctrine2: Limiting with Left Joins / Pagination - Best Practice

问题描述

我有一个很大的查询（在我的查询生成器）和很多左连接。所以我得到文章与他们的评论和标签等等。
假设我有以下dql：

  $ dql ='SELECT blogpost，comment，tags 
从BlogPost blogpost 
 LEFT JOIN blogpost.comments评论
 LEFT JOIN blogpost.tags tags'; 
  

现在我们的数据库有100多个博客，但是我只想要前10个，那些10的评论和他们所有的标签，如果它们存在。
如果我使用setMaxResults，它会限制行。所以我可能得到前两个帖子，但最后一个帖子缺少一些评论或标签。因此，followin不起作用。

  $ result = $ em-> createQuery（$ dql） - > setMaxResults 15） - >的getResult（）; 
  

使用勉强记录的分页随着doctrine2.2的解决方案对我来说并不真正有效，因为它太慢了，我可以加载所有数据。

我在 Stackoverflow中尝试过解决方案文章，但即使该条款仍然缺少最佳实践，并且所提出的解决方案是致命的缓慢。

是否有最佳做法如何做这个？
在生产模式下没有人使用Doctrine2.2？

解决方案

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

分页

本教程更多关于分页，而不是获得前5个结果，但总体的想法是，您需要执行SELECT DISTINCT a.id FROM articles a ... LIMIT 5而不是正常的SELECT。这比这更复杂一点，但是该教程的最后两点应该让你走上正轨。

更新： p>

这里的问题不是Doctrine或任何其他ORM。问题在于数据库能够返回您要求的结果。这只是连接如何工作。

如果您对查询执行了EXPLAIN，它将为您提供更深入的回答。将其结果添加到您的初始问题是一个好主意。

根据分页文章中讨论的内容，您至少需要2个查询以获得您期望的结果。将DISTINCT添加到查询中可能会大大减慢查询速度，但是如果您在其中加入了它，那么它才是真正需要的。您可以编写另一个查询，只需检索按创建日期排序的前10个帖子，而不使用连接。一旦你拥有这10个帖子的ID，你可以使用你的连接进行另一个查询，一个 WHERE blogpost.id IN（...）ORDER BY blogpost.created 。这种方法应该更有效率。

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

你关心的第一个查询是ID，你可以设置Doctrine来使用标量水合。

  SELECT 
 bg 
 FROM 
 Blogpost bp 
 LEFT JOIN 
 bp.comments c 
 LEFT JOIN 
 bp.tags t 
 WHERE 
 bp。 id IN（...）
 ORDER BY 
 bp.created DESC 
  

您也可以使用相关的子查询在一个查询中执行此操作。子查询总是坏的神话不是真的。有时候他们比加入速度要快。您将需要尝试找出最适合您的解决方案。

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';

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();

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.

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.

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

解决方案

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

Pagination

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.

Update:

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.

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.

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

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.

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