How to add join columns to tablegateway in zendframework 2

Question

I am struggling with Zend now for having the joined table columns in the sql string properly.

I have this code (which works fine):

$expression = new \Zend\Db\Sql\Expression('group = acl_groups.id');

$select = $this->tableGateway->getSql()->select();
$select->columns(array(
    $select::SQL_STAR,
    'group',
    'id',
    'status'
))->join('acl_groups', 'group = acl_groups.id');
return $this->tableGateway->selectWith($select);

But I can`t get the acl_groups columns to work. Any attempt fails. I have read the Zend Documentation and nothing effectively worked for me.

My join table has this columns:

id
name
status

I need name to have an alias "groupname"

I tried this:

$select->columns(array(
    $select::SQL_STAR,
    'group',
    'id',
    'status',
    array('acl_groups.name' => 'groupname')
)

$select->columns(array(
    $select::SQL_STAR,
    'group',
    'id',
    'status',
    'acl_groups.name AS groupname'
)

But none works.

Answer 1

You have two ways to do this as mentioned here

Method 1

 $resultSet = $tableGateway->select (function (Select $select) {
    // now you have `select` object, do whatever you like
});

Method 2

$select = new Select('table_name');
$select->join(
  'another_table_name',
  'join condition',
  array('column of another table name'),
  Select::JOIN_INNER
);
$resultSet = $tableGateway->selectWith($select);