Table Gateway and fetching one filtered row

[divix1988]

I need to return a single rowset from my tablegateway but which match additional conditions.
However because I am using paginator to fetch multiple results I need to use getSql->select() in order to maanipulate and then pass that $select object to fetchAll().
My problem is fetchRow() method which looks like this:

protected function fetchRow($passedSelect)
{
    $row = $this->tableGateway->select(function (\Zend\Db\Sql\Select $select) use ($passedSelect) {
        $select = $passedSelect;
   });

    return $row->current();
}

And here is my TableGateway:

public function getBy(array $params = array())
{
    $select = $this->tableGateway->getSql()->select();

    if (isset($params['id'])) {
        $select->where('id = ?', $params['id']);
        $params['limit'] = 1;
    }
    if (isset($params['title'])) {
        $select->where('title = ?', $params['title']);
    }
    if (isset($params['thumb'])) {
        $select->where('thumb = ?', $params['thumb']);
    }

    if (isset($params['limit'])) {
       $select->limit($params['limit']);
    }

    $result = (isset($params['limit']) && $params['limit'] == 1)
        ? $this->fetchRow($select) 
        : $this->fetchAll($select, ['limit' => $this->resultsPerPage, 'page' => $params['page']]);

    return $result;
}

Usage:
$object->getBy(['id'] => 3);

Why closure in fetchRow(), return first element in table, instead of passed id of 3? It looks like closure doesn't overrride $select var at all.

[alextech]

No, it would not because closures do not overwrite original variables.

$select = $passedSelect;

Does not move $passedSelect up to where $select came from. Instead, there is a local variable $select which is pointing to object from table gateway. Once you make it equal something else you are actually saying "I do not want original reference anymore, now $select should be point to $passedSelect instead". The change is within the closure only.

Instead of making a new select object and passing it to closure you can have

protected function fetchRow($passedSelect)
{
    $row = $this->tableGateway->select(function (\Zend\Db\Sql\Select $select) use ($additionalCriteria) {
        $select->where($additionalCriteria);
   });

    return $row->current();
}

As long as you do not destroy what $select is pointing to, you can perform operations on it, like adding your extra where statement with id.

[divix1988]

Thanks for your reply, I've managed to fixed it by doing the following:

public function getBy(array $params = array())
    {
        $select = $this->tableGateway->getSql()->select();
        
        if (!isset($params['page'])) {
            $params['page'] = 0;
        }
        if (isset($params['id'])) {
			$select->where(['id' => $params['id']]);
            $params['limit'] = 1;
        }
        if (isset($params['title'])) {
            $select->where(['title' => $params['title']]);
        }
        if (isset($params['thumb'])) {
            $select->where(['thumb' => $params['thumb']]);
        }
         
        if (isset($params['limit'])) {
           $select->limit($params['limit']);
        }
         
        $result = (isset($params['limit']) && $params['limit'] == 1)
            ? $this->fetchRow($select) 
            : $this->fetchAll($select, ['limit' => $this->resultsPerPage, 'page' => $params['page']]);

		return $result;
    }

and inside AbstractTable:

protected function fetchRow($passedSelect)
    {
        $row = $this->tableGateway->selectWith($passedSelect);
        
        return $row->current();
    }