Zf2 select columns from joined tables - how?

Question

It seems to me that I should skip something very simple. This is 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 columns for all joined tables in plain SQL. Here is my attempt in zf2:

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

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

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

But they all just explode. Of course, there is a way to do this, and it’s just that I don’t miss it completely.

I thought a simple example is to avoid confusion, but there is more code here:

 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 - 'lookup_items'. In fact, all I want to do is get the columns from both joined tables. I assume that there is a zf2 way to just make a direct SQL query without any Oal-falderal, so I could just force it that way. But I would prefer to 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'); 

Answer 2

Raj's answer is the best, but it only works if you remember to add these parameters to your LookupItems model.

 class LookupItems { // Your lookup_items fields here... ... // And the added lookup fields here, the ones you add in the array public $lookupcol1; public $lookupcol2; 

And in the exchangeArray method:

 public function exchangeArray($data) { // .... your fields, and the new ones $this->lookupcol1 = (! empty($data['lookupcol1'])) ? $data['lookupcol1'] : null; $this->lookupcol2 = (! empty($data['lookupcol2'])) ? $data['lookupcol2'] : null; } 

Answer 3

I get it.

Added:

 $select->columns(array('*')); 

And this is closer to the end:

 $sql = new Sql($this->adapter); $statement = $sql->prepareStatementForSqlObject($select); $rowset = $statement->execute(); 

This returns the expected result with the warning that now my rows are returned as associative arrays instead of objects.

Answer 4

Here's how you can create join queries in zf2.

  $resultSet = $this->select(function (Select $select) { // omit the table name //$select->from('foo'); $select->join('users', "users.id foo.createdby", 'firstname', ''); $select->order('id ASC'); // echo $select->getSqlString();// to print your query }); $entities = array(); foreach ($resultSet as $row) { $entity = new Entity\Foo(); $entity->setId($row->id) ->setFullname($row->fullname) ->setCaseid($row->caseid) ->setTestimonial($row->testimonial) ->setSortorder($row->sortorder) ->setActive($row->active) ->setCreated($row->created) ->setModified($row->modified) ->setFirstname($row->firstname) ->setCreatedby($row->createdby); $entities[] = $entity; } return $entities;