By somebodysysop on

My problem: I wish to create Views of my nodes filtered and sorted by categories (from category table) rather than taxonomy vocabulary terms. The Views module only sees vocabulary terms (even with category_views, which depends upon taxonomy wrapper to create a "mirror" of categories as terms). I can't use taxnonomy wrapper because it appears to break OG forums.

So, I now wish to "expose" the category table to the Views module. I've read the Views Module API documentation here: http://drupal.org/node/42609, but I don't even know where to start. I know that I need to indirectly link the category table to the node table via the category_node table (which lists which categories are assigned to which nodes), but I don't know where to put this code and a bunch of other stuff.

Can someone help, please?

Thanks.

-ron

⋅ Categories: Drupal 4.7.x

Comments

somebodysysop’s picture

somebodysysop commented

Here's what I did and it works:

I modified category.inc. I tried using template.php in my theme, but for some reason that did not work. Don't know why.

function category_views_tables() {

  return array(
    'category_node' => array(
      'name' => 'category_node',
      'join' => array(
        'left' => array(
          'table' => 'node',
          'field' => 'nid',
        ),
        'right' => array(
          'field' => 'nid',
        ),
      ),
      'fields' => array(
        'cid' => array(
          'name' => t('Category: ID'),
          'sortable' => true
         )
       )
    ),
    'category' => array(
      'name' => 'category',
      'join' => array(
        'left' => array(
          'table' => 'category_node',
          'field' => 'cid',
        ),
        'right' => array(
          'field' => 'cid',
        ),
      ),
      'fields' => array(
        'description' => array(
          'name' => t('Category: Description'),
          'sortable' => true
         )
       ),
      'sorts' => array(
       'description' => array(
       		'name' => 'Category: Description')
      ),
    )
  );
}

It works. Now, all I have to do is figure out how syntax for creating argument and filter options.

armanjava’s picture

armanjava commented

I'm using the category module and also the OG module. How does it break the OG forums?

Also, i'm trying to create views with exposed filters for users to filter on the category items but it's not working, is that because views looks at category terms and taxonomy terms differently and the exposed filters only work with the old taxonomy module?

Thanks in advance for any help. Oh, and did you ever find a complete solution to the above? you said all that was left is to figure out the syntax for creating argument and filter options? I'm not a coder and don't know what that means, so if you could ellaborate on how you found your solution, that would be great.

somebodysysop’s picture

somebodysysop commented

In *my* case, it is the taxonomy wrapper that breaks OG forums, not the categor module.

I'm not an expert at views and php myself, but below is what I put together in category.inc:

//
// Here is where we expose category table to views
//
function category_views_tables() {

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

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

'filters' => array(
'cid' => array(
'name' => t('Category Node: ID'),
'operator' => 'category_handler_operator_eqto',
'handler' => 'category_handler_filter_eqto',
'help' => t('This filter allows categories to be filtered by their IDs.'),
), // end cid array
'nid1' => array(
'field' => 'nid',
'name' => t('Category Node: Node ID'),
'operator' => 'category_handler_operator_eqto',
'handler' => 'category_handler_filter_eqto',
'help' => t('This filter allows categories to be filtered by their node IDs.'),
), // end nid array
'nid2' => array(
'field' => 'nid',
'name' => t('Category Node: ID = Node ID'),
'operator' => 'category_handler_operator_eqto',
'handler' => 'category_handler_filter_nid_a',
'help' => t('This filter filters by only allowing nodes which match category_node.nid = or not = category_node.cid. Use this to determine if node ID is parent or not. This only works if you only have one level.'),
), // end nid array
'nid3' => array(
'field' => 'nid',
'name' => t('Category Hierarchy Parent + Category Node: ID = Node ID'),
'operator' => 'category_handler_operator_eqto',
'handler' => 'category_handler_filter_nid_b',
'help' => t('This filters by only allowing nodes which match the Category Hierarchy parent id entered plus category_node.nid = category_node.cid. Use this to determine if node ID is parent level or not.'),
), // end nid array
), // end filters
), // end category_node array

'category' => array(
'name' => 'category',
'join' => array(
'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'),
'sortable' => true
) // end description 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 categories to be filtered by their IDs.'),
), // end cid array
'description' => array(
'name' => t('Category: Description'),
'operator' => 'views_handler_operator_like',
'handler' => 'category_handler_filter_like',
'help' => t('This filter allows categories to be filtered by their descriptions.'),
), // end description array
'cnid' => array(
'name' => t('Container: ID'),
'operator' => 'category_handler_operator_eqto',
'handler' => 'category_handler_filter_eqto',
'help' => t('This filter allows categories to be filtered by their containers.'),
), // end cnid array
), // end filters

'sorts' => array(
'description' => array(
'name' => 'Category: Description') // end description array
) // end sorts array

), // end category

'category_hierarchy' => array(
'name' => 'category_hierarchy',
'join' => array(
'left' => array(
'table' => 'category',
'field' => 'cid',
),
'right' => array(
'field' => 'cid',
), // end right array

), // end join array

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

'filters' => array(
'parent' => array(
'name' => t('Category Hierarchy: Parent'),
'operator' => 'category_handler_operator_eqto',
'handler' => 'category_handler_filter_eqto',
'help' => t('This filter allows categories to be filtered by their parent categories.'),
), // end parent array
'cid' => array(
'name' => t('Category Hiearchy: ID'),
'operator' => 'category_handler_operator_eqto',
'handler' => 'category_handler_filter_eqto',
'help' => t('This filter allows categories to be filtered by their parent categories.'),
) // end parent array
) // end filters array

) // end category_hiearchy array

); // 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'),
),
'parent' => array(
'name' => t("Category Hierarchy: Parent"),
'handler' => 'category_handler_arg_parent',
'help' => t('This argument filters for a given Category Hierarchy Parent'),
),
// 'parent' => array(
// 'name' => t("Category: Parent"),
// 'handler' => 'category_handler_arg_parent',
// 'help' => t('This argument filters for a given Category parent 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');
$query->add_where('category.cid = %d', $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);
$title = check_plain($voc->description);
return $title;
}
}
/**
* A list of options to be used in LIKE queries
*/
function category_handler_operator_like() {
return array('=' => t('Is Equal To'), 'contains' => t('Contains'), 'starts' => t('Starts With'), 'ends' => t('Ends With'), 'not' => t('Does Not Contain'));
}

/**
* 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'));
}

/**
* Custom filter for LIKE operations
* Modified original operation in the case that value is numeric
*/
function category_handler_filter_like($op, $filter, $filterinfo, &$query) {
switch (trim($filter['value'])) {
case (''):
return;
break;
}
switch ($op) {
case 'handler':
$fieldbits = explode('.', $filter['field']);
$query->ensure_table($fieldbits[0]);
switch ($filter['operator']) {
case 'contains':
$query->add_where("UPPER(%s) LIKE UPPER('%%%s%%')",
$filter['field'], $filter['value']);
break;
case 'starts':
$query->add_where("UPPER(%s) LIKE UPPER('%s%%')",
$filter['field'], $filter['value']);
break;
case 'ends':
$query->add_where("UPPER(%s) LIKE UPPER('%%%s')",
$filter['field'], $filter['value']);
break;
case 'not':
$query->add_where("UPPER(%s) NOT LIKE UPPER('%%%s%%')",
$filter['field'], $filter['value']);
break;
case '=':
$query->add_where("UPPER(%s) = '%%%s%%' OR %s = %s",
$filter['field'], $filter['value']);
break;
}
break;
}
}
/**
* Custom filter for equal to operations
* Modified original operation in the case that value is numeric
*/
function category_handler_filter_eqto($op, $filter, $filterinfo, &$query) {
switch (trim($filter['value'])) {
case (''):
return;
break;
}
switch ($op) {
case 'handler':
$fieldbits = explode('.', $filter['field']);
$query->ensure_table($fieldbits[0]);
switch ($filter['operator']) {
case '=':
$query->add_where("%s = %s",
$filter['field'], $filter['value']);
break;
case 'not':
$query->add_where("%s != %s",
$filter['field'], $filter['value']);
break;
}
break;
}
}

//
// Original
//
//function category_handler_filter_nid($op, $filter, $filterinfo, &$query) {
// $query->ensure_table('category_node');
// $query->add_where("category_node.nid = category_node.cid");
//}

function category_handler_filter_nid_a($op, $filter, $filterinfo, &$query) {
switch ($op) {
case 'handler':
$fieldbits = explode('.', $filter['field']);
$query->ensure_table('category_node');
switch ($filter['operator']) {
case '=':
$query->add_where("category_node.nid = category_node.cid");
break;
case 'not':
$query->add_where("category_node.nid != category_node.cid");
break;
}
break;
}
}

function category_handler_filter_nid_b($op, $filter, $filterinfo, &$query) {

$query->ensure_table('category_node');
$table_data = _views_get_tables();
$joininfo = $table_data['category_hierarchy']['join'];
// $joininfo['extra'] = array('parent' => $filter['value']);
$tblnum = $query->add_table('category_hierarchy', false, 1, $joininfo);
$table = $query->get_table_name('category_hierarchy', $tblnum);
$query->add_where("category_node.nid = category_node.cid and $table.parent = '%s'", $filter['value'], $filter['value']);

}

function category_handler_arg_parent($op, &$query, $argtype, $arg = '') {
switch($op) {
case 'summary':
$query->ensure_table('category');
$query->ensure_table('category_hierarchy');
$query->add_field('parent', 'category_hierarchy');
$fieldinfo['field'] = "category_hierarchy.parent";
return $fieldinfo;
case 'sort':
$query->add_orderby('category_hierarchy', 'parent', $argtype);
break;
case 'filter':
$query->ensure_table('category_hierarchy');
$query->add_where('category_hierarchy.parent = %d', $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);
$title = check_plain($voc->description);
return $title;
}
}

lias’s picture

lias commented

Thanks for your post, did you add this code to the end of category.inc? Do you think it's going to be rolled into the category.module as an official patch? Curious because I don't understand why views doesn't automatically register categories along with taxonomy.

somebodysysop’s picture

somebodysysop commented

They are all functions, so you can put them all at the end if you wish. I don't know if any of this will be rolled into category.module. As you see here, there has been no response from the maintainter. Just seemed like the logical thing to do.

lias’s picture

lias commented

This works great as is but I was wondering, how would I add category name to the list for views? So that I can create views based on category names rather than IDs? Unless there is something within these functions that does the same thing?

Thanks again SomebodySysop for this code!

slybud’s picture

slybud commented

Isabug, I have exactly the same needs as you : did you find the answer for filtering views with category names ?

somebodysysop’s picture

somebodysysop commented

The category name is in the title field of the category node. So, what you need is filter by node type "category-cat", and then use the normal node title filter for the caegory name(s) you wish to search on.

Lioz’s picture

Lioz commented

works perfectly for me!
thanks!

http://www.signalkuppe.com

fudge714’s picture

fudge714 commented

on my site there are about 50 categories, and various nodes in each. the point of the view i have set up is an advanced search which allows me to search by keyword in specific categories. so what i need to do is have the list of categories come up in a select box as a filter. I dont want to return a list of categories like you would do with node:type = category. Can this code help me? I've been able to get the extra options showing up, but none of them seem to be relevant...
thanks

azbok’s picture

azbok commented

I followed the directions and put the code in category.inc but I don't see any new items in the View selection boxes. Is there something extra one needs to do or is there some updates necessary to have it work with Drupal 5.1? Thanks.

somebodysysop’s picture

somebodysysop commented

That would happen to me all the time. You need to:

1. Delete your cache (execute sql command: "delete from cache")
2. Run cron.php (execute: http://yoursite/cron.php)
3. Logout and then log back in.

I don't know if all of this is *really* necessary, but when I follow these steps I am able to see the new items I've added to Views tables.

azbok’s picture

azbok commented

I did the "delete from cache" command and logged out and logged back in, went into the views and the category items showed up! I didn't need to run the cron.php to have it work. Thanks!!!

inforeto’s picture

inforeto commented

If there's further trouble, perhaps cleaning the views cache separately would help.
In the recent version there's a handy button for that.

JirkaRybka’s picture

JirkaRybka commented

I just posted my own step-by-step description on embedding Views onto Category-pages here: http://drupal.org/node/69861#comment-238417

Thanks for your code, which is actually the heart of the whole thing.

liquidcms’s picture

liquidcms commented

Looks quite promising but i dont think it works for parent args??

Unless i am missing the point - i want to filter nodes by those that have the arg as my parent. So, in other wrods:

- i create a page view with url = "test"
- i add arg: Category Hierarchy: Parent
- i can see a few nodes in category_hierarchy with parent = 45
- if i go to test/45 i think i should get all the nodes from the c_h table with parent = 45

but i get no results.

using Drupal 5.3, btw

Peter Lindstrom
LiquidCMS - Content Management Solution Experts

Peter Lindstrom
LiquidCMS - Content Solution Experts