Symfony2来自查询的错误响应，包含maxresults和firstresult

I've meet a serious problem with setMaxResult and setFirstResult.

我遇到了setMaxResult和setFirstResult的严重问题。

When i'm trying to get result without setMaxResults and setFirstResult, it works OK, all rows returned.

当我试图在没有setMaxResults和setFirstResult的情况下获得结果时,它可以正常工作,返回所有行。

When i'm using offset = 0 and limit=10 , it works good, 10 rows returned.

当我使用offset = 0和limit = 10时,它运行良好,返回10行。

When i'm using offset = 10 and limit = 10 , it return 5 rows (must be 7)

当我使用offset = 10和limit = 10时,它返回5行(必须是7行)

Another example, i've used offset = 0 , limit = 20 ,it returned 15 rows.But it must be 17 rows.

另一个例子,我使用了offset = 0,limit = 20,它返回了15行。但它必须是17行。

With offset=0 and limit = 30 , it returned all 17 rows .... Why this query works so bad ? With offset = 0 and limit 20, it should have returned all 17 rows... but not 15..

使用offset = 0和limit = 30,它返回所有17行....为什么这个查询工作得那么糟糕?对于offset = 0和limit 20,它应该返回所有17行......但不是15 ..

Code :

$eligibleCircles = $this->getAllCircles($user);
$results = $this->getEntityManager()
    ->createQuery(
        'SELECT
            e
        FROM
            TestBundle:Event e
        LEFT JOIN
            e.eligibleCircles eligibleCircles
        WHERE
            (
                eligibleCircles in (:eligibleCircles)
                OR
                e.owner = :user
            )
                AND
                e.eventStatus = :eventStatus
                AND
                NOT EXISTS (
                    SELECT
                    eh
                    FROM
                    TestBundle:EventHidden eh
                    WHERE
                        eh.user = :user
                        AND
                        eh.event = e
                    )
        AND
            e.startDate < :currentDate
        ORDER BY e.startDate DESC
        '
    )
    ->setParameter('eventStatus', 3)
    ->setParameter('eligibleCircles', $eligibleCircles )
    ->setParameter('user', $user )
    ->setParameter('currentDate', new \DateTime('now') )
    ->setFirstResult($offset)
    ->setMaxResults($limitNr)
    ->getResult();

1 个解决方案

#1

Cerad is correct with regard to the sql-limit not being useful when you have a join in your query.

对于在查询中加入连接时sql-limit无效,Cerad是正确的。

If you want to paginate while using Doctrine2 there are some helpfull tools for you available.

如果您想在使用Doctrine2时进行分页,可以使用一些有用的工具。

Have a look at the documentation here: http://doctrine-orm.readthedocs.org/en/latest/tutorials/pagination.html

看看这里的文档:http://doctrine-orm.readthedocs.org/en/latest/tutorials/pagination.html

You need a bit of extra code, but most of the complex stuff is handled for you. It will also require 1 or 2 additional queries to find the correct records+data. This may or may not be a problem in your situation.

您需要一些额外的代码,但大多数复杂的东西都是为您处理的。它还需要1或2个额外查询才能找到正确的记录+数据。在您的情况下,这可能是也可能不是问题。

#1