3

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.

  • Maybe an average of levenshtein sums could fit? See Levenshtein Wikipedia, and Levenshtein PHP. – Strae May 30 '12 at 11:49
  • possible duplicate of How to create a list of similar nodes in Drupal 7/Views 3 – Letharion May 30 '12 at 12:31
  • @Letharion It's not a duplicate. – kiamlaluno May 30 '12 at 12:56
  • It's quite close :) The answer on that question, particularly the module there has helped me come up with a solution. Thanks @Letharion – Beebee May 30 '12 at 12:59
  • @kiamlaluno I defer to your judgement, but I don't see why? If it's because the other question has "Views" in the title, then wouldn't it make more sense to just remove that? – Letharion May 30 '12 at 14:26
3

This was what I ended up writing to select nodes:

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

$results = $query->execute();

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.