too long

Question

Info

I have an application where I want to query a table to be able to get out the record with the smallest id. To simplify my understanding I composed two queries in two table methods.

I am able to query the database with a hard coded scalar but can not query based on the queried scalar from another query.

Question

How do I get a scalar value extracted from from a query in Zend Framework 2 and use the value as an input to the where clause of another (main) query?

Code

   public function getMinActionItemID()
    {
        $select = new Select();
        $select->from('actionitems', array('MinActionItemID'))->columns(array('MinActionItemID' => new Expression('min(ActionItemID)')));
        $resultSet = $this->tableGateway->selectWith($select);
        $resultSet->buffer();
        return $resultSet;
    }

public function getFirstActionItem()
{       
    var_dump($this->getMinActionItemID());
    $select = new Select();
    $select->from('actionitems')->where(array('ActionItemID' => $this->getMinActionItemID()));
    $row = $this->tableGateway->selectWith($select);        

    if (!$row) {
        throw new Exception("Action Item Not Found");
    }

    return $row;

}

I am able to get the following from the query but am unable to get out the scalar value from the object below which is returned from getFirstActionItemID() and pass into the where clause of the getFirstActionItem() method.

  object(Zend\Db\ResultSet\ResultSet)[288]
  protected 'count' => int 1
  protected 'dataSource' => 
    object(Zend\Db\Adapter\Driver\Pdo\Result)[266]
      protected 'statementMode' => string 'forward' (length=7)
      protected 'fetchMode' => int 2
      protected 'resource' => 
        object(PDOStatement)[267]
          public 'queryString' => string 'SELECT min(ActionItemID) AS `MinActionItemID` FROM `actionitems`' (length=64)
      protected 'options' => null
      protected 'currentComplete' => boolean true
      protected 'currentData' => 
        array (size=1)
          'MinActionItemID' => string '1' (length=1)
      protected 'position' => int 0
      protected 'generatedValue' => string '0' (length=1)
      protected 'rowCount' => int 1
  protected 'fieldCount' => int 1
  protected 'position' => int 0

EDIT

I ended up abandoning my approach and used the answers provided. I also preserved the getMinActionItemID() so that I would use it for boundary checking when I navigate to the first and last record and try to go next or previous in my application.

With some investigation through the intellisense, I finally found a way to get out the desired scalar. In hindsight I found both the answer and this code to solve my problem of navigation (one record at a time).

How To Get Scalar From Query

Answer

use Zend\Db\Adapter\Driver\Pdo\Result;

/* @var $resultSet ResultSet */
    /* @var $dataSource Result */
    public function getMinActionItemID()
    {
        $select = new Select();
        $select->from('actionitems', array('MinActionItemID'))->columns(array('MinActionItemID' => new Expression('min(ActionItemID)')));
        $resultSet = $this->tableGateway->selectWith($select);
        $dataSource = $resultSet->getDataSource();
        return $dataSource->getResource()->fetchAll()[0]['MinActionItemID'];
    }

Usage

public function getNextActionItem($id)
{

    if ($id == $this->getMaxActionItemID())
    {
        return $this->getActionItem($id);
    }

    $select = new Select();
    $select->from('actionitems')
           ->where("ActionItemID > $id")
           ->order('ActionItemID')
           ->limit(1);
    $row = $this->tableGateway->selectWith($select);

    if (!$row) {
        throw new Exception ("Action Item Not Found");
    }

    return $row;
}

Answer 1

How to Extract Scalar From an Arbitrary Query from $resultSet in two steps.

 $dataSource = $resultSet->getDataSource();
 $minActionItemID =  $dataSource->getResource()->fetchAll()[0]['MinActionItemID'];  //Note: [0] is the first record and ['MinActionItemID'] is the field name of the custom query