ZF2 Custom query

Question

Hello and good morning to you all,

I am following the qickstart tutorial from Rob Allen in the manual. I am trying to change some things. One of the things I am trying to do is to get a query like this:

"SELECT max(id) FROM Albums";

I tried things like

$this->select();
$this->columns(array('id' => 'MAX(id)'));

Apparently this is not the way to do it. I probablly need some expression object or so.

Can anyone tell me how to solve this?

EDIT (forget the above)

This whole code is based on the quickstart in the manual (ZF2) I have managed to write a query like this:

    $select = $this->getSql()->select();
    $select->columns(array(new Expression('max(id) as MaxId')));
    $rowset = $this->selectWith($select);
    $row = $rowset->current();
    return $row;

The result of this is an empty object.

But when i change

    $select->columns(array(new Expression('max(id) as MaxId')));

to

  $select->columns(array(new Expression('max(id) as id')));

then i get back an object with the id as 1. Which is the max(id).

But when I add in my album object in the function exchangeArray one line with maxId, then it returns the maxId field.

BUT, it can't be that i need to do this everytime i just want to do a query like this. Is this really the way it works?

Answer 1

Use Zend\Db\Sql\Expression

So if i see this correctly (which may not be the case) you'd do it like you just did, but wrap the SQL-Expression into new Expression('max(id)). So it should be like the following

use Zend\Db\Sql\Expression;
//...
$this->columns(array(
    'maxid' => new Expression('max(id)')
));

If the syntax like this is wrong, please don't curse me, but i would assume that knowing about the Sql\Expression will already help you ;)