TableGateway with multiple FROM tables

Question

I would like to do a simple INNER JOIN between two tables in Zend2.

Concretely, I would like to do this in Zend2:

SELECT * FROM foo, bar WHERE foo.foreign_id = bar.id;

I have a FooTable:

class FooTable
{
  protected $tableGateway;

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

  public function get($id)
  {
    $rowset = $this->tableGateway->select(function (Select $select) {
      $select->from('foo');
    });
  }
}

The $select->from('foo'); returns an error:

==> Since this object was created with a table and/or schema in the constructor, it is read only.

So, I can't tweak my FROM statement to match a simple inner join between FooTable and BarTable.

Diemuzi · Accepted Answer · 2013-07-26T17:18:53.567

12

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();
    }
}

edited Jul 26 '13 at 17:18

answered Jan 17 '13 at 01:56

Diemuzi

3,507
7
36
61

What is `use` in the above example mean/do ? Is this a Lambda function? Is `use` used to kind of use the external variable within the declared function? – Ziyan Junaideen Feb 11 '13 at 18:25
1

I'm going to assume you are talking about the `use ($poolid)` section. Since the $this->select is calling a function(), I also needed to use a variable in my query, adding `use ($poolid)` allows that ability. Normal functions `function name($var1, $var2)`, but in this case we are making a function call out of the Select – Diemuzi Feb 11 '13 at 18:35
Never mind, I got the answer... PHP has changed a lot since the 4 years I have been away from it. Here is nice post on it: http://stackoverflow.com/questions/1065188/in-php-5-3-0-what-is-the-function-use-identifier-should-a-sane-programmer-use – Ziyan Junaideen Feb 11 '13 at 18:58
Thanks, I didn't see your answer. Really cool stuff. Thank for the multiple join answer. I am going to try it out tomorrow morning... – Ziyan Junaideen Feb 11 '13 at 19:01
@Diemuzi, could you please explain why you define closure in this case? – Dima Dz Nov 23 '16 at 18:54

TableGateway with multiple FROM tables

1 Answers1

Linked