zf2 select columns from joined tables - how?

Question

I feel like I must me missing something very simple. It's a very simple task, all I want to do is get something like:

SELECT * FROM lookup_items
JOIN lookup ON lookup_items.lookup_id = lookup.id

This returns all the columns for all the joined tables, in regular SQL. Here's my attempt in zf2:

$select = new Select();

$select->from('lookup_items');
$select->join('lookup', 'lookup_items.lookup_id = lookup.id');

The result set only includes the columns in 'lookup_items'. I've tried various ways to get the 'lookup' columns, including:

$select->columns(array('lookup_items.*', 'lookup.*'));

But they all just blow up. Surely there's a way to do this, and it's just so simple I'm missing it completely.

I thought a simple example would be avoid confusion, but here's more code:

class LookupItemsTable extends AbstractTableGateway
{
public function getList($resource)
{
    $system_name = str_replace('*', '%', strtoupper($resource));

    $joinTable = 'lookup';

    $select = new Select();

    $select->from($this->table);
    $select->join($joinTable, "{$this->table}.lookup_id = {$joinTable}.id");

    $where = array();
    $where[] = "{$this->table}.enabled is true";
    $where[] = "{$joinTable}.enabled is true";
    $where[] = "UPPER({$joinTable}.system_name) ilike '{$system_name}'";

    $select->where($where);

    $sort[] = 'sort_order ASC';
    $sort[] = 'value ASC';
    $select->order($sort);

    $rowset = $this->selectWith($select);

    return $rowset;
}
}

Where:

$resource = $this->params()->fromRoute('resource', 'BUSINESS');

And $this->table is 'lookup_items'. Really all I want to do is get columns from both joined tables. I guess there's a zf2 way to just make a straight SQL statement without all the OO falderal, so I could just force it that way. But I'd rather work within the framework as much as possible.

Answer 1

Just change this line

$select->join('lookup', 'lookup_items.lookup_id = lookup.id');

to

$select->join('lookup', 'lookup_items.lookup_id = lookup.id', array('lookupcol1', 'lookupcol2');