- π¬π§United Kingdom scott_euser
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, ]); } }
- π¦πΉAustria electric.larry Vienna
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'); } }