How to use pagination with JOINs in Zend Framework 2?

Question

In an application I implemented a Mapper to retrieve data from the database and objects from the ResultSet. In order to get all the data I have to JOIN multiple tables. Then I get a huge array structured like this:

[
    0 => [
        main_id => 1,
        main_title => 'lorem',
        main_desc => 'ipsum',
        foo_id => '12',
        foo_name => 'qwer',
        bar_id => '56',
        bar_name => 'asdf'
    ],
    1 => [
        main_id => 1,
        main_title => 'lorem',
        main_desc => 'ipsum',
        foo_id => '12',
        foo_name => 'qwer',
        bar_id => '67',
        bar_name => 'hjkl'
    ],
    2 => [
        main_id => 1,
        main_title => 'lorem',
        main_desc => 'ipsum',
        foo_id => '23',
        foo_name => 'uiop',
        bar_id => '67',
        bar_name => 'hjkl'
    ],
    ...
    10 => [
        main_id => 1,
        main_title => 'lorem',
        main_desc => 'ipsum',
        foo_id => '23',
        foo_name => 'uiop',
        bar_id => '91',
        bar_name => 'cvbn'
    ],
    11 => [
        main_id => 2,
        main_title => 'dolor',
        main_desc => 'sit',
        foo_id => '78',
        foo_name => 'fghj',
        bar_id => '89',
        bar_name => 'vbnm'
    ],
    ...
    12 => [
        main_id => 3,
        foo_id => '135',
        bar_id => '246',
        ...
    ],
    13 => [
        main_id => 3,
        foo_id => '135',
        bar_id => '468',
        ...
    ],
    14 => [
        main_id => 3,
        foo_id => '357',
        bar_id => '680',
        ...
    ],
    ...
    1000 => [
        ...
    ]
]

Then I iterate over the array, build objects (Main, Foo, Bar etc.), and combine them, e.g.

$newMain = $myMainHydrator->hydrate($results[0]);
$newFoo = $myFooHydrator->hydrate($results[0]);
$newBar = $myBarHydrator->hydrate($results[0]);
$newFoo->setBar($newBar);
$newMain->setFoo($newFoo);
$resultObjects[] = $newMain;

Now I built in a Paginator and got following issue: The Paginator sets a LIMIT, e.g. 10, and retrieves then only 10 rows, while I need for every object more than one result row (currently even 12 rows).

I cannot believe, that the Paginator cannot handle JOINs, so there must be a way to get it working. How to use the Paginator for complex SELECTs with JOINs?

Answer 1

The issue can be solved by replacing of the LIMIT clause by an IN in the Paginator Adapter's getItems(...).

namespace Foo\Paginator\Adapter;

use Zend\Db\Sql\Select;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Expression;

class FooPaginatorAdapter extends DbSelect
{

    public function count()
    {
        $select = new Select();
        $select->from('foo')->columns([self::ROW_COUNT_COLUMN_NAME => new Expression('COUNT(*)')]);

        $statement = $this->sql->prepareStatementForSqlObject($select);
        $result    = $statement->execute();
        $row       = $result->current();
        $this->rowCount = $row[self::ROW_COUNT_COLUMN_NAME];

        return $this->rowCount;
    }

    public function getItems($offset, $itemCountPerPage)
    {
        $select = clone $this->select;
        // replaced
        // $select->offset($offset);
        // $select->limit($itemCountPerPage);
        // by this
        $relevantIds = $this->getRelevantIds($offset, $itemCountPerPage);
        $select->where->in('foo.id', $relevantIds);

        $statement = $this->sql->prepareStatementForSqlObject($select);
        $result    = $statement->execute();

        $resultSet = clone $this->resultSetPrototype;
        $resultSet->initialize($result);

        return iterator_to_array($resultSet);
    }

    protected function getRelevantIds($offset, $itemCountPerPage)
    {
        $sql = new Sql($this->sql->getAdapter());
        $select = $sql->select('foo');
        $select->columns(['id']);
        $select->offset($offset);
        $select->limit($itemCountPerPage);

        $statement = $this->sql->prepareStatementForSqlObject($select);
        $result    = $statement->execute();

        $resultArray = iterator_to_array($result);

        $relevantIds = array_column($resultArray, 'id');

        return $relevantIds;
    }

}

As you see in this case the IDs first have to be retrieved with a separate database request. But anyway -- it works. If you know a better solution, please feel free to post it.