Migrate ZF2 to ZF3
Closed this issue · 2 comments
diegobittencourt commented
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 commented
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 commented
Thank you for your attention.