Add custom orderBy in ViewsSort plugin

Question

I'm attempting to sort the results of a View by matching one of the Entity IDs in the result set to the referring URL Entity ID. I'm trying to do this in a custom Views Sort plugin. I did some research on Custom Views Sort plugins and came across this link:

https://medium.com/oleksandr-trotsenko/drupal-8-views-tutorial-for-developers-part-iii-sorts-filters-49baa40418de

Following the tutorial, I arrived at the simplest hook_views_data() entry:

$data['node_field_data']['nid'] = array(
    'title' => t('Custom trials sort'),
    'group' => t('Content'),
    'help' => t('Sort events by by referral source.'),
    'sort' => array(
      'id' => 'trials_sort'
    ),
  );

I added this sort to the View in question. In the class with the id trials_sort I've added this method:

public function query() {
  $this->ensureMyTable();
  $has_custom_sort_id = $this->detectCustomSorting();
  if($has_custom_sort_id !== FALSE){
    $id_to_top = $has_custom_sort_id;

    $sql_snippet = <<<EOF
CASE $this->tableAlias.nid
  WHEN nid = $id_to_top THEN 1 ELSE 2
END
EOF;
    $this->query->addOrderBy(NULL, $sql_snippet, $this->options['order']);
    dd($this->query);
  }

}

The goal was to get an ORDER BY like:

ORDER BY (CASE WHEN nid = $id_to_top THEN 1 ELSE 2 END);

All of this is executing on the appropriate view. However, Drupal is adding the SQL in an odd way. In the above code, my ORDER BY looks close to right:

But my fields array looks very wonky:

I'm clearly missing something that tells Drupal to interpret this additional orderBy as a formula. How do i do that?

Answer 1

As shown in this SO answer:

How to sort on different columns in the same view?

the views SQL object uses ->addField instead of ->addExpression. This code ended up working for me:

  public function query() {
    $this->ensureMyTable();
    $has_custom_sort_id = $this->detectCustomSorting();
    if($has_custom_sort_id !== FALSE){
      $id_to_top = $has_custom_sort_id;
      $this->query->addField(NULL,
        "nid = ".$id_to_top, 'is_match');
      array_unshift($this->query->orderby,['field' => 'is_match', 'direction' => 'DESC']);
    }
  }

The array_unshift just moves the is_match sort before the draggableviews sort which is also on this View. For some reason, re-arranging them in the View UI caused the draggableviews sort to drop off. Hope this helps someone in the future.