Prev/next links depends on taxonomy term referenced to node

I used this patch. It wouldn't automatically apply so I did it manually and so far the testing is looking good.

I found that if the term id wasn't chosen for the piece of content then this fails.

if (strlen($join) > 0) {
    // search category for current node
    $category = variable_get(PREV_NEXT_NODE_TYPE . $node_type . '_category_criteria', 'none');
    $category_id = db_query("SELECT " . $category . "_tid FROM {field_data_" . $category . "} WHERE entity_id = :nid", array(':nid' => $nid))->fetchField();
    $cond = " AND " . $category . "_tid = " . $category_id . " ";
    $cond .= _prev_next_node_types_sql($node_type);

Category ID is empty here and causes no nodes to be indexed and quit with a MySQL error.

Here's the code that I used to fixed the category_id is empty condition.

Attaching the git patch file.

/**
 * Create or update the prev_next records.
 */
function _prev_next_add($nid) {
  $node_type = db_query_range("SELECT type FROM {node} WHERE nid = :nid", 0, 1, array(':nid' => $nid))->fetchField();
  $search_criteria = variable_get(PREV_NEXT_NODE_TYPE . $node_type . '_indexing_criteria', PREV_NEXT_INDEXING_CRITERIA_DEFAULT);

  $join = _prev_next_category_sql($node_type);
  if (strlen($join) > 0) {
    // search category for current node
    $category = variable_get(PREV_NEXT_NODE_TYPE . $node_type . '_category_criteria', 'none');
    $category_id = db_query("SELECT " . $category . "_tid FROM {field_data_" . $category . "} WHERE entity_id = :nid", array(':nid' => $nid))->fetchField();
    if (empty($category_id)) {
      $cond = '';
      watchdog('prev_next', 'Node ' . $nid . ' missing taxonomy id for field_data_' . $category, WATCHDOG_ERROR);
    }
    else {
      $cond = " AND " . $category . "_tid = " . $category_id . " ";
      $cond .= _prev_next_node_types_sql($node_type);
    }
  } else {
    $cond = _prev_next_node_types_sql($node_type);
  }
  
  if ($search_criteria != 'nid') {
    $criteria_value = db_query_range("SELECT $search_criteria FROM {node} WHERE nid = :nid", 0, 1, array(':nid' => $nid))->fetchField();
    $next_nid = db_query_range("SELECT nid FROM {node} $join WHERE (($search_criteria = :value AND nid > :nid) OR $search_criteria > :value) AND status = 1 $cond ORDER BY $search_criteria ASC,nid ASC", 0, 1, array(':value' => $criteria_value, ':nid' => $nid))->fetchField();
    $prev_nid = db_query_range("SELECT nid FROM {node} $join WHERE (($search_criteria = :value AND nid < :nid) OR $search_criteria < :value) AND status = 1 $cond ORDER BY $search_criteria DESC,nid DESC", 0, 1, array(':value' => $criteria_value, ':nid' => $nid))->fetchField();
  }
  else {
    $next_nid = db_query_range("SELECT nid FROM {node} $join WHERE nid > :nid AND status = 1 $cond ORDER BY nid ASC", 0, 1, array(':nid' => $nid))->fetchField();
    $prev_nid = db_query_range("SELECT nid FROM {node} $join WHERE nid < :nid AND status = 1 $cond ORDER BY nid DESC", 0, 1, array(':nid' => $nid))->fetchField();
  }
  // Update the node-level data
  $exists = (bool) db_query_range('SELECT 1 FROM {prev_next_node} WHERE nid = :nid', 0, 1, array(':nid' => $nid))->fetchField();
  if ($exists) {
    db_update('prev_next_node')
      ->fields(array(
        'prev_nid' => ($prev_nid) ? $prev_nid : 0,
        'next_nid' => ($next_nid) ? $next_nid : 0,
        'changed' => REQUEST_TIME,
      ))
      ->condition('nid', $nid)
      ->execute();
  }
  else {
    $id = db_insert('prev_next_node')
      ->fields(array(
        'prev_nid' => ($prev_nid) ? $prev_nid : 0,
        'next_nid' => ($next_nid) ? $next_nid : 0,
        'changed' => REQUEST_TIME,
        'nid' => $nid,
      ))
      ->execute();
  }

  // Update the other nodes pointing to this node
  foreach (node_type_get_types() as $type => $name) {
    if (variable_get(PREV_NEXT_NODE_TYPE . $type, 0)) {
      $search_criteria = variable_get(PREV_NEXT_NODE_TYPE . $type . '_indexing_criteria', PREV_NEXT_INDEXING_CRITERIA_DEFAULT);
    
      $join = _prev_next_category_sql($node_type);
      if (strlen($join) > 0) {
        // search category for current node
        $category = variable_get(PREV_NEXT_NODE_TYPE . $node_type . '_category_criteria', 'none');
        $category_id = db_query("SELECT " . $category . "_tid FROM {field_data_" . $category . "} WHERE entity_id = :nid", array(':nid' => $nid))->fetchField();
        if (empty($category_id)) {
          $cond = '';
          watchdog('prev_next', 'Node ' . $nid . ' missing taxonomy id for field_data_' . $category, WATCHDOG_ERROR);
        }
        else {
          $cond = " AND " . $category . "_tid = " . $category_id . " ";
          $cond .= _prev_next_node_types_sql($node_type);
        }
      } else {
        $cond = _prev_next_node_types_sql($node_type);
      }

      if ($search_criteria != 'nid') {
        $criteria_value = db_query_range("SELECT $search_criteria FROM {node} WHERE nid = :nid", 0, 1, array(':nid' => $nid))->fetchField();
        $prev_nid = db_query_range("SELECT nid FROM {node} $join WHERE (($search_criteria = :value AND nid > :nid) OR $search_criteria > :value) AND status = 1 $cond ORDER BY $search_criteria ASC,nid ASC", 0, 1, array(':value' => $criteria_value, ':nid' => $nid))->fetchField();
        $next_nid = db_query_range("SELECT nid FROM {node} $join WHERE (($search_criteria = :value AND nid < :nid) OR $search_criteria < :value) AND status = 1 $cond ORDER BY $search_criteria DESC,nid DESC", 0, 1, array(':value' => $criteria_value, ':nid' => $nid))->fetchField();
      }
      else {
        $prev_nid = db_query_range("SELECT nid FROM {node} $join WHERE nid > :nid AND status = 1 $cond ORDER BY nid ASC", 0, 1, array(':nid' => $nid))->fetchField();
        $next_nid = db_query_range("SELECT nid FROM {node} $join WHERE nid < :nid AND status = 1 $cond ORDER BY nid DESC", 0, 1, array(':nid' => $nid))->fetchField();
        watchdog('prev_next', "SELECT nid FROM {node} $join WHERE nid > :nid AND status = 1 $cond ORDER BY nid ASC", array(':nid' =>  $nid));
        watchdog('prev_next', "SELECT nid FROM {node} $join WHERE nid < :nid AND status = 1 $cond ORDER BY nid DESC", array(':nid' =>  $nid));
      }

      if ($next_nid) {
        db_update('prev_next_node')
          ->fields(array(
            'next_nid' => $nid,
          ))
          ->condition('nid', $next_nid)
          ->execute();
      }
      if ($prev_nid) {
        db_update('prev_next_node')
          ->fields(array(
            'prev_nid' => $nid,
          ))
          ->condition('nid', $prev_nid)
          ->execute();
      }
    }
  }

}

rerolled this patch

I'm testing this patch now, there is an extra } on line 551 in the patch. Here is a new one that fixes it.

I'm marking this as needs work.

It works as designed for some nodes, but node for others.

I get the same node for previous/next on some nodes.

Here is a sample of the prev_next_node table:

Steps to reproduce:

1. Create a taxonomy vocabular Type
2. Add three terms, Sedan, Suv, Sport
3. Create a content type Vehicle
4. Add a term reference field to the Vehicle content type and select your Type Vocabulary.
5. Configure prev_next to index Vehicle nodes.
6. Choose include for vehicle
7. Select Post Date for the indexing criteria
8. Select Only nodes with the same content type
9. Choose Type for the criteria.
10. Use devel_generate to generate Vehicle nodes and set their created date to go back a year.
11. Re-index the site to generate the prev_next indexes.
12. Enable the prev_next block and view a Vehicle

Some will show properly, but others will have previous/next nodes that aren't in sequence and the previous/next link will be the same node.

MariaDB [d7_dev]> select * from prev_next_node;
+------+----------+----------+------------+
| nid  | prev_nid | next_nid | changed    |
+------+----------+----------+------------+
| 1001 |     1047 |     1017 | 1403901230 |
| 1002 |     1040 |     1035 | 1403901230 |
| 1003 |     1016 |     1020 | 1403901230 |
| 1004 |     1043 |     1047 | 1403901230 |
| 1005 |     1044 |     1031 | 1403901230 |
| 1006 |     1049 |     1009 | 1403901230 |
| 1007 |     1037 |     1034 | 1403901230 |
| 1008 |     1033 |     1050 | 1403901230 |
| 1009 |     1006 |     1006 | 1403901230 |
| 1010 |     1030 |     1042 | 1403901230 |
| 1011 |     1022 |     1040 | 1403901230 |
| 1012 |     1038 |     1015 | 1403901230 |
| 1013 |     1020 |     1036 | 1403901230 |
| 1014 |     1036 |     1038 | 1403901230 |
| 1015 |     1012 |     1012 | 1403901230 |
| 1016 |     1003 |     1003 | 1403901230 |
| 1017 |     1001 |     1001 | 1403901230 |
| 1018 |     1041 |     1024 | 1403901230 |
| 1019 |     1009 |     1009 | 1403901230 |
| 1020 |     1003 |     1003 | 1403901230 |
| 1021 |     1045 |     1041 | 1403901230 |
| 1022 |     1011 |     1011 | 1403901230 |
| 1023 |     1032 |     1029 | 1403901230 |
| 1024 |     1018 |     1018 | 1403901230 |
| 1025 |        0 |     1046 | 1403901230 |
| 1026 |     1039 |        0 | 1403901230 |
| 1027 |     1046 |     1037 | 1403901230 |
| 1028 |     1031 |     1043 | 1403901230 |
| 1029 |     1023 |     1023 | 1403901230 |
| 1030 |     1010 |     1010 | 1403901230 |
| 1031 |     1005 |     1005 | 1403901230 |
| 1032 |     1023 |     1023 | 1403901230 |
| 1033 |     1008 |     1008 | 1403901230 |
| 1034 |     1007 |     1007 | 1403901230 |
| 1035 |     1002 |     1002 | 1403901230 |
| 1036 |     1013 |     1013 | 1403901230 |
| 1037 |     1007 |     1007 | 1403901230 |
| 1038 |     1012 |     1012 | 1403901230 |
| 1039 |     1019 |     1019 | 1403901230 |
| 1040 |     1002 |     1002 | 1403901230 |
| 1041 |     1018 |     1018 | 1403901230 |
| 1042 |     1010 |     1010 | 1403901230 |
| 1043 |     1004 |     1004 | 1403901230 |
| 1044 |     1005 |     1005 | 1403901230 |
| 1045 |     1021 |     1021 | 1403901230 |
| 1046 |     1025 |     1025 | 1403901230 |
| 1047 |     1001 |     1001 | 1403901230 |
| 1048 |     1033 |     1033 | 1403901230 |
| 1049 |     1006 |     1006 | 1403901230 |
| 1050 |     1008 |     1008 | 1403901230 |

This updates builds upon the previous patches and integrates taxonomy indexing.

It also separates some of the code into include files, and adds documentation for some of the custom functions.

Please review and test.

This patch doesn't cover node deletion properly. Marking back to needs work.

I've incorporated the patch from xumepadismal, #1372754, that updates the index properly when deleting a node that seems to have solved the deletion problem in #12.

This patch fixes improper indexing of existing nodes and fixes the modification of the nodes that pointed to the last node as well.