Intergration with views

Hi,

I am trying to integrate the CRE module with views.
My main goals are:

1. Score field with a widget (possibly using something like fivestar)
2. Sort and filter settings for the score field
3. there should be 2 types of score fields:
1. type 1 returns only the nodes that can be scored (just like my recommendation)
2. type 2 returns all nodes where those that don't have a score has some predefined value (like NULL)

The type 1 query is:
SELECT
node.nid,
node.title AS node_title,
node.changed AS node_changed,
sum(cre_similarity_matrix.sum+cre_similarity_matrix.count*cre_similarity_matrix_voting.value)/sum(cre_similarity_matrix.count) AS score
FROM
node node
inner JOIN
cre_similarity_matrix cre_similarity_matrix
ON
node.nid = cre_similarity_matrix.content_id1
inner JOIN
votingapi_vote cre_similarity_matrix_voting
ON
cre_similarity_matrix.content_id2 = cre_similarity_matrix_voting.content_id
AND cre_similarity_matrix_voting.uid = '1'
WHERE
(cre_similarity_matrix.content_id1<>cre_similarity_matrix_voting.content_id)
GROUP BY
cre_similarity_matrix.content_id1
ORDER BY
score desc

This query is fast around 0.1 seconds on my machine for 1 thousand nodes and several thousand votes.

the second type of query is:
SELECT
node.nid,
node.title AS node_title,
node.changed AS node_changed,
sum(cre_similarity_matrix.sum+cre_similarity_matrix.count*cre_similarity_matrix_voting.value)/sum(cre_similarity_matrix.count) AS score
FROM
node node
left JOIN
cre_similarity_matrix cre_similarity_matrix
ON
node.nid = cre_similarity_matrix.content_id1
left JOIN
votingapi_vote cre_similarity_matrix_voting
ON
cre_similarity_matrix.content_id2 = cre_similarity_matrix_voting.content_id
AND cre_similarity_matrix_voting.uid = '1'
WHERE
(cre_similarity_matrix.content_id1<>cre_similarity_matrix_voting.content_id) or cre_similarity_matrix.content_id1 is NULL
GROUP BY
cre_similarity_matrix.content_id1
ORDER BY
score desc

this takes 40 seconds.
if i add:
AND cre_similarity_matrix_voting.content_type='node'
to the last ON then i get around 3.8 seconds.
this is as fast as i got with this (including adding al sorts of different indexes to the table).

if any one can help me here it will be really appreciated.

Thanks, Eli

Here is the code that implements the type 1 query (just add it to the cre.module):


function cre_views_tables(){
    $tables ['cre_similarity_matrix_voting'] = array(
    'name' => 'votingapi_vote',
    'join' => array(
      'type' => 'inner',
      'left' => array(
        'table' => 'cre_similarity_matrix',
        'field' => 'content_id2'
      ),
      'right' => array(
        'field' => 'content_id'
      ),
      'extra' => array(
        'uid' => '***CURRENT_USER***'
      )
    ),
   
    'fields' => array(
      'score' => array(
        'field' => 'score',
        'name' => t('cre: score'),
        'help' => t('the score the cre gives this node, only nodes that can be scored will be shown'),
        'sortable' => TRUE,
        'notafield' => TRUE,
        'query_handler' => 'cre_views_field_query_handler',
        'handler' => 'cre_score_field_handler'
      )
    )
  );
     
  $tables ['cre_similarity_matrix'] = array(
   'name' => 'cre_similarity_matrix',
    'join' => array(
      'type' => 'inner',
      'left' => array(
        'table' => 'node',
        'field' => 'nid'
      ),
      'right' => array(
        'field' => 'content_id1'
      )
    )
  );
   
  return $tables;
}

function cre_views_field_query_handler($fielddata, $fieldinfo, &$query){
  $query->add_field('sum(cre_similarity_matrix.sum+cre_similarity_matrix.count*cre_similarity_matrix_voting.value)/sum(cre_similarity_matrix.count)', '','score');
  $query->ensure_table('cre_similarity_matrix_voting', true);
  $query->add_where('cre_similarity_matrix.content_id1<>cre_similarity_matrix_voting.content_id');
  $query->add_orderby('','sum(cre_similarity_matrix.sum+cre_similarity_matrix.count*cre_similarity_matrix_voting.value)/sum(cre_similarity_matrix.count)','desc','score');
  $query->add_groupby('node.nid', true);
}

function cre_score_field_handler($fieldinfo, $fielddata, $value, $data) {
  return $data->score;
}