table view: taxonomy term as field

Actually, I started with something close to your suggestion

LEFT JOIN term_node on term_node.nid = node.nid LEFT JOIN term_data on term_data.tid = term_node.tid AND term_data.vid = MYVOCABULARYID

Unfortunately it works only if the node has one and only one term (or no terms). Let's say the node has one term in MYVOCABULARYID and another in ANOTHERVID. You'll get two rows back with that nid. One with term_data.tid set, and another with NULL. Add more terms to your node, and the query returns even more nodes. I believe both your suggestions have this problem.

With your other suggestion, you may be on to something:

You can actually build this construct in views, but you have to cheat a little. The 'extra' info expects it to be "key" => "value" which translates to "key = value". But you can cheat and do "key != value" => NULL.

This tells me that I could first do a seperate query for all the TIDs in my vocabulary. Then I could construct the 'extra' field like so:

'extra' => 'tid IN (2, 3, 4, 7, 9)' => NULL

Where the numbers are the TIDs returned from my query. The drawbacks here are that it would only work if the node has one (or zero) terms in the vocab, and more importantly if terms are added to the vocab my query will not update because the Views module uses caching.

However since I am using mysql 4.1 I could use a nested query:

'extra' => "tid IN (SELECT tid FROM {term_data} WHERE vid=$vid)" => NULL

That still works correctly only when there is one or zero terms from the vocab (otherwise it will return multiple rows per node). But it should allow me to sort by the column, which would be excellent. And I can still use my original scheme when I know there could be multiple terms.

It's late here. I'll wait till tomorrow to try it out.

The distinct idea sounds good. I have not tried it yet. I'm OK with you closing this issue. But just to share, here's the code I'm currently using. It takes advantage of the extra trick you mentioned earler.

It also handles to cases. If the caller knows the vocabulary allows single select, it uses an approach that allows me to sort the table by terms in that vocab. If multiple select it uses a group concat and does not allow the sorting.

function views_term_table($vid, $name, $multiple = false) {
  $tables = array();
  if (!$multiple) {
	$table_name = "term_node_".$vid;
	// add tables necessary to join the term_node and term_data tables.
	// term_data contains the human-readable term name.
	
	$tables[$table_name] = 
	   array(
			 'name' => 'term_node',
			 'join' => array('left' => array('table' => 'node',
											 'field' => 'nid'),
							 'right' => array('field' => 'nid'),
							 // trick
							 'extra' => array("tid IN (SELECT tid FROM {term_data} WHERE vid=$vid)" => null),
							 ),
			 );
	
	$term_data_table_name = 'term_data_'.$vid;
	$tables[$term_data_table_name] =
	   array('name' => 'term_data',
			 'join' => array('left' => array('table' => $table_name,
											 'field' => 'tid'),
							 'right' => array('field' => 'tid'),
							 ),
			 'fields' =>
			 array("name" => 
				   array('name' => $name,
						 'sortable' => true,
						 ),
				   ),
			 // can't define just one sort for both of these
			 'sorts' => array('weight' => 
							  array('name' => $name . " weight"),
							  'name' =>
							  array('name' => $name . " name"),
							  ),							  
			 );
	
  }

  if ($multiple) {
	$term_data_table_name = 'term_data_'.$vid;
	$tables[$term_data_table_name] =
	   array(
			 'fields' =>
			 array("term_$vid" => 
				   array('name' => $name,
						 'notafield' => true,
						 'query_handler' => 'views_query_handler_field_term',
						 'handler' => 'views_handler_field_term',
						 'sortable' => false,
						 'vid' => $vid,
						 ),
				   ),
			 );
  }
  
  return $tables;
}


function views_query_handler_field_term($field, $fieldinfo, &$query) {
  $vid = $fieldinfo['vid']; // vocabulary ID (not view id)

  // Use GROUP_CONCAT to find all terms.  Requires MySQL 4.1.  Nested SELECT also requires 4.1
  // Could use MAX instead of CONCAT to find just one
  $query->add_field("(SELECT GROUP_CONCAT(td.name ORDER BY td.weight, td.name) FROM {term_data} td LEFT JOIN {term_node} tn ON tn.tid = td.tid WHERE td.vid = $vid AND tn.nid = node.nid)", null, "term_$vid");

  /*  Here's an alternate query that does not use nested queries but does use group-concat.  MAX should work to find just one.
  $query->ensure_table("term_data_$vid");
  $query->add_groupby("node.nid");
  $query->add_field("GROUP_CONCAT(DISTINCT term_data_$vid.name ORDER BY term_data_$vid.name)", null, "term_$vid"); 
  */
}

// we define this handler to display the term.  Necessary because we used 'notafield' in defining the field.
function views_handler_field_term($fieldinfo, $fielddata, $value, $data) {
  $vid = $fieldinfo['vid']; // vocabulary ID (not view id)
  $field = "term_$vid";
  return $data->$field;
}