Orm: Doctrine Pagination With Left Join (ArrayCollection) NOT working correctly

[ibekiaris]

Hi there. I have the following query:

   $qb->select(array('c', 'd'))
        ->from($this->_rps('Client'), 'c')
        ->where('c.account = :account');

    if ($searchTerm) {
        $qb->andWhere('c.title LIKE :term');
        $qb->setParameter('term', '%' . $searchTerm . '%');
    }

    $qb->leftJoin('c.domains', 'd');
    $qb->setFirstResult($offset)->setMaxResults($limit);
    $qb->setParameter('account', $account);

    $query = $qb->getQuery();
    $paginator = new Paginator($query, false);

    return $paginator;

This is not working correctly. Only if I remove left join everything works fine. I have also tried new Paginator($query, true)...

I there any effective way to work with that returning always a paginator object?

Thank you in advance

[Ocramius]

As already asked in doctrine/DoctrineORMModule#446, please provide more detail about the expected results (compared to actual results)

[ibekiaris]

Limit and Offset is not working in my case when left join domains array collection.

Having four clients in my db using offset 0 and limit 10 (when join) I get 3(!) results. Removing left join everything works perfect

As for the entities in case you need them:

Client {
    /**
     * @ORM\OneToMany(targetEntity="Domain", mappedBy="client")
     *
     * @var ArrayCollection
     */
    protected $domains;
}

Domain {
     /**
     * @ORM\ManyToOne(targetEntity="Client", inversedBy="domains")
     * @ORM\JoinColumn(name="client_id", referencedColumnName="id", onDelete="CASCADE")
     *
     * @var Client
     */
    protected $client;
}

[Ocramius]

@ibekiaris remove that false from the second constructor parameter of the Paginator

[ibekiaris]

I did no result

[Ocramius]

@ibekiaris can you check what SQL is being generated by the paginator, and how many results are found by it? Overall, this looks like a case where you have 3 Client objects and 10 associated Domain objects that get hydrated into the Client: therefore the paginator limit is causing the reduce amount of records.

[ibekiaris]

SELECT ..
FROM clients c0_ LEFT JOIN domains d1_ ON c0_.id = d1_.client_id WHERE c0_.account_id = ? LIMIT 10 OFFSET 0

[aimfeld]

I have a similar problem, I can't limit my results. I always get all users for my user paginator.

My code looks something like this:

use Doctrine\ORM\Tools\Pagination\Paginator as OrmPaginator;
use DoctrineORMModule\Paginator\Adapter\DoctrinePaginator;
use Zend\Paginator\Paginator;

$select = $this->db->getUserRepo()
    ->createQueryBuilder('user')
    ->orderBy('user.id', 'DESC');

$select->setMaxResults($limit); // Does not work

$paginator = new Paginator(new DoctrinePaginator(new OrmPaginator($select)));

$paginator->setCurrentPageNumber($pageNumber);

In my case, it doesn´t matter if I left-join other entities to the users.

[juananrey]

Hello all,
I think this problem is just solved from Doctrine 2.5.0:
https://groups.google.com/forum/#!topic/doctrine-user/AZyudulmXWE

Basically there was another programmer facing this exact same problem , and in the thread, @Ocramius just answered him that it should be solved in 2.5.0 release and the programmer replied that problem just vanished.

I can't reproduce it or make a 100% assertion as I am using PHP < 5.4 still, but it would be just perfect if @Ocramius can tell us the changes that made Pagination just work as expected.

[Ocramius]

Would need to add a test case to master and 2.5.x to validate that. I can't just "tell" you :-)

[aimfeld]

I upgraded doctrine/doctrine-orm-module from 1.0.0 to 1.1.0, which installed doctrine/orm v2.5.5. However, my problem mentioned above still persists.

[mdzzohrabi]

I have same issue with Doctrine Pagination that used in one of my symfony bundle's ( APY\DataGridBundle ) and pagination throws "The Paginator does not support Queries which only yield ScalarResults." exception in doctrine v2.5. and before updating to v2.5 it was throws "Not all identifier properties can be found in the ResultSetMapping: id".
DQL generated by Grid bundle is :

SELECT _a.id, _a.subject, _user.id as user::id, _a.createdDate, _a.status, _a.closed FROM SupportBundle\Entity\Ticket _a LEFT JOIN _a.user _user ORDER BY _a.createdDate desc

[amirduran]

Any news regarding this bug? I'm facing exactly the same problem.

[Ocramius]

@amirduran consider providing an isolated test case to be added to the ORM test suite.

[tklaas]

@amirduran
try groupBy the entity you want to setMaxResults on

[adiq]

Bump, this issue still occurs.
Will try to provide an isolated test in upcoming weeks, when I find a moment of spare time.

[ArtiomRudenco]

Dear,
I have got the same bug with Paginator.
I use ZF3, Doctrine ORM.

Controller:

protected function getListPaginator()
{
    $currentUser = $this->auth->getCurrentUser();
    $query = $this->bannerStatusDomain->getBannerStatuses($currentUser);
    $adapter = new DoctrineAdapter(new ORMPaginator($query, false));
    $paginator = new Paginator($adapter);
    $page = $this->params()->fromQuery('page');
    $paginator->setItemCountPerPage(10);
    $paginator->setCurrentPageNumber($page);
    if ($page > $paginator->count()) {
        $paginator->setCurrentPageNumber(1);
    }
    return $paginator;
}

...........................................................................................................................

BannersDomain:

protected $entityName = Banner::class;
/**
 * @param      $filter
 *
 * @param null|string $hydrationMode
 *
 * @return \Doctrine\ORM\Query
 */
public function getBanners($filter, $hydrationMode = NULL)
{
    $qb = $this->getRepository()->createQueryBuilder('banners');

    $qb->addSelect('banners', 'category', 'city', 'images', **'bannerStatuses'**)
            ->leftJoin('banners.category', 'category')
            ->leftJoin('banners.city', 'city')
            ->leftJoin('banners.images', 'images')
            -**>leftJoin('banners.bannerStatuses', 'bannerStatuses')**
            ->addOrderBy('banners.city')
            ->addOrderBy('banners.area');

    if ($filter['city'] != null) {
        $qb->where('banners.city = :city')
                ->setParameter('city', $filter['city']);
    }
    if ($filter['category'] != null) {
        $qb->andwhere('banners.category = :category')
                ->setParameter('category', $filter['category']);
    }
    if ($filter['id']   != null) {
        $qb->andwhere('banners.id = :id')
                ->setParameter('id', $filter['id']);
    }
    if ($filter['currentUserRole'] !== NULL
        AND $filter['currentUserRole'] !== 'Administrator') {
        $qb->andwhere('banners.userId = :userId')
            ->setParameter('userId', $filter['currentUserId']);
    }
    $query = $qb->getQuery();
    if (is_null($hydrationMode)) {
        $hydrationMode = Query::HYDRATE_ARRAY;
    }
    $query->setHydrationMode($hydrationMode);
    return $query;
}

...........................................................................................................................

and
View:

    <?php $i = $this->paginator->getAbsoluteItemNumber($relativeItemNumber, $pageNumber);?>
    <?php foreach ($this->paginator as $banner) : ?>
    <td><?php  echo $i++; ?></td>
    <td><?= $this->escapeHtml($banner['category']['name']) ?></td>
    <td><?= $this->escapeHtml($banner['city']['nameCity']) ?></td>
    <td><?= $this->escapeHtml($banner['city']['nameRegion']) ?></td>
    <td><?= $this->escapeHtml($banner['area']) ?></td>
    <td><?= $this->escapeHtml($banner['adress']) ?></td>
    <td><?= $this->escapeHtml($banner['image']) ?></td>
    <td><?= $this->escapeHtml($banner['description']) ?></td>
    <td>
        <a class="btn btn-success btn-sm glyphicon glyphicon-cog"
           title="<?= $this->translate('Update Banner') ?>"
           href="<?= $this->url('admin', ['controller' => 'banners', 'action' => 'update'], ['query' => ['id' => $banner['id']]]); ?>">
        </a>
        <a class="btn btn-danger btn-ajax-confirmation btn-sm glyphicon glyphicon-remove"
           data-toggle="tooltip" data-placement="top" title="<?= $this->translate('Delete Banner') ?>"
           href="<?= $this->url('admin', ['controller' => 'banners', 'action' => 'delete'], ['query' => ['id' => $banner['id']]]); ?>"
           data-confirmation-title="<?= $this->translate('Delete') ?>"
           data-confirmation-text="<?= $this->translate('Are you sure to delete this Banner?') ?>">
        </a>
    </td>
</tr>
<?php endforeach; ?>

...........................................................................................................................

Entity of Banner:
/**

• @ORM\Entity
• @ORM\Table(name="banners")
  /
  class Banner extends EntityAbstract
  {
  /*
  
  
  
  • @var integer
    
    *
  
  
  • @ORM\Column(name="id", type="integer", nullable=false)
  
  
  • @ORM\Id
  
  
  • @ORM\GeneratedValue(strategy="IDENTITY")
    
    */
  
  

/**
 * @var \Application\Entity\BannerStatus
 * @ORM\OneToMany(targetEntity="\Application\Entity\BannerStatus", mappedBy="banner")
 */
protected $bannerStatuses;

...........................................................................................................................

I had found the error that occurred from * leftJoin * (to * bannerStatuses *).
Fortunately, that LeftJoin was no need more, so I had fixed it easily.
I tried to fix that problem it via Mapping in Entity (OnetoMany), but it is not give some positive result.
Probably the bug is in the pagination with some leftJoins ...