How to handle multidimensional output with (nested) lists using Zend \ Db \ TableGateway using mapper in Zend Framework 2?

Question

I am developing a RESTful ZF2 application and using the TableGateway implementation (subclass of Zend\Db\TableGateway ) in combination with a simple model mapper , similar to the ZF2 example album example .

Table class

 <?php namespace Courses\Model; use ... class CourseTable { protected $tableGateway; public function __construct(TableGateway $tableGateway) { $this->tableGateway = $tableGateway; } public function findOnceByID($id) { $select = new Select(); $where = new Where(); $select->columns(array( 'id', 'title', 'details', )); $select->from($this->tableGateway->getTable()); $select->where($where, Predicate::OP_AND); $resultSet = $this->tableGateway->selectWith($select); return $resultSet; } } 

Mapping class

 <?php namespace Courses\Model; use ... class CourseDetails implements ArraySerializableInterface { public $id; public $title; public $details; public function exchangeArray(array $data) { $this->id = (isset($data['id'])) ? $data['id'] : null; $this->title = (isset($data['title'])) ? $data['title'] : null; $this->details = (isset($data['details'])) ? $data['details'] : null; } public function getArrayCopy() { return get_object_vars($this); } } 

controller

 <?php namespace Courses\Controller; use ... class CoursesController extends RestfulController // extends AbstractRestfulController { protected $acceptCriteria = array( 'Zend\View\Model\JsonModel' => array( 'application/json', ), 'Zend\View\Model\FeedModel' => array( 'application/rss+xml', ), ); private $courseTable; public function get($id) { $course = $this->getCourseTable()->findOnceByID($id)->current(); $viewModel = $this->acceptableViewModelSelector($this->acceptCriteria); $viewModel->setVariables(array('data' => array( 'id' => $courseDetails->id, 'title' => $courseDetails->title, 'details' => $courseDetails->details ))); return $viewModel; } ... } 

It works for shallow output as follows:

 { "data":{ "id":"123", "title":"test title", "details":"test details" } } 

But now I need multidimensional output with nested lists:

 { "data":{ "id":"123", "title":"test title", "details":"test details", "events":{ "count":"3", "events_list":[ <- main list { "id":"987", "date":"2013-07-20", "place":"Berlin", "trainers":{ "count":"1", "trainers_teamid":"14", "trainers_teamname":"Trainers Team Foo", "trainers_list":[ <- nested list { "id":"135", "name":"Tom" } ] } }, { "id":"876", "date":"2013-07-21", "place":"New York", "trainers":{ "count":"3", "trainers_teamid":"25", "trainers_teamname":"Trainers Team Bar", "trainers_list":[ <- nested list { "id":"357", "name":"Susan" }, { "id":"468", "name":"Brian" }, { "id":"579", "name":"Barbara" } ] } }, { "id":"756", "date":"2013-07-29", "place":"Madrid", "trainers":{ "count":"1", "trainers_teamid":"36", "trainers_teamname":"Trainers Team Baz", "trainers_list":[ <- nested list { "id":"135", "name":"Sandra" } ] ] } ] } } } 

How / where should I collect data in this structure? Directly in the converter so that it contains all the data? Or should I handle this with a few anb database queries in order to create a structure in the controller?

Answer 1

What you are trying to do has nothing to do with TableGateway -Pattern. The TableGateway-Pattern is designed to access the data of one specified table. This is one of the reasons why in ZF2 you no longer have the findDependantRowsets() option. This is simply not a TableGateways Job application.

To achieve what you are looking for, you have almost two options:

1. Join the request

You can write a large query that combines all the relevant tables, and then you manually display the output in the desired JSON format.

2. Several queries

A slightly less efficient approach (looking at the side of SQL things), but "easier" to "display" in your JSON format.

To give some idea, Doctrine uses a multi-query approach by default. This is mainly (I think!) Done to provide functions that will work on every data backend, and not just on several versions of SQL ...

Class of service

Since you are wondering about building json / array, I would set it this way

 'service_manager' => array( 'factories' => array( 'MyEntityService' => 'Mynamespace\Service\Factory\MyEntityServiceFactory' ) ) // MyEntityServiceFactory.php // assuming you only need one dependency! more lines for more dependencies ;) class MyEntityServiceFactory implements FactoryInterface { public function createService(ServiceLocatorInterface $serviceLocator) { return new MyEntityService($serviceLocator->get('YourTableGateway')); } } // Your SERVICE Class class MyEntityService { // do constructor and stuff to handle dependency public function someBigQueryAsArray() { // Query your Gateway here and create the ARRAY that you want to return, // basically this array should match your json output, but have it as array // to be used for other stuff, too } } // lastly your controller public function someAction() { $service = $this->getServiceLocator()->get('MyEntityService'); $data = $service->someBigQueryAsArray(); // do that viewmodel selector stuff // ASSUMING $data is a array of more than one baseObject // i did this in my app to produce the desired valid json output, there may be better ways... if ($viewModel instanceof JsonModel) { foreach($data as $key => $value) { $viewModel->setVariable($key, \Zend\Json\Json::encode($value)); } return $viewModel; } // Handle other ViewModels .... }