10

I am trying to create a custom sorting option for view that will sort result on the basis of percentage of 2 fields.

field 1 = node__field_funding_amount field 2 = node__field_funding_goal

and i am trying to do something like below

$percentage = "round(( $this->tableAlias.$this->realField/node__field_funding_goal.field_funding_goal_value * 100 ),2)";
    $this->query->addOrderBy(NULL,
      $percentage
    );

Full code is here 1:- code in views.inc

function crowdfundingproject_views_data_alter(array &$data) {
  $data['node__field_funding_amount']['views_cfp_almost_full'] = array(
    'title' => t('CFP Almost Full'),
    'group' => t('Content'),
    'help' => t('Project almost full'),
    'field' => [
      'id' => 'almost_full',
      'click sortable' => TRUE,
    ],
    'sort' => [
      'field' => 'field_funding_amount_value',
      'id' => 'funding-percent',
    ],
  );
}

2:- Code in sorting class

use Drupal\views\Plugin\views\sort\SortPluginBase;

/**
 * Basic sort handler for Events.
 *
 * @ViewsSort("funding-percent")
 */
class FundingPercent extends SortPluginBase {

  /**
   * Called to add the sort to a query.
   */
  public function query() {
    $this->ensureMyTable();
    $percentage = "round(( $this->tableAlias.$this->realField/node__field_funding_goal.field_funding_goal_value * 100 ),2)";
    $this->query->addOrderBy(NULL,
      $percentage
    );
  }

}

But it's showing me column not found error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'node__field_funding_goal.field_funding_goal_value' in 'field list': SELECT node_field_data.created AS node_field_data_created, node_counter.totalcount AS node_counter_totalcount, node__field_funding_end.field_funding_end_value AS node__field_funding_end_field_funding_end_value, node_field_data.nid AS nid, ABS(round(( node__field_funding_amount.field_funding_amount_value/node__field_funding_goal.field_funding_goal_value * 100 ),2)) AS absroundnode__field_funding_amountfield_funding_amount_ FROM {node_field_data} node_field_data LEFT JOIN {node_counter} node_counter ON node_field_data.nid = node_counter.nid LEFT JOIN {node__field_funding_end} node__field_funding_end ON node_field_data.nid = node__field_funding_end.entity_id AND node__field_funding_end.deleted = :views_join_condition_0 LEFT JOIN {node__field_funding_amount} node__field_funding_amount ON node_field_data.nid = node__field_funding_amount.entity_id AND node__field_funding_amount.deleted = :views_join_condition_1 WHERE (node_field_data.status = :db_condition_placeholder_2) AND (node_field_data.type IN (:db_condition_placeholder_3)) ORDER BY node_field_data_created DESC, node_counter_totalcount DESC, absroundnode__field_funding_amount.field_funding_amount_ ASC LIMIT 6 OFFSET 0; Array ( [:db_condition_placeholder_2] => 1 [:db_condition_placeholder_3] => crowdfunding_project [:views_join_condition_0] => 0 [:views_join_condition_1] => 0 )
1
  • you figure this out? I cant even get my custom sort filter to show up
    – Ronnie
    Jan 26, 2019 at 0:38

1 Answer 1

1

There is an error in your SQL so you're getting the column not found error. If you break up your query, you should be able to get it.

Since this question has 1k views and no answers, and I just had to do this myself, here are some helpful resources for anyone looking to build their own Views custom sort plugin in Drupal 8/9.

First, this blog post on Medium by Oleksandr Trotsenko has a detailed tutorial on writing custom sorts and filters for Views. The example is to take a product weight and weight unit fields to sort all products by weight, regardless of whether that weight is recorded in lb or kg. There are code examples for hook_views_data() and the views sort plugin.

There's another tutorial by Clare Ming on how to extend a datefield to add a sort that shows dates in the future first, and then dates in the past in reverse chronological order. It also has code examples.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.