I'm creating a method that gets values for createQueryBuilder () coming by parameters.
I tested it in my local database with pdo_mysql driver and it worked perfectly, by ordering both an int (id) field and a varchar (title) type field.
However, after testing in another database on an external server with driver pdo_sqlsrv (and also sqlsrv driver) it works when "order by" is by a field of type int (id), but when sorting is by a field of type varchar (title) gives timeout on the connection.The following are the return prints by the mysql database and by the SqlServer database:
public function getBy($fields, $orderBy, $where = null, $params = null)
{
if (empty($where))
$where = '1 = 1';
$query = $this->getEntityManager()->createQueryBuilder()
->select($fields)
->from($this->entity, $this->alias)
->where($where)
->add('orderBy', $orderBy)
->setFirstResult(URIHelper::$arrayParams['offset'])
->setMaxResults(URIHelper::$arrayParams['limit'])
->getQuery();
if (!empty($params))
$query->setParameters($params);
print_r($query->getSQL());
//print_r($query->getParameters());
//print_r($query->getArrayResult());
//die();
return $query->getArrayResult();
}
Return OK when using MySQL database sort order (varchar):
ReturnOKwhenusingSqlServerdatabasesortid(int):
ReturnERRORwhenusingSqlServerdatabasesortingtitle(varchar):