Tablegateway with Multiple from Tables

TableGateway with multiple FROM tables

I hope this will help you along your journey as this is a working example I have:

namespace Pool\Model;

use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Sql\Select;

class IpaddressPool extends AbstractTableGateway
{
    public function __construct($adapter)
    {
        $this->table = 'ipaddress_pool';

        $this->adapter = $adapter;

        $this->initialize();
    }

    public function Leases($poolid)
    {
        $result = $this->select(function (Select $select) use ($poolid) {
            $select
                ->columns(array(
                    'ipaddress',
                    'accountid',
                    'productid',
                    'webaccountid'
                ))
                ->join('account', 'account.accountid = ipaddress_pool.accountid', array(
                    'firstname',
                    'lastname'
                ))
                ->join('product_hosting', 'product_hosting.hostingid = ipaddress_pool.hostingid', array(
                    'name'
                ))
                ->join('webaccount', 'webaccount.webaccountid = ipaddress_pool.webaccountid', array(
                    'domain'
                ))->where->equalTo('ipaddress_pool.poolid', $poolid);
        });

        return $result->toArray();
    }
}

Can TableGateway Use Multiple Tables Zend Framework 2

No it is not. Table Gateway object is intended to provide an object that represents a table in a database. Array can be passed to the constructor, but if you pass it, you will get InvalidArgumentException. Please check this code

https://github.com/zendframework/zend-db/blob/release-2.8.2/src/TableGateway/TableGateway.php#L34,L35

Please look at again TableGateway purposed on documentation here

https://framework.zend.com/manual/2.4/en/modules/zend.db.table-gateway.html

Zend:Fetching Data from Multiple tables

Try this :

module.php

<?php
    public function getServiceConfig()
    {
        return array(
            'factories' => array(
                'Application\Model\ShopgoodsTable' =>  function($sm) {
                    $tableGateway = $sm->get('ShopgoodsTableGateway');
                    $table = new ShopgoodsTable($tableGateway);
                    return $table;
                },
                'ShopgoodsTableGateway' => function ($sm) {
                    $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                    $resultSetPrototype = new ResultSet();
                    $resultSetPrototype->setArrayObjectPrototype(new Shopgoods());
                    return new TableGateway('shops_goods', $dbAdapter, null, $resultSetPrototype);
                },
            ),
        );
    }

And in your controller

public function getShopgoodsTable()
{
        if (!$this->shopGoodsTable) 
        {
            $sm = $this->getServiceLocator();
            $this->shopGoodsTable= $sm->get('Shopping\Model\ShopgoodsTable');
        }
        return $this->shopGoodsTable;
}

how to join tables using tablegateway

This is very simple if you know how to handle two tables within a model. Assuming you have ProjectTable and UnitTable models and two TableGateway services. Those will handle two tables respectively in the database. So if you want to join them in your ProjectTable model that would then be

ProjectTable.php

class ProjectTable
{
    private $projectTableGateway;
    private $unitTableGateway;

    public function __construct(
        TableGatewayInterface $projectTableGateway, 
        TableGatewayInterface $unitTableGateway)
    {
        $this->projectTableGateway = $projectTableGateway;
        $this->unitTableGateway = $unitTableGateway;
    }

    public function projectUnit($id)
    {

        /** 
         * as you are joing with "project_table"
         * this will handle "unit_table" 
         */ 
        $sqlSelect = $this->unitTableGateway->getSql()->select();

        /**
         * columns for the "unit_table".
         * if want to use aliases use as 
         * array('alias_name' => 'column_name')
         */
        $sqlSelect->columns(array('column_one', 'column_two'));

        /**
         * this can take two more arguments: 
         * an array of columns for "project_table"
         * and a join type, such as "inner"
         */
        $sqlSelect->join('project_table', 'unit_table.project_id = project_table.id');

        /**
         * set condition based on columns
         */
        $sqlSelect->where(array('unit_table.project_id' => $id));

        $resultSet = $this->unitTableGateway->selectWith($sqlSelect);

        return $resultSet; 
    }
}

Now create two TableGateway services for handling two tables and pass them to the ProjectTable's constructor as the following

Model\ProjectTable::class => function($container) {
    $projectTableGateway = $container->get(Model\ProjectTableGateway::class);          
    $unitTableGateway = $container->get(Model\UnitTableGateway::class);

    return new Model\ProjectTable($projectTableGateway, $unitTableGateway);          
}

Accessing 2 different tables from same controller file

According to manual

1. Create model
2. Create model table

3. Add factory to your module

    return array(
        'factories' => array(
            'Album\Model\ArtistTable' =>  function($sm) {
                $tableGateway = $sm->get('ArtistTableGateway');
                $table = new ArtistTable($tableGateway);
                return $table;
            },
            'ArtistTableGateway' => function ($sm) {
                $dbAdapter = $sm->get('Zend\Db\Adapter\Adapter');
                $resultSetPrototype = new ResultSet();
                $resultSetPrototype->setArrayObjectPrototype(new Artist());
                return new TableGateway('artist', $dbAdapter, null, $resultSetPrototype);
            },
        ),
    );
   

join 2 tables using tablegateway in ZF2

You need to use the following these two line:

$statement = $sql->prepareStatementForSqlObject($select);
$result = $statement->execute();

instead of the line:

$result = $this->tableGateway->selectWith($select) ;

The above line will return the array of data.

LEFT JOIN in ZF2 using TableGateway

Adding to @samsonasik's answer and addressing the issues in its comments. You won't be able to get the joined values out of what is returned from that statement. That statement returns the model object which won't have the joined rows. You'll need to execute it as SQL at a level which will prepare it as raw SQL and return you each resulting row as an array rather than an object:

$sqlSelect = $this->tableGateway->getSql()->select();
$sqlSelect->columns(array('column_name_yourtable'));
$sqlSelect->join('othertable', 'othertable.id = yourtable.id', array('column_name_othertable'), 'left');

$statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect);
$resultSet = $statement->execute();
return $resultSet;

//then in your controller or view:

foreach($resultSet as $row){
    print_r($row['column_name_yourtable']);
    print_r($row['column_name_othertable']);
}

---

Related Topics