How do I add column to select statement in ZF2?

Question

I am trying to add a column in zf2 to a select statement. Similar to the below link (but below only seems to work for ZF1). Is there a way to do this in zf2?

Zend DB Selecting constants - columns that do not exist in table

Below is what I tried:

$select->columns("foo" => new \Zend\Db\Sql\Expression('"foo" as type'))

The SQL Query looks like this:

select *, 'foo'  from bar

Where foo is the value and name of column for all results. Trying the above I get "Unknown column 'foo' in 'field list'"

Many Thanks, M

Answer 1

Problem is that the method $select->columns() overwrites the existing columns.

To counter this problem, you can extend your own "select" and add a new method to "add" columns (as the columns property of the Select class is protected).

Example;

CustomSelect.php

class CustomSelect extends \Zend\Db\Sql\Select
{
    /**
     * @param array $columns
     * @param bool $prefixColumnsWithTable
     * @return $this
     */
    public function addColumns(array $columns, $prefixColumnsWithTable = true) {
        $this->columns = $this->columns + $columns;
        $this->prefixColumnsWithTable = (bool) $prefixColumnsWithTable;
        return $this;
    }
}

Usage:

$select = new CustomSelect();
$select->addColumns(['type' => new \Zend\Db\Sql\Expression("'foo'")]);
$select->addColumns(['*']); //This is the default value though - so adding after adding shouldn't be nessecary (except if you've used "columns()" before)

Answer 2

I'm assuming type is the alias:

try this:

$select->columns(array("type" => new \Zend\Db\Sql\Expression("'foo'")));