By jashirouprotips on

Hi, this is my first topic here and I hope have more clearly this doubt.

On D8 I could cast a field into a "orderBy" dynamic queries, but now in D9 I got an error, and I try to use other ways to CAST or CONVERT a field but I still got syntax errors...

I have this on my storage php file that is into a function I called from a Controller file:

$order_field is received from the Controller php file.

static function getSortedLimit($limit,$order_field) {...

$connection->orderBy('DCR.' . $order_field, 'DESC');

}

and theres no error, but the field is an string so the order is not 100% logical so  when I try to cast the field like this:

$connection->orderBy('cast(DCR.numero AS UNSIGNED)' ,'DESC');

I receive this log error:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'castDCR.numeroASUNSIGNED' in 'order clause':

I don't understand why in D8 this same way to cast a field in the "orderBy" it's works and now in D9 doesn't not.

Someone knows how can I do to convert string to a integer in the "orderBy" dynamic queries?

I tried to use

$orderBy = $connection->addExpression('CONVERT(DCR.' . $order_field , int);

$connection->orderBy($orderBy, 'DESC');

but doesn't work neither, I got the same before log error.

Thanks in advance.

⋅ Categories: Drupal 9.x

Comments

dimon4ikzp’s picture

dimon4ikzp commented

 I had similar problem, this way it works in D9.

$query = $connection->select('invoice_field_data')
  ->condition('status', 1)
$query->addField('invoice_field_data', 'label');

$orderByField = $query->addExpression('CAST(SUBSTRING(label,2,5) AS SIGNED)', 'last_num');
$query->orderBy($orderByField, 'DESC');

$last_num = $query
  ->execute()
  ->fetchField();