ZF2 TableGateway join

Question

I am in the process of trying to learn OO/Zend Framework over standard PHP.. I want to scream and write a mysql query instead of using the TableGateway method.

I have been following tutorials and have successfully printed out a table and some fields, although with the way I have gone about doing this, I am totally lost in how I should make this a join with another table and print out some fields there.

For example.

Table Fields customer Idx, Company contact Idx, First_Name

This is my customersController where I assume the work is carried out

      namespace Customers\Controller;

      use Zend\Mvc\Controller\AbstractActionController;
      use Zend\View\Model\ViewModel;
      use Zend\DB\TableGateway\TableGateway;


class CustomersController extends AbstractActionController
 {

 protected $customersTable;

 public function indexAction()
 {
     return new ViewModel(array('customer' => $this->getCustomersTable()->select()));
     //return new ViewModel(array('customers' => $this->fetchJoin()->select()));
 }

 public function addAction()
 {
 }

 public function editAction()
 {
 }

 public function deleteAction()
 {
 }

 public function getCustomersTable()
 {
        if (!$this->customersTable) {
        $this->customersTable = new TableGateway (
        'customer', //table name
         $this->getServiceLocator()->get('Zend\DB\Adapter\Adapter')
         );
     }
     return $this->customersTable;


    }

   }

Am I on the right track here?

Answer 1

If you need to make joins read about Zend\Db\Sql and Zend\Db\Select which you can read about here

http://framework.zend.com/manual/2.0/en/modules/zend.db.sql.html

An example would be:

In your model(that extends the TableGateway or the AbstractTableGateway)
in Some function you can have something like(this is from a project) :

$sql = new \Zend\Db\Sql\Sql($this->getAdapter());

$select = $sql->select()
    ->from('event_related_events')
    ->columns(array())
    ->join('event_invitees', 'event_invitees.event_id = 
       event_related_events.related_event_id')
    ->where(array('event_related_events.event_id' => $eventId));

$selectString = $sql->getSqlStringForSqlObject($select); 
$results      = $this->getAdapter()->query($selectString, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);

Then you can loop over the results and do what you need to.

Taking a look at more powerful ORM like Doctrine or Propel may also help, but may be an overkill for a small/hobby project.

---

EDIT: Answer for what was asked in comments

For Using expression(if, case etc) directly you can use something like :

 $sql->select()
    ->from('table')
    ->columns(array(
        'sorter' => new Expression('(IF ( table.`something` >= 'otherthing',  1,  0))'),
    'some_count' => new Expression('(count(*))'),
    )
)

Explaining the last line in SQL terms, it would be: count(*) AS some_count

Answer 2

So this is my controller, basically from the Album example but now it will display customers from the customer table.

<?php

    namespace Customers\Controller;

     use Zend\Mvc\Controller\AbstractActionController;
     use Zend\View\Model\ViewModel;
     use Customers\Model\Customers;          
     use Customers\Form\CustomersForm; 

     class CustomersController extends AbstractActionController
     {

      protected $customersTable;

         public function indexAction()
         {
             return new ViewModel(array(
                 'customer' => $this->getCustomersTable()->fetchAll(),
             ));
         }

        public function addAction()
        {
        }

        public function editAction()
         {
         }

        public function deleteAction()
        {
        }

         public function getCustomersTable()
         {
             if (!$this->customersTable) {
                 $sm = $this->getServiceLocator();
                 $this->customersTable = $sm->get('Customers\Model\CustomersTable');
             }
             return $this->customersTable;
         }



     }


     ?>

The indexAction calls the getCustomersTable method which goes to the model (CustomersTable) and executes the "query" there.

<?php

namespace Customers\Model;

 use Zend\Db\TableGateway\TableGateway;

 class CustomersTable
 {
     protected $tableGateway;

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

     public function fetchAll()
     {
         $resultSet = $this->tableGateway->select();
         return $resultSet;
     }

     public function getCustomers($id)
     {

     }

     public function saveCustomers(customers $customers)
     {

     }


     public function deleteCustomers($id)
     {
     }
 }

 ?>

So from your example, I should be trying to implement this into the fetchAll in the model? Thanks for the help.

$sql = new \Zend\Db\Sql\Sql($this->getAdapter());

        $select = $sql->select()
    ->from('customer')
    ->columns(array())
    ->join('contact', 'contact.Idx = customer.Idx')
    ->where(array('contact.Idx' => $eventId));

$selectString = $sql->getSqlStringForSqlObject($select); 
$results      = $this->getAdapter()->query($selectString, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);