Welcome to Abdul Malik Ikhsan's Blog

When joining table using Zend\Db, calling coloumn(s) in other table when make a join is a pain. It because if we use normal Zend\Db\ResultSet\ResultSet and setting up ArrayObjectPrototype with the class object, we only get the current model class only ( that consist of columns of single table). Register other table column on that class is NOT a solution, it will make a confusion on what entity is for. Maybe not best (errr…good) solution but simplest solution is to use Zend\Db\ResultSet\HydratingResultSet and Zend\Stdlib\Hydrator\ObjectProperty.
There is I explain on case situation : “we have a table named album ( with columns : `id`, `artist`, `title` ) and table named track ( with columns : `track_id`, `track_title`, and `album_id` as foreign key agains album )”.
Ok, let’s create model like as the docs :

//filename : module/Album/src/Album/Model/Album.php
namespace Album\Model;

class Album
{
    public $id;
    public $artist;
    public $title;

    public function exchangeArray($data)
    {
        $this->id     = (isset($data['id']))     ? $data['id']     : null;
        $this->artist = (isset($data['artist'])) ? $data['artist'] : null;
        $this->title  = (isset($data['title']))  ? $data['title']  : null;
    }

    public function getArrayCopy()
    {
        return get_object_vars($this);
    }
}

Ok, Let’s create a sample table class for album table :

//filename : module/Album/src/Album/Model/AlbumTable.php
namespace Album\Model;

use Zend\Db\TableGateway\TableGateway;

class AlbumTable
{
    protected $tableGateway;

    public function __construct(TableGateway $tableGateway)
    {
        $this->tableGateway = $tableGateway;
    }
    
   //to retrieve tableGateway object when needed.
    public function getTableGateway()
    {
        return $this->tableGateway;
    }

    public function JoinfetchAll()
    {
        $sqlSelect = $this->tableGateway->getSql()
                          ->select()
                          ->join('track', 'track.album_id = album.id', array('*'), 'left');

        return $this->tableGateway->selectWith($sqlSelect);
    }
}

If you want to reduce closure usage at your application, you can create factory for AlbumTable creation like the following :

//filename : module/Album/src/Album/Factory/Model/AlbumTableFactory.php
namespace Album\Factory\Model;

use Zend\ServiceManager\FactoryInterface;
use Zend\ServiceManager\ServiceLocatorInterface;
use Zend\Db\TableGateway\TableGateway;
use Album\Model\AlbumTable;
use Album\Model\Album;

use Zend\Stdlib\Hydrator\ObjectProperty;
use Zend\Db\ResultSet\HydratingResultSet;

class AlbumTableFactory implements FactoryInterface
{
    public function createService(ServiceLocatorInterface $serviceLocator)
    {
        $db = $serviceLocator->get('Zend\Db\Adapter\Adapter');

        $resultSetPrototype = new HydratingResultSet();
        $resultSetPrototype->setHydrator(new ObjectProperty());
        $resultSetPrototype->setObjectPrototype(new Album());

        $tableGateway       = new TableGateway('album', $db, null, $resultSetPrototype);
        $table              = new AlbumTable($tableGateway);

        return $table;
    }
}

and we just make a call like this :

//filename : module/Album/Module.php
namespace Album;

class Module
{
     // getAutoloaderConfig() and getConfig() methods here

     // Add this method:
     public function getServiceConfig()
     {
         return array(
             'factories' => array(
                 'Album\Model\AlbumTable' =>  'Album\Factory\Model\AlbumTableFactory'
             ),
         );
     }
 }

That’s it, we now can call like this :

$table = $this->getServiceLocator()->get('Album\Model\AlbumTable');
$joinedData = $table->JoinfetchAll();

foreach($joinedData as $row) {
    //$row->track_id will called even not registered
    //in album model class.
    echo $row->artist.' : '.$row->track_id; 
}

We know that we need to retrieve track table column form *JoinfetchAll()* table. but we don’t need to register it to be retrieve-able.

Ok, done, happy hacking 😉