Migrate ZF2 to ZF3

[diegobittencourt]

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;
	}
	
}

[weierophinney]

May I make a suggestion? Ask this in the forums. There are likely more folks there who can help you troubleshoot. If it turns out to be a bug, you can then come back here to report it.

[diegobittencourt]

Thank you for your attention.