1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161
|
<?php
declare(strict_types=1);
namespace Doctrine\Tests\ORM\Tools\Pagination;
use Doctrine\ORM\Query;
use Doctrine\ORM\Tools\Pagination\LimitSubqueryWalker;
use Doctrine\ORM\Tools\Pagination\Paginator;
use PHPUnit\Framework\Attributes\Group;
use PHPUnit\Framework\Attributes\RequiresPhpunit;
#[Group('DDC-1613')]
#[RequiresPhpunit('< 12')]
class LimitSubqueryWalkerTest extends PaginationTestCase
{
public function testLimitSubquery(): void
{
$dql = 'SELECT p, c, a FROM Doctrine\Tests\ORM\Tools\Pagination\MyBlogPost p JOIN p.category c JOIN p.author a';
$query = $this->entityManager->createQuery($dql);
$limitQuery = clone $query;
$limitQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [LimitSubqueryWalker::class]);
self::assertEquals(
'SELECT DISTINCT m0_.id AS id_0 FROM MyBlogPost m0_ INNER JOIN Category c1_ ON m0_.category_id = c1_.id INNER JOIN Author a2_ ON m0_.author_id = a2_.id',
$limitQuery->getSQL(),
);
}
public function testHintCanDisableDistinct(): void
{
$dql = 'SELECT p, c, a FROM Doctrine\Tests\ORM\Tools\Pagination\MyBlogPost p JOIN p.category c JOIN p.author a';
$query = $this->entityManager->createQuery($dql);
$limitQuery = clone $query;
$limitQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [LimitSubqueryWalker::class]);
$limitQuery->setHint(Paginator::HINT_ENABLE_DISTINCT, false);
self::assertEquals(
'SELECT m0_.id AS id_0 FROM MyBlogPost m0_ INNER JOIN Category c1_ ON m0_.category_id = c1_.id INNER JOIN Author a2_ ON m0_.author_id = a2_.id',
$limitQuery->getSQL(),
);
}
public function testLimitSubqueryWithSort(): void
{
$dql = 'SELECT p, c, a FROM Doctrine\Tests\ORM\Tools\Pagination\MyBlogPost p JOIN p.category c JOIN p.author a ORDER BY p.title';
$query = $this->entityManager->createQuery($dql);
$limitQuery = clone $query;
$limitQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [LimitSubqueryWalker::class]);
self::assertEquals(
'SELECT DISTINCT m0_.id AS id_0, m0_.title AS title_1 FROM MyBlogPost m0_ INNER JOIN Category c1_ ON m0_.category_id = c1_.id INNER JOIN Author a2_ ON m0_.author_id = a2_.id ORDER BY m0_.title ASC',
$limitQuery->getSQL(),
);
}
public function testLimitSubqueryWithSortFunction(): void
{
$dql = 'SELECT p FROM Doctrine\Tests\ORM\Tools\Pagination\MyBlogPost p JOIN p.category c GROUP BY p.id ORDER BY COUNT(c.id)';
$query = $this->entityManager->createQuery($dql);
$limitQuery = clone $query;
$limitQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [LimitSubqueryWalker::class]);
self::assertSame(
'SELECT DISTINCT m0_.id AS id_0 FROM MyBlogPost m0_ INNER JOIN Category c1_ ON m0_.category_id = c1_.id GROUP BY m0_.id ORDER BY COUNT(c1_.id) ASC',
$limitQuery->getSQL(),
);
}
public function testCountQueryMixedResultsWithName(): void
{
$dql = 'SELECT a, sum(a.name) as foo FROM Doctrine\Tests\ORM\Tools\Pagination\Author a';
$query = $this->entityManager->createQuery($dql);
$limitQuery = clone $query;
$limitQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [LimitSubqueryWalker::class]);
self::assertEquals(
'SELECT DISTINCT a0_.id AS id_0 FROM Author a0_',
$limitQuery->getSQL(),
);
}
public function testAggQueryMixedResultsWithNameAndSort(): void
{
$dql = 'SELECT a, sum(a.name) as foo FROM Doctrine\Tests\ORM\Tools\Pagination\Author a ORDER BY foo DESC';
$query = $this->entityManager->createQuery($dql);
$limitQuery = clone $query;
$limitQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [LimitSubqueryWalker::class]);
self::assertSame(
'SELECT DISTINCT a0_.id AS id_0, sum(a0_.name) AS sclr_1 FROM Author a0_ ORDER BY sclr_1 DESC',
$limitQuery->getSQL(),
);
}
public function testAggQueryMultipleMixedResultsWithSort(): void
{
$dql = 'SELECT a, sum(a.name) as foo, (SELECT count(subA.id) FROM Doctrine\Tests\ORM\Tools\Pagination\Author subA WHERE subA.id = a.id ) as bar FROM Doctrine\Tests\ORM\Tools\Pagination\Author a ORDER BY foo DESC, bar ASC';
$query = $this->entityManager->createQuery($dql);
$limitQuery = clone $query;
$limitQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [LimitSubqueryWalker::class]);
self::assertSame(
'SELECT DISTINCT a0_.id AS id_0, sum(a0_.name) AS sclr_1, (SELECT count(a1_.id) AS sclr_3 FROM Author a1_ WHERE a1_.id = a0_.id) AS sclr_2 FROM Author a0_ ORDER BY sclr_1 DESC, sclr_2 ASC',
$limitQuery->getSQL(),
);
}
#[Group('DDC-2890')]
public function testLimitSubqueryWithSortOnAssociation(): void
{
$dql = 'SELECT p FROM Doctrine\Tests\ORM\Tools\Pagination\MyBlogPost p ORDER BY p.author';
$query = $this->entityManager->createQuery($dql);
$limitQuery = clone $query;
$limitQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [LimitSubqueryWalker::class]);
self::assertEquals(
'SELECT DISTINCT m0_.id AS id_0, m0_.author_id AS sclr_1 FROM MyBlogPost m0_ ORDER BY m0_.author_id ASC',
$limitQuery->getSQL(),
);
}
/**
* Arbitrary Join
*/
public function testLimitSubqueryWithArbitraryJoin(): void
{
$dql = 'SELECT p, c FROM Doctrine\Tests\ORM\Tools\Pagination\MyBlogPost p JOIN Doctrine\Tests\ORM\Tools\Pagination\Category c WITH p.category = c';
$query = $this->entityManager->createQuery($dql);
$limitQuery = clone $query;
$limitQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [LimitSubqueryWalker::class]);
self::assertEquals(
'SELECT DISTINCT m0_.id AS id_0 FROM MyBlogPost m0_ INNER JOIN Category c1_ ON (m0_.category_id = c1_.id)',
$limitQuery->getSQL(),
);
}
public function testLimitSubqueryWithSortWithArbitraryJoin(): void
{
$dql = 'SELECT p, c FROM Doctrine\Tests\ORM\Tools\Pagination\MyBlogPost p JOIN Doctrine\Tests\ORM\Tools\Pagination\Category c WITH p.category = c ORDER BY p.title';
$query = $this->entityManager->createQuery($dql);
$limitQuery = clone $query;
$limitQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [LimitSubqueryWalker::class]);
self::assertEquals(
'SELECT DISTINCT m0_.id AS id_0, m0_.title AS title_1 FROM MyBlogPost m0_ INNER JOIN Category c1_ ON (m0_.category_id = c1_.id) ORDER BY m0_.title ASC',
$limitQuery->getSQL(),
);
}
}
|