ZF2 - Join with TableGateway inside Model Table

Question

How can I do a JOIN inside a Model Table with TableGateway?

I have this in my Model/NoticiasTable.php:

  protected $tableGateway;

  public function __construct(TableGateway $tableGateway) {
    $this->tableGateway = $tableGateway;
  }

  public function fetchAll() {
    $where = new Where();
    $where->isNull('deleted_at');

    $resultSet = $this->tableGateway->select(function (Select $select) use ($where) {
      $select->join('news_photos', 'news_photos.news_id = news.id');
      $select->where($where);
      $select->order('date DESC');
    });
    return $resultSet;
  }

It doesn't show any errors, but the result is empty... If I remove the "join" it works. So, how to use JOIN inside a Model Table?

Answer 1

This is how I do join tables.

use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Predicate\Expression;


/**
 * Preducate contstants.
 */
const PRE_AND = "AND";
const PRE_OR = "OR";
const PRE_NULL = null;

/**
 * @param bool $pagination
 * @param string $join
 * @param array $tbl1OneCols - content table
 * @param array $tbl2OneCols - the joined table
 * @param string $on
 * @param string $joinType
 * @param null|array|string $where
 * @param null $group
 * @param null $order
 * @param int $limit
 * @param int $offset
 *
 * @return ResultSet|Paginator|null
 */
public function fetchJoin($pagination = false, $join = '', array $tbl1OneCols = [], array $tbl2OneCols = [], $on = '', $joinType = self::JOIN_INNER, $where = null, $group = null, $order = null, $limit = 0, $offset = 0)
{
    $select = $this->tableGateway->getSql()->select();
    $select->join($join, $on, $tbl2OneCols, $joinType);
    $result = $this->prepareQuery($select, $tbl1OneCols, $where, self::PRE_NULL, $group, $order, (int) $limit, (int) $offset);
    if ((bool) $pagination === true) {
        return new Paginator(new DbSelect($result, $this->tableGateway->getAdapter(), $this->tableGateway->getResultSetPrototype()));
    } else {
        $resultSet = $this->tableGateway->selectWith($result);
        $resultSet->buffer();
        if ($resultSet->isBuffered() && $resultSet->valid() && $resultSet->count() > 0) {
            return $resultSet;
        }
        return null;
    }
}

/**
 * Prepare all statements before querying the database.
 *
 * @param Zend\Db\Sql\Select $select
 * @param array $columns
 * @param null|array|string $where
 * @param null $group
 * @param null $predicate
 * @param null $order
 * @param null $limit
 * @param null $offset
 *
 * @return Zend\Db\Sql\Select
 */
private function prepareQuery(\Zend\Db\Sql\Select $select, array $columns = [], $where = null, $predicate = self::PRE_NULL, $group = null, $order = null, $limit = null, $offset = null)
{
    if (!empty($columns)) {
        $select->columns($columns);
    }
    if (is_array($where) && !empty($where)) {
        if (!in_array($predicate, [self::PRE_AND, self::PRE_OR, self::PRE_NULL])) {
            $predicate = self::PRE_NULL;
        }
        $select->where($where, $predicate);
    } elseif (!empty($where) && is_string($where)) {
        $select->where(new Expression($where));
    }
    if (!empty($group)) {
        $select->group($group);
    }
    if (!empty($order)) {
        $select->order($order);
    }
    if (!empty($limit) && $limit > 0) {
        $select->limit($limit);
    }
    if (!empty($offset) && $offset > 0) {
        $select->offset($offset);
    }
    return $select;
}

For $this->tableGateway I have a separated class.

namespace Admin\Factory\Model;

use Zend\ServiceManager\FactoryInterface;
use Zend\ServiceManager\ServiceLocatorInterface;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;
use Admin\Model\Menu;
use Admin\Model\MenuTable;

class MenuTableFactory implements FactoryInterface
{
    public function createService(ServiceLocatorInterface $sm = null)
    {
        $resultSetPrototype = new ResultSet();
        $resultSetPrototype->setArrayObjectPrototype(new Menu());
        $db = $sm->get('Zend\Db\Adapter\Adapter');

        $tableGateway = new TableGateway('menu', $db, null, $resultSetPrototype);
        $table = new MenuTable($tableGateway);

        return $table;
    }
}

Now inside the controller let's say that you want to show all X records for indexAction().

With pagination. $this->getView() simply returns ViewModel. If you don't want pagination simply et the first parameter to false.

/**
 * This action shows the list of all Administrators
 */
public function indexAction()
{
    $this->getView()->setTemplate("admin/administrator/index");
    $paginator = $this->getTable("administrator")->fetchJoin(true, "user", ["user"], ["name"], "administrator.user=user.id", "left");
    $paginator->setCurrentPageNumber((int)$this->getParam("page", 1));
    $paginator->setItemCountPerPage(20);
    $this->getView()->paginator = $paginator;
    return $this->getView();
}

$this->getTable() is a controller plugin with a factory which calls ServiceManager outside the controller keeping eveything simple.