Sort commerce view by calculated price

Question

I use commerce, search api and facets. I have a "shop" view with indexed products. It works good and I can filter and sort the view using "search api" facets and "search api" sorts fields.
I can sort the view by an indexed field, like product base price which is a field of a product object. The problem is this base price could change due to rules (discounts, offers, etc), and then, the final price for the customer could be different. I need to sort the "shop" view by the final customer prices, but I don't know how to do it with this indexed view. Any sugestion? TIA

Answer 1

Although I didn't find how to do it with views, I have found an alternative solution using views hooks.
First I have to unset the pager, because I need all records to sort. Only if it's this view and this sort criteria.

function MYMODULENAME_filters_views_pre_execute(&$view) {
  if (('shop' == $view->name) && ($view->exposed_raw_input['sort_by'] == 'field_product_commerce_price_amount_decimal_desc')) {
    $view->query->set_limit(NULL); 
    $view->query->set_offset(NULL);     
}}

After this, I reorder the $view->results as I need in another hook:

function MYMODULENAME_views_post_execute(&$view) {

    if (('shop' == $view->name) && ($view->exposed_raw_input['sort_by'] == 'field_product_commerce_price_amount_decimal_desc')) {

    $milista = array();
    foreach($view->result as $clave=>$valor) {
        $prdd = node_load($valor->entity);
        $producto = commerce_product_load($prdd->field_product['und'][0]['product_id']);
        $price = commerce_product_calculate_sell_price($producto);
        array_push($milista,array(0=>$clave, 1=>$valor->entity, 2=>$price['amount']));
    }

    if ($view->exposed_raw_input['sort_order']=='ASC') $ordenado = SortByKeyValue($milista, 2);
    else $ordenado = SortByKeyValue($milista, 2, SORT_DESC);

    $listafinal = array();
    foreach($ordenado as $datos) {
        array_push($listafinal, $view->result[$datos[0]]);
    }

    // Now, the pager
    $itemsperpage = 16;  // default items per page
    if ($view->exposed_raw_input['items_per_page']) $itemsperpage = $view->exposed_raw_input['items_per_page'];
    $page=1;
    if (isset($view->query->pager->current_page)) $page =  $view->query->pager->current_page;

    $indice = $itemsperpage * $page;

    if (($indice>sizeof($listafinal)) && ($itemsperpage!=0)) $indice = sizeof($listafinal) / $itemsperpage;

    $listafinal = array_slice($listafinal,$indice,$itemsperpage);

    // Lastly, I change the view results
    $view->result = $listafinal;
  }
}

To sort the array, I used the next functions:

function SortByKeyValue($data, $sortKey, $sort_flags=SORT_ASC)
{
    if (empty($data) or empty($sortKey)) return $data;

    $ordered = $data;

    if ($sort_flags==SORT_ASC) uasort($ordered,'cmpasc');
    else uasort($ordered,'cmpdesc');

    return $ordered;
}

function cmpasc($a, $b) {
    if ($a[2] == $b[2]) {
        return 0;
    }
    return ($a[2] < $b[2]) ? -1 : 1;
}

function cmpdesc($a, $b) {
    if ($a[2] == $b[2]) {
        return 0;
    }
    return ($a[2] > $b[2]) ? -1 : 1;
}

I hope this solution could help someone with my same use case.