need help in custom join query in zend framework 2

Question

I am confused with zend 2 join query with multiple column display, and unable to display those column

StudTable.php

public function custom_query() {
        $select = new Select();
        $select->from(array('s' => 'stud'));
        $select->columns(array('s.id','a.artist', 's.name', 's.sem','s.age'));
        //$select->where(array('id' => 14));

        $select->join(array('a' => 'album'),
                    'a.id = s.album_id');
        $select->order('s.id DESC'); 
        $select->limit(5);

        return $resultSet = $this->tableGateway->selectWith($select);
    }

index.phtml

<ul>
    <?php foreach ($this->customeObject as $obj_cus) : ?>
        <li>ID: <?php echo $obj_cus->id?>| Artist: <?php echo $obj_cus->artist?> | Name:<?php echo $obj_cus->name?> | Sem:<?php echo $obj_cus->sem?> | Age:<?php echo $obj_cus->age?></li>
    <?php endforeach; ?>
    </ul>

but it shows below error

Answer 1

$select = new Select();
$select->from(array('s' => 'stud'));
/* Select columns from primary table without prefix table */
$select->columns(array('id', 'name', 'sem', 'age'));

/* If need where */
$select->where(array('s.id' => 14));

/* It's a INNER JOIN */
$select->join(
    array('a' => 'album'),
    'a.id = s.album_id',
    /* Select joined columns */
    array('artist')
);
$select->order('s.id DESC'); 
$select->limit(5);
return $resultSet = $this->tableGateway->selectWith($select);

Answer 2

The columns method signature looks like this:

public function columns(array $columns, $prefixColumnsWithTable = true)

So by default prefixColumnsWithTable is enabled.

That's why you're getting the error message:

Unknown column s.s.id

So the easiest way to fix this is to pass in false as the second parameter to columns:

$select->columns(array('s.id','a.artist', 's.name', 's.sem','s.age'), false);