lampager/lampager-doctrine2

Rapid pagination for Doctrine 2

v0.2.2 2021-06-24 12:03 UTC

Requires

• php: ^5.6 || ^7.0 || ^8.0
• doctrine/orm: ^2.4.5
• lampager/lampager: ^0.4

Requires (Dev)

• ext-json: *
• doctrine/cache: ^1.11
• friendsofphp/php-cs-fixer: ^3.0
• nilportugues/sql-query-formatter: ^1.2.2
• php-coveralls/php-coveralls: ^1.0
• phpunit/phpunit: ^6.4

MIT

• mpyw

• symfony
• paginator
• pagination
• doctrine
• limit
• offset

README

Lampager for Doctrine 2

Rapid pagination for Doctrine 2

Requirements

• PHP: ^5.6 || ^7.0 || ^8.0
• doctrine/orm: ^2.4.5
• lampager/lampager: ^0.4

Matrix

Installing

composer require lampager/lampager-doctrine2

Usage

Basic

Instantiate your QueryBuilder to create the Paginator.

$cursor = [
    'p.id' => 3,
    'p.createdAt' => '2017-01-10 00:00:00',
    'p.updatedAt' => '2017-01-20 00:00:00',
];

$result = Paginator::create(
    $entityManager
        ->getRepository(Post::class)
        ->createQueryBuilder('p')
        ->where('p.userId = :userId')
        ->setParameter('userId', 1)
)
    ->forward()
    ->setMaxResults(5) // Or ->limit(5)
    ->orderByDesc('p.updatedAt') // ORDER BY p.updatedAt DESC, p.createdAt DESC, p.id DESC
    ->orderByDesc('p.createdAt')
    ->orderByDesc('p.id')
    ->paginate($cursor);

It will run the optimized DQL.

SELECT * FROM App\Entities\Post p
WHERE p.userId = 1
AND (
    p.updatedAt = '2017-01-20 00:00:00' AND p.createdAt = '2017-01-10 00:00:00' AND p.id <= 3
    OR
    p.updatedAt = '2017-01-20 00:00:00' AND p.createdAt < '2017-01-10 00:00:00'
    OR
    p.updatedAt < '2017-01-20 00:00:00'
)
ORDER BY p.updatedAt DESC, p.createdAt DESC, p.id DESC
LIMIT 6

And you'll get

object(Lampager\PaginationResult)#X (5) {
  ["records"]=>
  array(5) {
    [0]=>
    object(App\Entities\Post)#X (5) {
      ["id"]=>
      int(3)
      ["userId"]=>
      int(1)
      ["text"]=>
      string(3) "foo"
      ["createdAt"]=>
      object(DateTimeImmutable)#X (3) {
        ["date"]=>
        string(26) "2017-01-10 00:00:00.000000"
        ["timezone_type"]=>
        int(3)
        ["timezone"]=>
        string(3) "UTC"
      }
      ["updatedAt"]=>
      object(DateTimeImmutable)#X (3) {
        ["date"]=>
        string(26) "2017-01-20 00:00:00.000000"
        ["timezone_type"]=>
        int(3)
        ["timezone"]=>
        string(3) "UTC"
      }
    }
    [1]=> ...
    [2]=> ...
    [3]=> ...
    [4]=> ...
  }
  ["hasPrevious"]=>
  bool(false)
  ["previousCursor"]=>
  NULL
  ["hasNext"]=>
  bool(true)
  ["nextCursor"]=>
  array(2) {
    ["p.updatedAt"]=>
    object(DateTimeImmutable)#X (3) {
      ["date"]=>
      string(26) "2017-01-18 00:00:00.000000"
      ["timezone_type"]=>
      int(3)
      ["timezone"]=>
      string(3) "UTC"
    }
    ["p.createdAt"]=>
    object(DateTimeImmutable)#X (3) {
      ["date"]=>
      string(26) "2017-01-14 00:00:00.000000"
      ["timezone_type"]=>
      int(3)
      ["timezone"]=>
      string(3) "UTC"
    }
    ["id"]=>
    int(6)
  }
}

Advanced: Provide mapping for aliased columns and switch to different Hydration Mode

$result = Paginator::create(
    $entityManager
        ->getRepository(Post::class)
        ->createQueryBuilder('p')
        ->select('p.id as postId, p.userId as authorUserId, p.createdAt, p.updatedAt') // Aliasing
        ->where('p.userId = :userId')
        ->setParameter('userId', 1)
)
    ->forward()
    ->setMaxResults(5)
    ->orderByDesc('p.updatedAt')
    ->orderByDesc('p.createdAt')
    ->orderByDesc('p.id')
    ->setMapping([
        'p.id' => 'postId',
        'p.userId' => 'authorUserId',
    ]) // Mapping
    ->paginate($cursor, Query::HYDRATE_ARRAY); // Hydration Mode

Questions

Seekable(Bidirectional) Query?

Sorry you can't use seekable mode since Doctrine DQL does not support UNION ALL syntax. 😢

How about Tuple Comparison?

Doctrine DQL does not support Tuple Comparison syntax! 😢

Classes

Note: See also lampager/lampager.

API

Note: See also lampager/lampager.

Paginator::__construct()
Paginator::create()

Create a new paginator instance.

static Paginator create(\Doctrine\ORM\QueryBuilder $builder): static
Paginator::__construct(\Doctrine\ORM\QueryBuilder $builder)

Paginator::setMapping()

Paginator::setMapping(string[] $mapping): $this

Arguments

• (string[]) $mapping
  An associative array that contains $columnNameOrCursorKey => $fetchedFieldName.

Paginator::aggregated()

Paginator::aggregated(bool $aggregated = true): $this

Declare that HAVING should be used instead of WHERE for aggregation.

Paginator::setMaxResults()

Alias for \Lampager\Paginator::limit().

Paginator::setMaxResults(int $limit): $this

Paginator::transform()

Transform Lampager Query into Doctrine Query.

Paginator::transform(\Lampager\Query $query): \Doctrine\ORM\Query

Paginator::build()

Perform configure + transform.

Paginator::build(\Lampager\Contracts\Cursor|array $cursor = []): \Doctrine\ORM\Query

Paginator::paginate()

Perform configure + transform + process.

Paginator::paginate(\Lampager\Contracts\Cursor|array $cursor = []): \Lampager\PaginationResult

Arguments

• (mixed) $cursor
  An associative array that contains $column => $value or an object that implements \Lampager\Contracts\Cursor. It must be all-or-nothing.
  • For initial page, omit this parameter or pass empty array.
  • For subsequent pages, pass all parameters. Partial parameters are not allowd.