$query->addExpression and $query->orderBy in views Sql

Just an update on @dravenk's example, here is how the code looks now in D10 + including some WHERE condition examples.

use Drupal\views\ViewExecutable;

/**
 * Implements hook_views_pre_execute().
 */
function mymodule_views_pre_execute(ViewExecutable $view) {
  if($view->id() == 'my_view_name') {
    $query = &$view->build_info['query'];

    // Add a SELECT expression.
    $query->addExpression('CONVERT(node_field_data.title USING GBK)','custom_name_of_field');

    // Add a WHERE expression in MySQL.
    $query->addWhereExpression(0, "COALESCE(some_alias.date, '1900-01-01') < :some_value", [
      ':some_value' => $value_here,
    ]);

    // Add a WHERE expression in SQLite.
    $query->where("COALESCE(some_alias.date, '1900-01-01') < :some_value", [
      ':some_value' => $value_here,
    ]);
  }
}

Thank you @scott_euser and @dravenk for your great examples!

I landed here looking for a way to implement natural sorting of a text field in a view.

My workaround was to use LPAD on the text field and then sort by this field.

/**
 * @file
 * Custom module to implement natural sort order for staff order views.
 */

use Drupal\views\ViewExecutable;
use Drupal\views\Plugin\views\query\QueryPluginBase;
use Drupal\views\Plugin\views\query\Sql;
/**
 * Implements hook_views_query_alter().
 *
 * Alters the query for the "personalbestellungen" view to sort by
 * the custom sort field.
 *
 * @param \Drupal\views\ViewExecutable $view
 *   The view executable object.
 * @param \Drupal\views\Plugin\views\query\Sql $query
 *   The query object for the view.
 */
function wet_personalbestellung_views_query_alter(ViewExecutable $view, Sql $query) {
  if ($view->id() == "personalbestellungen" && $view->current_display === 'page_2') {
    // Add the custom sorting field to the view's query.
    array_unshift($query->orderby, [
      'field' => 'custom_sort_order',
      'direction' => 'DESC',
    ]);
  }
}

/**
 * Implements hook_views_pre_execute().
 *
 * Adds a custom expression for sorting in the "personalbestellungen" view.
 *
 * @param \Drupal\views\ViewExecutable $view
 *   The view executable object.
 */
function wet_personalbestellung_views_pre_execute(ViewExecutable $view) {
  if ($view->id() == "personalbestellungen" && $view->current_display === 'page_2') {
    // Prepend zeros to the order_number, to mimic natural sorting.
    $view->build_info['query']->addExpression("LPAD(order_number, 100, '0')",'custom_sort_order');
  }
}