Zend Framework 2 : Zend\Db Modelling – The hard way

This post inspire from Ralph Schindler presentation about Modelling in ZF2 with Zend\Db. Of course, using Zend\Db, we can do “join” directly when dealing with table relation, but when we have several columns that same in tables, like “album” and “track” that has same column named “title” per-table, we need to know what columns comes from. We can argue to use “aliasing” for same column, but we can’t do it easily when columns that same is more than we can imagine :p. So, we actually need it. I will show you how to do it.
Preparations :
1. Create tables
I create two kind of tables for this post purpose with this fake data ( I have no time for finding the right one😛 ) :

DROP TABLE IF EXISTS `album`;
CREATE TABLE IF NOT EXISTS `album` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `artist` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `album`
--

INSERT INTO `album` (`id`, `artist`, `title`) VALUES
(1, 'Bruno Mars', 'Go'),
(2, 'Syahrini', 'Membahana'),
(3, 'Justin Timberlake', 'Love');

-- --------------------------------------------------------

--
-- Table structure for table `track`
--

DROP TABLE IF EXISTS `track`;
CREATE TABLE IF NOT EXISTS `track` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `album_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `track`
--

INSERT INTO `track` (`id`, `title`, `album_id`) VALUES
(1, 'Sesuatu', 2),
(2, 'Aku Tak Biasa', 2),
(3, 'Grenade', 1),
(4, 'I like it!', 1);

1. Create Entities
   a. Track Entity

   class Track
   {
       public $id;
       public $title;
       public $album_id;
   }
   

   b. Album Entity

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

   How two kinds of entity “relate” ? Of course, add more function : setTrack() and getTrack() to Album Entity :

   class Album
   ...
       protected $tracks;
   
       public function setTracks($tracks)
       {
           $this->tracks = $tracks;
       }
   
       public function getTracks()
       {
           return $this->tracks;
       }
   ...
   }
   

2. Create AlbumTable and TrackTable, we can adapt from the docs ( I will not repeat here )
3. Create a mapper for them :

   class AlbumTrackMapper
   {
       protected $album;
       protected $track;
   
       public function __construct(AlbumTable $album, TrackTable $track)
       {
           $this->album = $album;
           $this->track = $track;
       }
   
       public function findAll()
       {
           $albumResult = $this->album->getTableGateway()->select();
           $albumResult->buffer();
   
           $albums = iterator_to_array($albumResult);
           foreach ($albums as $album) {
               $trackrows = $this->track->getTableGateway()
                                 ->select(array('album_id' => $album->id));
               $album->setTracks(iterator_to_array($trackrows));
           }
   
           return $albums;
       }
   }
   

4. Build instance of AlbumTrackMapper using ‘factories’.

       'service_manager' => array(
           'factories' => array(
               //register AlbumTable and TrackTable here
               //we can do same as the docs or using abstract_factories to "automate" them
   
               'AlbumTrackMapper' => function($sm) {
                    $albumtable = $sm->get('SanDbModellingWithZendDb\Model\AlbumTable');
                    $tracktable = $sm->get('SanDbModellingWithZendDb\Model\TrackTable');
    
                   $mapper = new Model\AlbumTrackMapper($albumtable, $tracktable);
   
                   return $mapper;
               },
           ),
       ),
   

5. So, when everything goes fine, we can var_dump the findAll at got it :

   object(Zend\Db\ResultSet\HydratingResultSet)[257]
     protected 'hydrator' => 
       object(Zend\Stdlib\Hydrator\ObjectProperty)[250]
         protected 'strategies' => 
           object(ArrayObject)[251]
         protected 'filterComposite' => 
           object(Zend\Stdlib\Hydrator\Filter\FilterComposite)[252]
             protected 'orFilter' => 
               object(ArrayObject)[253]
             protected 'andFilter' => 
               object(ArrayObject)[254]
     protected 'objectPrototype' => 
       object(SanDbModellingWithZendDb\Model\Album)[244]
         public 'id' => null
         public 'artist' => null
         public 'title' => null
         protected 'tracks' => null
     protected 'buffer' => 
       array (size=3)
         0 => 
           object(SanDbModellingWithZendDb\Model\Album)[293]
             public 'id' => string '1' (length=1)
             public 'artist' => string 'Bruno Mars' (length=10)
             public 'title' => string 'Go' (length=2)
             protected 'tracks' => 
               array (size=2)
                 0 => 
                   object(SanDbModellingWithZendDb\Model\Track)[303]
                     public 'id' => string '3' (length=1)
                     public 'title' => string 'Grenade' (length=7)
                     public 'album_id' => string '1' (length=1)
                 1 => 
                   object(SanDbModellingWithZendDb\Model\Track)[256]
                     public 'id' => string '4' (length=1)
                     public 'title' => string 'I like it!' (length=10)
                     public 'album_id' => string '1' (length=1)
         1 => 
           object(SanDbModellingWithZendDb\Model\Album)[305]
             public 'id' => string '2' (length=1)
             public 'artist' => string 'Syahrini' (length=8)
             public 'title' => string 'Membahana' (length=9)
             protected 'tracks' => 
               array (size=2)
                 0 => 
                   object(SanDbModellingWithZendDb\Model\Track)[304]
                     public 'id' => string '1' (length=1)
                     public 'title' => string 'Sesuatu' (length=7)
                     public 'album_id' => string '2' (length=1)
                 1 => 
                   object(SanDbModellingWithZendDb\Model\Track)[297]
                     public 'id' => string '2' (length=1)
                     public 'title' => string 'Aku Tak Biasa' (length=13)
                     public 'album_id' => string '2' (length=1)
         2 => 
           object(SanDbModellingWithZendDb\Model\Album)[296]
             public 'id' => string '3' (length=1)
             public 'artist' => string 'Justin Timberlake' (length=17)
             public 'title' => string 'Love' (length=4)
             protected 'tracks' => 
               array (size=0)
                 empty
     protected 'count' => int 3
     protected 'dataSource' => 
       object(Zend\Db\Adapter\Driver\Pdo\Result)[291]
         protected 'statementMode' => string 'forward' (length=7)
         protected 'resource' => 
           object(PDOStatement)[292]
             public 'queryString' => string 'SELECT `album`.* FROM `album`' (length=29)
         protected 'options' => null
         protected 'currentComplete' => boolean true
         protected 'currentData' => boolean false
         protected 'position' => int 3
         protected 'generatedValue' => string '0' (length=1)
         protected 'rowCount' => int 3
     protected 'fieldCount' => int 3
     protected 'position' => int 3
   

   In my machine, I try using HydratingResultSet.

And done. so we can show that with html like the following :

        echo '<ul>';
        foreach ($albums as $album) {
            echo  '<li>';

            echo $album->artist ;
            echo '<ul>';
                foreach ($album->getTracks() as $i => $track) {
                    echo '<li>' . ($i+1) . ': ' .  $track->title  . '</li>';
                }
            echo '</ul>';

            echo '</li>';
        }
        echo '</ul>';

Want to grap codes ? I have uploaded to my github account : https://github.com/samsonasik/SanDbModellingWithZendDb

References :
1. https://speakerdeck.com/ralphschindler/building-models-in-zf2-a-crash-course
2. https://gist.github.com/ralphschindler/6910421