Migrate ZF2 to ZF3

zend-db

#1

Hello, I am migrating an application from ZF 2.4 to ZF3, but I am having the following errors when calling the function getCountTicketsByStatus:

Fatal error: Uncaught exception 'Zend\Db\Sql\Exception\InvalidArgumentException' with message 'Argument type should be in array(identifier,literal,select,value)' in C:\EasyPHP\eds-www\zendframework3\project\vendor\zendframework\zend-db\src\Sql\AbstractExpression.php:82
Stack trace: 0 C:\EasyPHP\eds-www\zendframework3\project\vendor\zendframework\zend-db\src\Sql\AbstractExpression.php(57): Zend\Db\Sql\AbstractExpression->buildNormalizedArgument(NULL, false)1 C:\EasyPHP\eds-www\zendframework3\project\vendor\zendframework\zend-db\src\Sql\Expression.php(154): Zend\Db\Sql\AbstractExpression->normalizeArgument(Array, 'value')
2 C:\EasyPHP\eds-www\zendframework3\project\vendor\zendframework\zend-db\src\Sql\AbstractSql.php(130): Zend\Db\Sql\Expression->getExpressionData()
3 C:\EasyPHP\eds-www\zendframework3\project\vendor\zendframework\zend-db\src\Sql\AbstractSql.php(362): Zend\Db\Sql\AbstractSql->processExpression(Object(Zend\Db\Sql\Expression), Object(Zend\Db\Adapter\Platform\Mysql), Object(Zend\Db\Adapter\Driver\Pdo\Pdo), in C:\EasyPHP\eds-www\zendframework3\project\vendor\zendframework\zend-db\src\Sql\AbstractExpression.php on line 82

Could someone give me a direction I’m a bit rusty in zend …

<?php

namespace Ticket\Model;

use Zend\Db\Adapter\Driver\ConnectionInterface;
use Zend\Db\Adapter\Platform\Mysql as MysqlPlatform;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Sql\Expression;
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Where;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Insert;
use Zend\Db\TableGateway\TableGateway;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;

use Ticket\Model\TicketMessage;
use Ticket\Model\Ticket;

class TicketTable
{

// Tabela
protected $tableGateway;

public function __construct(TableGateway $tableGateway)
{
    $this->tableGateway = $tableGateway;
}
public function fetchAll()
{
    $resultSet = $this->tableGateway->select();
    return $resultSet;
}
// Retorno de dados paginados

public function fetchAllPaginated($id_user = 0, $filter)
{
$subcolumns1 = array(
‘lastdate’ => new Expression(‘MAX(t4.date)’),
);

  $subselect1 = new Select(array('t4' => 'db_ticket_message'));
  $subselect1->columns($subcolumns1);
  $subselect1->where(array('t4.id_ticket' => new Expression('t3.id_ticket')));
  
  $in = null;
  switch($filter)
  {
  	case TicketMessage::OPEN:
  	    $in = array(
  		    TicketMessage::OPEN,
  		);
  		break;
  	case TicketMessage::UPDATED:
  	    $in = array(
  		    TicketMessage::UPDATED,
  		);
  		break;
  	case TicketMessage::WAITING:
  	    $in = array(
  			TicketMessage::WAITING,
  		);
  		break;
  	case TicketMessage::RESOLVED:
  	    $in = array(
  			TicketMessage::RESOLVED,
  		);
  		break;
  	case TicketMessage::CLOSED:
  	    $in = array(
  			TicketMessage::CLOSED,
  		);
  		break;
  	default:
  	    $in = array(
  		    TicketMessage::OPEN,
  		    TicketMessage::UPDATED,
  			TicketMessage::WAITING,
  			TicketMessage::RESOLVED,
  			TicketMessage::CLOSED,
  		);
  	    break;
  }
  
  $on = new Expression(
      '? = ? AND ? = ? AND ? IN (?)', 
  	array(
  	    't1.id', 
  	    't3.id_ticket', 
  		't3.date',
  		$subselect1,
  		't3.status',
  		$this->tableGateway->getAdapter()
  		                   ->getPlatform()
  		                   ->quoteValueList($in)
  	), 
  	array(
  	    Expression::TYPE_IDENTIFIER, 
  		Expression::TYPE_IDENTIFIER, 
  	    Expression::TYPE_IDENTIFIER, 
  		Expression::TYPE_VALUE,
  		Expression::TYPE_IDENTIFIER,
  		Expression::TYPE_LITERAL
  	)
  ); 
  
    $columns = array(
        'id'             => 'id',
  	'createdate'     => 'date',
  	'subject'        => 'subject',
    );
  
    $columnst2 = array(
  	'author'         => 'id',
  	'name'           => 'name',
  	'lastname'       => 'lastname',
  );
    $columnst3 = array(
  	'lastupdate'     => 'date',
  	'laststatus'     => 'status',
  );
  
    $columnst5 = array(
  	'countmessages'     => 'count',
  );
  
    $subcolumns2 = array(
        'count' => new Expression('COUNT(*)'),
  	'id_ticket' => 'id_ticket',
  );
  $subselect2 = new Select('db_ticket_message');
  $subselect2->columns($subcolumns2);
  $subselect2->group('id_ticket');
  
  $select = new Select();
  $select->columns($columns);
  $select->from(array('t1' => 'db_ticket'));
  $select->join(array('t2' => 'db_user'), 't1.id_user = t2.id', $columnst2);
  $select->join(array('t3' => 'db_ticket_message'), $on, $columnst3);
  $select->join(array('t5' => $subselect2), 't5.id_ticket = t1.id', $columnst5);
  if($id_user != 0)
  {
      $select->where(array('t1.id_user' => $id_user));
  }
  $select->group('t1.id');
  $resultSetPrototype = new ResultSet();
  $paginatorAdapter = new DbSelect(
  	$select,
  	$this->tableGateway->getAdapter(),
  	$resultSetPrototype
  );
  $paginator = new Paginator($paginatorAdapter);
  return $paginator;

}

//Retorna o Cabeçalho do Ticket
public function getTicket($id)
{
$id = (int) $id;
$rowset = $this->tableGateway->select(array(‘id’ => $id));
$row = $rowset->current();
if (!$row)
{
return false;
}
return $row;
}

public function newTicket($user, $subject, $message)
{
  $retorno = false;

    $dataticket = array(
        'id_user' => $user->id,
        'subject' => $subject,
    );
  
  $dbadapter = $this->tableGateway->getAdapter();
  $connection = null;

  try {
  	$connection = $this->tableGateway->getAdapter()
  	                                 ->getDriver()
  									 ->getConnection();
  	$connection->beginTransaction();
  	
  	$this->tableGateway->insert($dataticket);
  	$id_ticket = $this->tableGateway->lastInsertValue;
  	$datamessage = array(
  		'id_user'   => $user->id,
  		'id_ticket' => $id_ticket,
  		'message'   => $message,
  	);
  	
  	$colunnsmessage = array(
  		'id_user', 
  		'id_ticket',
  		'message',
  	);
  	$insert = new Insert('db_ticket_message');
  	$insert->columns($colunnsmessage);
  	$insert->values($datamessage, $insert::VALUES_MERGE);
  	$statement = $this->tableGateway->getAdapter()->createStatement();
  	$insert->prepareStatement($this->tableGateway->getAdapter(), $statement);
  	$statement->execute();
  	$connection->commit();
  	$retorno = true;
  }
  
  catch (\Exception $e) {
  	if ($connection instanceof ConnectionInterface) 
  	{
  		$connection->rollback();
  	}
  }
  return $retorno;
}

// Retorna o numero de Tickets com o Status informado
public function getCountTicketsByStatus($id_user, $status)
{
$columnsempty = array();
$subcolumns1 = array(
‘lastdate’ => new Expression(‘MAX(t4.date)’),
);
$subselect1 = new Select(array(‘t4’ => ‘db_ticket_message’));
$subselect1->columns($subcolumns1);
$subselect1->where(array(‘t4.id_ticket’ => new Expression(‘t3.id_ticket’)));
$in = null;
if(!is_array($status))
{
switch($status)
{
case TicketMessage::OPEN:
$in = array(
TicketMessage::OPEN,
);
break;
case TicketMessage::UPDATED:
$in = array(
TicketMessage::UPDATED,
);
break;
case TicketMessage::WAITING:
$in = array(
TicketMessage::WAITING,
);
break;
case TicketMessage::RESOLVED:
$in = array(
TicketMessage::RESOLVED,
);
break;
case TicketMessage::CLOSED:
$in = array(
TicketMessage::CLOSED,
);
break;
default:
$in = array(
TicketMessage::OPEN,
TicketMessage::UPDATED,
TicketMessage::WAITING,
TicketMessage::RESOLVED,
TicketMessage::CLOSED,
);
break;
}
}
else
{
$in = $status;
}
$on = new Expression(
’? = ? AND ? = ? AND ? IN (?)’,
array(
t1.id’,
‘t3.id_ticket’,
‘t3.date’,
$subselect1,
‘t3.status’,
$this->tableGateway->getAdapter()
->getPlatform()
->quoteValueList($in)
),
array(
Expression::TYPE_IDENTIFIER,
Expression::TYPE_IDENTIFIER,
Expression::TYPE_IDENTIFIER,
Expression::TYPE_VALUE,
Expression::TYPE_IDENTIFIER,
Expression::TYPE_LITERAL
)
);
$select = new Select();
$select->columns(array(‘counttickets’ => new Expression(‘COUNT(*)’)));
$select->from(array(‘t1’ => ‘db_ticket’));
$select->join(array(‘t3’ => ‘db_ticket_message’), $on, $columnsempty);
if($id_user != 0)
{
$select->where(array(‘t1.id_user’ => $id_user));
}
$sql = new Sql($this->tableGateway->getAdapter());
$statement = $sql->prepareStatementForSqlObject($select);
$resultSet = $statement->execute();
$result = $resultSet->current();
return $result[‘counttickets’];
}

// Retorna o numero de Tickets com o Status informado

public function getCountTicketsPending($id_user)
{
$status = array(
TicketMessage::OPEN,
TicketMessage::UPDATED,
TicketMessage::WAITING,
TicketMessage::RESOLVED,
);
$count = $this->getCountTicketsByStatus($id_user, $status);
return $count;
}

}


#2

It’s simples in my inner join on having as select but im define it to VALUE by this is correct is SELECT:

	$on = new Expression(
	    '? = ? AND ? = ? AND ? IN (?)', 
		array(
		    't1.id', 
		    't3.id_ticket', 
			't3.date',
			$subselect1,
			't3.status',
			$this->tableGateway->getAdapter()
			                   ->getPlatform()
			                   ->quoteValueList($in)
		), 
		array(
		    Expression::TYPE_IDENTIFIER, 
			Expression::TYPE_IDENTIFIER, 
		    Expression::TYPE_IDENTIFIER, 
			Expression::**TYPE_VALUE**, // Replace to TYPE_SELECT
			Expression::TYPE_IDENTIFIER,
			Expression::TYPE_LITERAL
		)
	); 

Interesting that works perfectly in ZF2.

Now I just have to deal with: Usage of Zend \ ServiceManager \ ServiceManager ::getServiceLocator

:slight_smile:


#3

Since you seem to have solved your original issue, can this be closed: https://github.com/zendframework/zend-db/issues/251 ?