My module adds a CCK field to an existing content type. How do I then modify an existing view to sort using the new field? I am building a new module ('best_answer') that builds on the functionality an existing module ('answers').

The new module adds a new CCK field ('best_answer_p') to a CCK content type ('answer') that was defined in the original module.

The original module also defined a view ('question_answers') which lists answer nodes, sorted by several factors. I'd like to modify this view so that it also sorts by the new field 'best_answer_p'. 

I could do this by simply overriding the view, using the Views UI. However, what this means is that if any other module also wants to override the view in some way, my 'best_answer' sort will be lost. 

So, I'd like to do this in a way that programmatically alters the existing view instead of overriding it. I am trying to do this using Hook_views_query_alter. But I run into an error.

Could anyone point me in the right direction?

Here is my code:

function best_answer_views_query_alter (&amp;$view, &amp;$query) {

  if ($view-&gt;name == 'question_answers') {

    // add the best answer field to the query
    $query-&gt;tables['node']['node_data_field_best_answer_p'] = array(
      'count' =&gt; 1,
      'alias' =&gt; 'node_data_field_best_answer_p',
    );

    // sort answers so that the best answer is displayed first 
    $new_orderby[0] = 'node_data_field_best_answer_p_field_best_answer_p_value DESC';
    foreach ($query-&gt;orderby as $key =&gt; $value) {
      $new_orderby[$key+1] = $value;
    }
    $query-&gt;orderby = $new_orderby; 

  }
}


Here is the error: 


  user warning: Unknown column 'node_data_field_best_answer_p_field_best_answer_p_value' in 'order clause' query: SELECT node.nid AS nid, votingapi_cache_node_points_vote_sum.value AS votingapi_cache_node_points_vote_sum_value, node.created AS node_created FROM node node LEFT JOIN votingapi_cache votingapi_cache_node_points_vote_sum ON node.nid = votingapi_cache_node_points_vote_sum.content_id AND (votingapi_cache_node_points_vote_sum.content_type = 'node' AND votingapi_cache_node_points_vote_sum.value_type = 'points' AND votingapi_cache_node_points_vote_sum.tag = 'vote' AND votingapi_cache_node_points_vote_sum.function = 'sum') LEFT JOIN content_type_answer node_data_field_answer_question ON node.vid = node_data_field_answer_question.vid WHERE (node.status &lt;> 0 OR (node.uid = 1 AND 1 &lt;> 0) OR 1 = 1) AND (node.type in ('answer')) AND (node_data_field_answer_question.field_answer_question_nid = 2) ORDER BY node_data_field_best_answer_p_field_best_answer_p_value DESC, votingapi_cache_node_points_vote_sum_value DESC, node_created DESC LIMIT 0, 10 in D:\xampp\htdocs\drupal\sites\all\modules\views\includes\view.inc on line 771.
You should override the view and change the sort. I have never seen a module that attempted to alter a view defined by another module.