Show nodes by relevance block

I've been looking into "Relevant content" and "similar by terms" for sort by relevance.

They do the following requests:

$sql = sprintf(
  'SELECT n.nid, n.title, COUNT(n.nid) AS ncount '.
    'FROM {node} n '.
    'INNER JOIN {term_node} tn ON n.nid = tn.nid '.
    'WHERE tn.tid IN (%s) '.
      'AND n.type IN ('. $str_types .') '.
      'AND n.nid != %d '.
      'AND n.status = 1 '.
      'AND n.moderate = 0 '.
      'GROUP BY n.nid, n.title, n.created '.
      'ORDER BY ncount DESC, n.created DESC '.
      'LIMIT %d',
    terms, $nid, $count);

and this

SELECT
  n.nid AS nid,
  n.vid AS vid,
  n.title AS title,
  n.type AS type,
  COUNT(*) AS cnt
FROM {node} n
LEFT JOIN {term_node} tn ON tn.nid = n.nid AND {$term_sql}
WHERE {$types_sql} AND n.status = 1 AND tn.tid IS NOT NULL {$exclude_sql}
GROUP BY n.nid
ORDER BY cnt DESC, n.created DESC, n.nid DESC
LIMIT %d

with $term_sql = 'tn.tid IN('. db_placeholders($terms, 'int') .')';

Therefore I thought that even without the relevance information in the DB, Views could do the same.

My problem is to understand if it would be possible to integrate this in views as a complementary module and how I could achieve this.

I believe that the second part of my question is out of scope of views as it requires a second db request. This requirement is a customer request in order to make sure there will always be X entries in the side blocks. My point of view is that it should only be interesting at the beginning of the site as when the amount of content grows, the need of filling empty spaces will disappear.

I already saw that module as mentioned in my previous message but it does not fit my needs.

I have chosen to create a module providing a universal function to get what I need.
It takes the following arguments:
. content types
. terms
. amount of results
. excluded nodes
. optional other criterion

I have abandoned to provide nodes that are not related (but recent) but it would be easy to implement.

Here it is:

/*
 * related_get_related
 *
 * generated list of nids based on terms and content types.
 *
 * @param array() $terms
 * array of terms to include in the query
 * array('term1', 'term2')
 *
 * @param array() $types
 * array of content types to limit the query
 *
 * @param interger $limit
 * amount of results.
 *
 * @param array() $excluded_nodes
 * array of node ids to exclude from the result list
 *
 * @param array() $data
 * array of additional options
 * supported keys:
 * - 'join' : joins (ex. "LEFT JOIN {term_node} tn ON tn.nid = n.nid")
 *   n is the main node table
 * - 'where' : where clauses
 *
 * @return array()
 * array of nids sorted by relevance to the given terms then sorted
 * to the sticky property and finally to the date (update OR given "publication date")
 * 
 */
function related_get_related($terms, $types, $limit, $excluded_nodes = array(), $data = array()) {
  global $language;
  static $_related_cache;
  $sql_types = '';
  $sql_where_excludes = '';
  $sql_terms = '';
  $sql_cck = '';
  $sql_join = '';

  $cache = variable_get('cache', 1);
  $cache_age = variable_get('cache_lifetime', 60);
  
  $key = $language->language;

  // this query may be called several times on the same page we save it in a variable.

  // $sql_types
  switch (count($types)) {
    case 0:
      $sql_types = '';
      $key .= 'all:';
      break;
    case 1:
      $sql_types = 'LIKE \''. $types[0] .'\'';
      $key .= $types[0] . ':';
      break;
    default:
      $sql_types = 'IN (\''. join('\',\'', $types) .'\')';
      $key .= join(',', sort($types)) . ':';
  }

  // $sql_where_excludes
  if (is_array($excluded_nodes)) {
    switch (count($excluded_nodes)) {
      case 0:
        $sql_where_excludes .= '';
        $key .= 'none:';
        break;
      case 1:
        $sql_where_excludes .= 'AND n.nid != '. $excluded_nodes[0];
        $key .= $excluded_nodes[0];
        break;
      default:
        $sql_where_excludes .= 'AND n.nid NOT IN ('. join(',', $excluded_nodes) .')';
        sort($excluded_nodes);
        $key .= join(',', $excluded_nodes) .':';
    }
  }

  // $sql_terms
  if (is_array($terms)) {
    switch (count($terms)) {
      case 0:
        $sql_where_terms = ' ';
        $key .= 'all';
        break;
      case 1:
        $sql_join .= 'LEFT JOIN {term_node} tn ON tn.vid = n.vid';
        $sql_where_terms = ' AND tn.tid = '. join(array_keys($terms));
        $key .= join(array_keys($terms));
        break;
      default:
        $sql_join = 'JOIN {term_node} tn ON tn.vid = n.vid AND tn.tid IN ('. join(',', array_keys($terms)) .')';
        $keys = array_keys($terms);
        sort($keys);
        $key .= join(',', $keys);
    }
  }
  
  // data - additional query information
  if (count($data)){
    $join = $data['join']?$data['join']:'';
    $where = $data['where']?$data['where']:'';
    $sql_cck_join .= $join;
    $sql_where_excludes .= $where;
    $key .= $join . $where;
  }
  
  $key = md5($key);
  if (isset($_related_cache[$key])) {
    return $_related_cache[$key];
  }
  if ($cache && $data = cache_get('related:'. $key)){
    return $data->data;
  }
    

  // we manage the manually entered date (CCK field_date).
  $sql_cck_select = ', IF(cn.field_date_value, UNIX_TIMESTAMP(cn.field_date_value), n.changed ) AS contentdate';
  $sql_cck_join .= ' LEFT JOIN {content_field_date} cn ON cn.vid = n.vid';
  $sql_cck_order .= ', contentdate DESC';

  $query = "
    SELECT n.nid, n.title AS title, COUNT(*) AS cnt $sql_cck_select
    FROM {node} n
    $sql_join
    $sql_cck_join
    WHERE n.type $sql_types $sql_where_excludes $sql_where_terms
    GROUP BY n.nid
    ORDER BY cnt DESC , n.sticky DESC $sql_cck_order
    LIMIT $limit
    ";

  $nids = array();
  if ($results = db_query(db_rewrite_sql($query))) {
    while ($i = db_fetch_object($results)) {
      $nids[] = $i->nid;
    }
  }
  $_related_cache[$key] = $nids;
  if ($cache){
    cache_set('related:'. $key, $nids, 'cache', time() + $cache_age);
  }
  return $nids;
}