Contrib.social

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

Open on Drupal.org →
Created on 1 November 2017, about 7 years ago
Updated 20 February 2024, 9 months ago

Currently the views SQL class 'core/modules/views/src/Plugin/views/query/Sql.php Sql' has limitations in the sense that all joins, order by, group by, expressions, etc must belong to a group or field.

It would be nice to be able to have the same freedom when building/modifying queries as the Select class offers. (core/lib/Drupal/Core/Database/Query/Select.php Select)

eg. there is currently no way to call "addExpression" via hook_views_query_alter.

Yes we can call "addHavingExpression" or "addWhereExpression", however no way to add a select expression that is not related to a field.

Something like this would do the trick

public function addCustomExpression($string, $alias){
    $this->customExpressions[] = [
      'string' => $string,
      'alias' => $alias
    ];
  }

Then when building the query, something like this

protected function compileFields($query) {
    if(count($this->customExpressions) > 0){
      foreach($this->customExpressions as $ex){
        $query->addExpression($ex['string'], $ex['alias']);
      }
    }

Then of course a programmer may wish to order by this field, so we'd need some sort of custom order by to order by the field. Perhaps something like the below

public function addCustomOrderBy($field, $direction){
    $this->customOrderBys[] = [
      'field' => $field,
      'direction' => $direction
    ];
  }

Then inside the 'compileFields' method.

if(count($this->customOrderBys) > 0){
      foreach($this->customOrderBys as $ob){
        $query->orderBy($ob->field, $ob->direction);
      }
    }
}

I understand views is field based, however it would be nice to give developers the freedom to manipulate the sql to their choosing.

Cheers.

✨ Feature request
Status

Active

Version

11.0 🔥

Component
Views  →

Last updated about 15 hours ago

Created by

🇦🇺Australia Andrew211

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • Comment 9 months ago →
    🇬🇧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,
    ]);
  }
}
contrib.social Blog FAQ Discussions
Production build 0.71.5 2024