problems with category and views

Well, if your Drupal_term_node table is empty, then we've found the problem at last! This table is (as for the "fake Taxonomy") supposed to contain all the relations between nodes and terms, i.e. all existing ID-pairs of submitted nodes and their assigned Categories. THIS is where Views look to retrieve nodes for a given Category, so if there's nothing, you get no nodes listed then. (Also affects the Categories/Terms shown on node views, if supported by your theme.)

Weird thing is, that I also had this table empty once (just after upgrade and legacy import from Taxonomy to Category), so I suspect the Taxonomy-wrapper thing of having an undiscovered-yet bug causing this under some conditions. (Category package is not exactly bug-free, sadly.)

The table may be easily recovered by copying the contents from table Drupal_category_node, which is the original to be mirrored: Go to PhpMyAdmin (or similar), and do export of your Drupal_category_node table into SQL dump. Open the created file with a text-editor, and change all the occurences of "Drupal_category_node" to read "Drupal_term_node", and the column identificator "cid" to be "tid". Then import the tweaked file into your database, and if you're lucky, you'll have the tables in sync again. I've done exactly this on my site, and it did the trick with no side effects. But needless to say - always keep a backup of your database, while trying this kind of stuff.

But now another question arises: Are we sure that the tables won't come out of sync again in future, as new nodes are added to your site? Answer is: Not quite sure, neither of us know exactly what's happening. So I still suggest trying the solution I mentioned above, which is working perfectly well on my site:

Category module itself doesn't offer its own filters etc. for Views, it only just maintains "fake Taxonomy" - but not exactly bug-free, as we've seen. It works like this:

Node submission -> Drupal_category_node -> Taxonomy wrapper -> Drupal_term_node -> Filter by Taxonomy term ID -> Views

I've a more straight solution:

Node submission -> Drupal_category_node -> Filter by Category ID -> Views

This is logical, and it's pretty bombproof as it uses the "original" table directly, so we no more need to bother what happens with the poorly-maintained "mirror" (which might be still necessary for other purposes, though). All we need is a Category-filter for Views, the missing bit between the two modules.

To get this working, do the following:

--- Create a new file in sites/all/modules/views/modules/ (hopefully this is the path where your Views' .inc files are already located), name it views_category.inc, and paste the following inside:

<?php
//
// Here is where we expose category tables to views
//
function category_views_tables() {
if (module_exists('category')) {

  return array(
    'category_node' => array(
      'name' => 'category_node',
      'join' => array(
        'type' => 'left',
        'left' => array(
          'table' => 'node',
          'field' => 'nid',
        ),
        'right' => array(
          'field' => 'nid',
        ), // end right array
      ), // end join array

      'fields' => array(
        'cid' => array(
          'name' => t('Category ID'),
          'sortable' => true
         ), // end cid array
       ), // end fields array

      'filters' => array(
        'cid' => array(
          'name' => t('Category ID'),
          'operator' => 'category_handler_operator_eqto',
          'handler' => 'category_handler_filter_eqto',
          'help' => t('This filter allows to filter by category IDs.'),
        ), // end cid array
      ), // end filters
    ), // end category_node array
    
    'category' => array(
      'name' => 'category',
      'join' => array(
        'type' => 'inner',
        'left' => array(
          'table' => 'category_node',
          'field' => 'cid',
        ),
        'right' => array(
          'field' => 'cid',
        ), // end right array
      ), // end join array

      'fields' => array(
        'description' => array(
          'name' => t('Category: Description'),
          'handler' => array(
            'category_hand_fld_desc_lnk'   => t('With link'),
            'category_hand_fld_desc_nolnk' => t('Not a link')
           ),
          'addlfields' => array('cid'),
          'sortable' => true
         ) // end description array
       ), // end fields array

      'filters' => array(
        'cnid' => array(
          'name' => t('Container: ID'),
          'operator' => 'category_handler_operator_eqto',
          'handler' => 'category_handler_filter_eqto',
          'help' => t('This filter allows to filter by container-ID.'),
        ), // end cnid array
      ), // end filters

      'sorts' => array(
       'description' => array(
       'name' => 'Category: Description',
       'help' => t('Sorts by category description.')
       ) // end description array
      ) // end sorts array
    ), // end category

    'node_cat' => array(    // Another instance of node, to look after category names
      'name' => 'node',
      'join' => array(
        'type' => 'inner',
        'left' => array(
          'table' => 'category_node',
          'field' => 'cid',
        ),
        'right' => array(
          'field' => 'nid',
        ), // end right array
      ), // end join array

      'fields' => array(
        'title' => array(
          'name' => t('Category: Title'),
          'handler' => array(
            'category_hand_fld_titl_lnk'   => t('With link'),
            'category_hand_fld_desc_nolnk' => t('Not a link')
           ),
          'addlfields' => array('nid'),
          'sortable' => true
         ) // end description array
       ), // end fields array

      'filters' => array(
        'title' => array(
          'name' => t('Category: Title'),
          'operator' => 'views_handler_operator_like',
          'handler' => 'views_handler_filter_like',
          'help' => t('This filter allows nodes to be filtered by Category names.'),
        ), // end title array
      ), // end filters

      'sorts' => array(
       'title' => array(
       'name' => 'Category: Title',
       'help' => t('Sorts by category title.')
       ) // end title array
      ) // end sorts array

), // end node_cat


  ); // end return array
}
}


function category_views_arguments() {
  $args = array(
    'cid' => array(
      'name' => t("Category ID"),
      'handler' => 'category_handler_arg_cid',
      'help' => t('This argument filters for a given Category ID'),
      )
    );
  return $args;
}


function category_handler_arg_cid($op, &$query, $argtype, $arg = '') {
  switch($op) {
    case 'summary':
      $query->ensure_table('category');
      $query->add_field('description', 'category');
      $query->add_field('cid', 'category');
      $fieldinfo['field'] = "category.description";
      return $fieldinfo;
    case 'sort':
      $query->add_orderby('category', 'description', $argtype);
      break;
    case 'filter':
      $query->ensure_table('category_node');
      $query->add_where('category_node.cid = %s',$arg);
      $query->set_distinct();
      break;
    case 'link':
      return l($query->description, "$arg/" . intval($query->cid));
    case 'title':
      $result = db_query("SELECT description FROM {category} WHERE cid = %d", $query);
      $voc = db_fetch_object($result);
      return check_plain($voc->description);
  }
}

/**
*  Category-description/title as link (or not) handlers
*/

function category_hand_fld_desc_lnk($fieldinfo, $fielddata, $value, $data) {
  return l($value, "node/$data->category_cid");
}

function category_hand_fld_desc_nolnk($fieldinfo, $fielddata, $value, $data) {
  return check_plain($value);
}

function category_hand_fld_titl_lnk($fieldinfo, $fielddata, $value, $data) {
  return l($value, "node/$data->node_cat_nid");
}


/**
* A list of options to be used in equal to queries
*/
function category_handler_operator_eqto() {
  return array('=' => t('Is Equal To'), 'not' => t('Is NOT Equal To'));
}


/**
 * Filter for IDs
 */
function category_handler_filter_eqto($op, $filter, $filterinfo, &$query) {
  if (is_numeric($filter['value'])) {
    switch ($op) {
      case 'handler':
        $table = $filterinfo['table'];
        $column = $filterinfo['field'];
        $field = "$table.$column";
        $query->ensure_table($table);

        switch ($filter['operator']) {
          case '=':
            $query->add_where('%s = %s',$field,$filter['value']);
            break;
          case 'not':
            $query->add_where('%s != %s',$field,$filter['value']);
            break;
        }
      break;
    }
  }
}

(I know it's long, but I seem unable to attach files to this thread. The code basically came from a few other threads, and was later extended and heavily debugged by me. Still not done completely, perhaps you can see, some items around Argument page-titles still using Category description instead of title, not all the fields/filters defined that one might want... But for the purpose discussed here, it's fully working.)

--- Clear the Views' cache (button on Views, or just empty the table; if you get any problems, try clearing also other caches).

--- Now you should see in the Views some new things appear: Fields Category ID, Category description, Category title; filters by Category ID, Container ID, Category title; sorts by Category description, Category title; and the most important - Argument Category ID. Use this argument instead of previously suggested "Taxonomy term ID" on your View (better leave the other settings to this argument at default - other choices are not fully tested), and if you wish, you can also use the other new items, such as showing Category title assigned to each node (linked or not), and such.