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

Question

I'm developing a RESTful ZF2 based application and using a TableGateway implementation (subclass of the Zend\Db\TableGateway) in combination with a simple mapper for the model, similar to the Album example of the ZF2 manual.

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;
    }

}

Mapper 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's working for a shallow output like this:

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

But now I need a multidimensional output with nested lists like this:

{
    "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 assemble the data to this structure? Directly in the mapper, so that it contains the whole data? Or should I handle this with multiple database requests anb assemple the structure in the controller?

Menorca

Answer 1

What you are trying to accomplish has nothing to do with the TableGateway-Pattern. The TableGateway-Pattern is there to gain access to the Data of one specified Table. This is one of the reasons why in ZF2 you no longer have the option to findDependantRowsets(). It's simply not the TableGateways Job to do so.

To achieve what you are looking for you have pretty much two options:

1. Joined Query

You could write a big query that joins all respective tables and then you'd manually map the output into your desired JSON Format.

2. Multiple Queries

A little less performant approach (looking at the SQL side of things) but "easier" to "map" into your JSON Format.

To give some insight, Doctrine would go with the multiple query approach by default. This is mostly (i guess!) done to provide features that would work on every data backend possible rather than just a couple of SQL Versions...

Service Class

Since you're wondering about the assembling of the json / array, i would set it up like this

'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 ....
}

Quattlebaum

Thank you for you answer, Sam! I think, perhaps my question is not exactly enough. 1. To the TableGateway pattern: I wrote, that I'm using it here, just to describe the context: model consisting of a TableGateway implementation (subclass of the Zend\Db\TableGateway) + mapper. 2. To your two solutions: Yes, actually there are these two ways to get the data. But what I want to understand is, how / where (in which class) to assemble the data (already fetched with either one fat or multiple smaller queries) to this structure complex structure with nested lists. – Menorca

@Menorca Either the Service-Class or your Controller. I'd argue that the way to go would be to have the Service return the appropriate array that you'd need (the nested version like you've classified above) and your controller would handle the array depending on the request needs. – Quattlebaum

Assembling in the Controller would make the Controller pretty fat. But what do you mean with "Service-Class"? A special "assembler" / "combiner" class? If yes: This class has to communicate with the Table class, right? So the code, that is currently being executed in the controller ($this->getCourseTable()->findOnceByID($id)->current();) and some futher similar calls to get the data for the sublists -- this code will move into this assembler class. And the Controller will just execute something like $courseAssembler->getCourse()? Asking just to be sure, that I've understood you correclty. – Menorca

@Menorca Sounds like you've got it right, i editted the answer with an Service-example, though i return an array, of course your could have your service return the appropriate json, too... – Quattlebaum