Zend Framework 2 : Using HydratingResultSet and ObjectProperty’s Hydrator
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 😉
It’s nice to see your tutorial, i am working with HydratingResultSet and after i get the album entity, I want to save it so I pass to AlbumTable and it show message: track_id, track_title,album_id are not in the fileds list because Album(table database) consist only id, artist and title field.
May you show me the best way to fix this issue, thanks !
the $album object you pass to AlbumTable should not a joined data for saving look the docs : http://zf2.readthedocs.org for selecting one row on user guide part
[…] Abdul Malik Ikhsan: Using HydratingResultSet and ObjectProperty’s Hydrator […]
Hello I tried your sample. I injected this code in index.phtml.
$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;
}
i have an error
get was unable to fetch or create an instance for getsServiceLocator.
Could you giva nay help
you can’t call that at view, call at controller action!. btw, you should understand the basic before start copy paste. read this for service manager cheat sheet : https://samsonasik.wordpress.com/2013/01/02/zend-framework-2-cheat-sheet-service-manager/ and of course, start learn from the docs http://zf2.readthedocs.org/
Thank your for your feed back. I’ll follow your recommendations. In case I am bothering you because I am a beginner with Zend don’t hesitate to tell me.
Thanks a lot
Is there any way to see the $joinedData array_object as simple array rather than looping in for loop?
this is nice..reli worked for me…
[…] https://samsonasik.wordpress.com/2013/09/05/zend-framework-2-using-hydratingresultset-and-objectprope… […]
[…] Zend Framework 2 : Using HydratingResultSet and ObjectProperty’s Hydrator […]
Is it necessary to create factory for AlbumTable, if i already have this:
‘Album\Model\TrackTable’ => function($sm) {
$tableGateway = $sm->get(‘TrackTableGateway’);
$table = new TrackTable($tableGateway);
return $table;
},
in module.php
yes, if you don’t want to create that, you need to create abstract_factories, please read service manage cheat sheet on abstract_factories section here https://samsonasik.wordpress.com/2013/01/02/zend-framework-2-cheat-sheet-service-manager/
Hey, thanks for the code sharing.
Is this best practise? For example when I have a user table which has a foreign key for a role table.
Should I generate from the fetchAll result a role object and store it in the employee object (via setter/getter functionality) or isn’t this a good approach? <- this could be done by writing a custom hydrator with HydratorInterface implementation.
As you can image I'm trying to get the user roles after he successfully logged in (via auth stuff). Now I need to store the user_role with the authenticated user to check permissions when navigation (for example if the logged user has permission to navigate to a specific route)
Unfortunately I don't find any good resource what is the best practise to match the authenticated user with the user in the database (and therefore it's configured role)…
Any hints are warmly welcome 😉
Greetings
Ah I obviously can answer my question itself
I don’t need this by getting the user object via fetch in my userTable.
I can get my result via altering the sql @ the authentication db table stuff.
see here: https://docs.zendframework.com/zend-authentication/adapter/dbtable/credential-treatment/#alter-the-sql-select-directly
I can then do something like this:
$adapter = new CredentialTreatmentAdapter(
$this->dbAdapter,
’employee’,
‘system_name’,
‘password’,
true
);
// get the user role description
$select = $adapter->getDbSelect();
$select->join([‘R’ => ‘role’], ‘role_id = R.id’, [‘role’ => ‘description’], Select::JOIN_LEFT);
and later store it in the loginController via:
$this->authService->getAuth()->getStorage()->write([
‘id’ => $employeeObject->id,
‘role’ => $employeeObject->role,
]);
Greetings