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);
- Create Entities
a. Track Entityclass 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; } ... }
- Create AlbumTable and TrackTable, we can adapt from the docs ( I will not repeat here )
- 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; } }
- 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; }, ), ),
- 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
hi samsonasik,
I was unable to contact you, So i am commenting here. I want to generate pdf from html in zf2. I have search about this. I found a module DomPdfModule but it is not working because I don’t know how to use it exactly. So can you please guide me for implementing this.
Thanks in advanced.
http://raymondkolbe.com/2012/07/01/dompdf-in-zf2/
Thanks for your quick reply, I will try this
I am unable to install the dompdf module. Getting this error
Loading composer repositories with package information
Installing dependencies (including require-dev)
Your requirements could not be resolved to an installable set of packages.
Problem 1
– The requested package raykolbe/dompdfmodule could not be found in any version, there may be a typo in the package name.
Potential causes:
– A typo in the package name
– The package is not available in a stable-enough version according to your minimum-stability setting
see for more details.
Read for further common problems.
and Code of my composer.json is
{
“name”: “zendframework/skeleton-application”,
“description”: “Skeleton Application for ZF2”,
“license”: “BSD-3-Clause”,
“keywords”: [
“framework”,
“zf2”
],
“minimum-stability”: “dev”,
“homepage”: “http://framework.zend.com/”,
“require”: {
“php”: “>=5.3.3”,
“zendframework/zendframework”: “2.*”,
“dino/dompdf-module”: “dev-master”
}
}
Thanks samsonasik its working now
Hi Sam,
Thank you for your tutorial . I have one question : I didn’t find getTableGateway() function in the album model. So, what ‘s it? Pls help me. I have just start with ZF2 and my english is bad.
create one :p
From what I’m seeing you are querying the tracks table while looping in each album. That seems inefficient.
yes, but that what I’ve learn from Ralph Schindler presentation : https://speakerdeck.com/ralphschindler/building-models-in-zf2-a-crash-course , any suggestion will be appreciated 😉
Hi Sam, thank you very much for this site it is very usefull. I have one question. When I realised this module, I got an incorrect behavior when trying to setTracks in the findAll function, I getting only one row on a finish. If I removing foreach with setTracks I getting all rows with all albums. What may be a problem? Sorry for my english.
follow http://framework.zend.com/manual/2.3/en/user-guide/database-and-models.html
this method then after above concept apply..
please reply fast.
When i have 1000$ albums, Zend create 1000 queries?
yeah, there is performance drawback on this, i propose to use manual join if the purpose if performance, or use doctrine instead 😉