multiple tables for Zend Framework 2

zend-framework2

Solution

The ablums tutorial uses `Zend\Db\TableGateway` which does not support joining to multiple tables.

You need to use `Zend\Db` directly or via a mapper class, such as `AbstractDbMapper` within the ZfcBase module.

The basic usage looks like this:

<?php

// Given that $dbAdapter is an instance of Zend\Db\Adapter\Adapter

use Zend\Db\Sql\Select();
use Zend\Db\ResultSet\ResultSet();

$select = new Select();
$select->from('album')
   ->columns(array('album.*', 'a_name' => 'artist.name'))
   ->join('artist', 'album.artist_id' = 'artist.id');

$statement = $dbAdapter->createStatement();
$select->prepareStatement($dbAdapter, $statement);
$driverResult = $statment->execute();

$resultset = new ResultSet();
$resultset->initialize($driverResult); // can use setDataSource() for older ZF2 versions.

foreach ($resultset as $row) {
        // $row is an ArrayObject
}

The `join()` method is used to perform the join between the `album` and `artist` table. We also use `columns()` to select which columns are returned. In this case, I create an alias called `a_name` for the `name` column within the artist table.

Once the `Select` object is set up, then the rest is the standard `Db` code that will return a `ResultSet` object for you containing the data.

Problem

I'm new to Zend Framework 2. I successfully completed the Album tutorial for ZF2. Now I'd like to display only a certain data from multiple tables in the database. I have a simple database setup with tables, for example, person, books, status..etc. It's really not important what the database's supposed to do. What I would like to know is if there's a tutorial that would show me step-by-step guidance to display data from table joins. I have seen snippets of codes showing how to do joins, but I haven't found any tutorials on setting up the classes, and how to configure Module.php. In other words, the Module in Album has one table name hardcoded in getServiceConfig(). But how do I set it up so it knows I'm requesting data from multiple tables. Also, if I want to setup the relationship, do I still create class for database tables like in Album tutorial, or is it going to be something different. Can you please help, or show me the right path? If you know of any tutorial that explains handling multiple tables, that would be great.

Original source