Sorting in views not working for drupal commerce products

Question

In the product views of my drupal commerce site, I want to list the products sorted by amount(price).

The product has 4 categories. They are added as relationships in the view:

Now to sort them by price, I am doing the following:

This is working perfectly fine in another view where I am using Contextual filters(Product category).

But in this view, where I am displaying products of all categories, the sorting is not working. What is happening in this case is:

• First all the Poultry items are coming in sorted(by price) order.

• Then all the Fish items in sorted(by price) order.

• And so on.

What I want is :

All Items should be collectively sorted by price.

Answer 1

This isn't working as configured because you're using separate product reference fields per content type. This instructs the query to attempt a sort based on non-existent values for those rows where the content type does not have the expected reference field. To sort this View properly, you'd need the base table to be the Products and then some sort of a reverse reference to content referencing those products.

Answer 2

I sorted my view using hook_views_pre_render() and array_multisort().

function MY_MODULE_views_pre_render(&$view) {
      if($view->name == 'MY_VIEW'){   
        $price_array = array();
        foreach($view->result as $key => $row) {
          $price = '0';
          if(isset($row->field_commerce_price[0])){
            $price = $row->field_commerce_price[0]['raw']['amount'];
          } elseif (isset($row->field_commerce_price_1[0])) {
            $price = $row->field_commerce_price_1[0]['raw']['amount'];
          } elseif (isset($row->field_commerce_price_2[0])) {
            $price = $row->field_commerce_price_2[0]['raw']['amount'];
          } elseif (isset($row->field_commerce_price_3[0])) {
            $price = $row->field_commerce_price_3[0]['raw']['amount'];
          }
          $price_array[$key] = $price;
        }
        array_multisort($price_array, SORT_ASC, $view->result);
      }
    }