Querying two tables based on the results of one table - Zend 2

Question

I am using TableGateways along with the Object Mapper which uses the exchangeArray() method for getting database results. This method is fine for getting data in one go.

However, I am struggling to render a table of results where I want to get another resultset in the foreach loop for each row by performing a query.

In regular PHP, I would simply write a loop for the first set of data then write another loop within the first loop that would execute a query for each record. This does not follow Zend's MVC approach so how would I achieve the same thing?

This isn't anything to do with joins.

I have a table of invoices and payments. I am outputting a table of payments and for each payment, I want to output the invoices that are tied to that specific payment. I am trying to achieve this using Zend's MVC and TableGateway patterns.

Thanks.

Answer 1

In regular PHP, I would simply write a loop for the first set of data then write another loop within the first loop that would execute a query for each record. This does not follow Zend's MVC approach so how would I achieve the same thing?

Well, the thing is that the MVC pattern is going to add a level of complexity to something like this and that is not necessarily a bad thing.

You can still do the same as you did before, loop through the results & fetch the associated data. However, instead of doing it as you're displaying the data, you would do it in the controller or preferably in a service or DataMapper object. This is the added level of complexity since you'll have to loop through the results twice. Once to gather the associated data & once to display it. Again, this is not necessarily a bad thing since it makes your code more maintainable by not allowing you to change what the data looks like without needing to change how it's gather or vice versa.

Alternatively you could use an ORM such as Doctrine to give you the relationship mapping in one function call (under the hood it's doing essentially the same two loops). Of course, this means you will need to learn how the ORM works and its API.