Zend Framework 2 model foreign keys

Question

I am now approaching Zend Framework 2 and I followed the tutorial on creating a simple CRUD application. Everything is fine but now I want to, for instance, add a genre to the album.

I added a category table to the database and created a foreign key category_id in the album table which refers to the category.id in the category table.

How do I reflect this situation on my model using TableGateway?

Answer 1

I hope this will help you and you will get the idea how to accomplish your task:

<?php
In 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;
        $this->dbSql = new Sql($this->tableGateway->getAdapter()); // add this line
    }

    public function fetchAll()
    {
        $select = $this->dbSql->select();

        $select->from('album')
               ->columns(array('id', 'artist', 'title'))
               ->join(array('C' => 'category'),
                            'fk_category = id_category',
                      array('id_category', 'name'),
                            $select::JOIN_LEFT);
        $resultSet = $this->tableGateway->selectWith($this->select);

        return $resultSet;
    }

    public function getAlbum($id)
    {
        $id  = (int) $id;

        $select = $this->dbSql->select();
        $where = new Where();

        $select->from('album')
               ->columns(array('id_album', 'artist', 'title'))
               ->join(array('C' => 'category'),
                            'fk_category = id_category',
                      array('id_category', 'name'),
                            $select::JOIN_LEFT);
        $where->equalTo('id' => $id);
        $rowset = $this->tableGateway->selectWith($this->select->where($where));

        $row = $rowset->current();
        if (!$row) {
            throw new \Exception("Could not find row $id");
        }
        return $row;
    }

    public function saveAlbum(Album $album)
    {
        $data = array(
            'artist' => $album->artist,
            'title'  => $album->title,
            'fk_category' => $album->category_id
        );

        $id = (int)$album->id;
        if ($id == 0) {
            $this->tableGateway->insert($data);
        } else {
            if ($this->getAlbum($id)) {
                $this->tableGateway->update($data, array('id' => $id));
            } else {
                throw new \Exception('Form id does not exist');
            }
        }
    }

    public function deleteAlbum($id)
    {
        $this->tableGateway->delete(array('id' => $id));
    }
}

UPDATE

In module/Album/src/Album/Model/Album.php
=========================================

namespace Album\Model;

class Album
{
    public $albumId;
    public $artist;
    public $title;
    public $categoryId;
    public $categoryName;

    public function exchangeArray($data)
    {
        $this->albumId = (isset($data['id_album'])) ? $data['id_album'] : null;
        $this->artist = (isset($data['artist'])) ? $data['artist'] : null;
        $this->title = (isset($data['title'])) ? $data['title'] : null;
        $this->categoryId = (isset($data['id_category'])) ? $data['id_category'] : null;
        $this->categoryName = (isset($data['name'])) ? $data['name'] : null;
    }

    public function getArrayCopy()
    {
        return get_object_vars($this);
    }
}

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