A nice way to build a related content algorithm I am making a custom D7 module which has a block that lists related nodes of the current node you're viewing. The relation is determined by Taxonomy terms (from 1 vocab). The complication is that the related nodes need to be ordered by how many tags are matched in descending order.
How can you order nodes by matching tags? I have an idea of how to select nodes with matching tags, but have no clue as to how to order these by the number of matching tags. Any suggestions? Do it in SQL or in PHP?
Are there any modules that implement such functionality?
Simple case study for clarity
I have 2 content types: basic_page, article
Both of these content types have a same term reference field: field_tags
When someone opens a basic_page node that has been tagged with Tag A, Tag B and Tag C, I want to show article nodes that have been tagged with Tag A, Tag B and Tag C. But these article nodes need to be ordered by 'relevancy'. For example: the article nodes with all 3 tags matched must be at the top, and article nodes with 2 matching tags comes next, then the ones with 1 matching tag.
This was what I ended up writing to select nodes:

$query = db_select('node', 'n');
$query-&gt;addField('n', 'nid');
$query-&gt;addExpression('COUNT(n.nid)', 'ncount');
$query-&gt;innerJoin('taxonomy_index', 'ti', 'n.nid = ti.nid');
$query-&gt;condition('ti.tid', $tids, 'IN');
$query-&gt;condition('n.type', $types, 'IN');
$query-&gt;condition('n.nid', $node-&gt;nid, '&lt;&gt;'); // Exclude current node
$query-&gt;condition('n.status', 1); // Published
$query-&gt;condition(
  db_or()-&gt;condition('n.language', 'und')
         -&gt;condition('n.language', $node-&gt;language)
);
$query-&gt;groupBy('n.nid');
$query-&gt;groupBy('n.title');
$query-&gt;groupBy('n.created');
$query-&gt;orderBy('ncount', 'DESC');
$query-&gt;range(0, 10);

$results = $query-&gt;execute();


Kudos to Similar by Terms module, as I copied most of it from there. The magic happens when you order by ncount field.