Entity view (Content)

Describing Privatemsg tables to Views

By admin_1
Oct. 19, 2010

Describing Tables to Views: Using your own custom database tables in Drupal Views Difficulty: Intermediate Estimated Time: 20-40 minutes Goal: A custom module that describes the privatemsg module tables to Views. There are times when you (or a contributed module) may have created a custom database table and now you’d like to include those fields in one of your views. For example, the privatemsg module provides theming functions but at times you just need a little more flexibility. Perhaps you’d like to display a view displaying your inbox with information from a user’s profile. The easiest, most flexible, and way would be to describe the privatemsg module tables to views. So, that’s what I show you how to do. From there, you can create relationships to users and nodes. To get started, create a new module, call it privatemsg_views. privatemsg_views.info

// custom module privatemsg_view.info file
name = Privatemsg Views
description = Privatemsg Views Module
package = Views
core = 6.x

// add dependencies. For this module all we really need is views, privatemsg
dependencies[] = views
dependencies[] = privatemsg

privatemsg_views.module

// custom module privatemsg_view.module file
Now that you have your custom module setup, you need add the following:
/**
 * Implementation of hook_views_data()
 */
function privatemsg_views_views_data() {
	// include helper functions, containing the custom tables
	module_load_include('inc', 'privatemsg_views', '/includes/privatemsg_views.tables');

	// initialize the array
	$data = array();

	// the keys in this array are the names of the tables in your database. The private
	// message module adds pm_index and pm_message which we will need in order to
	// build a custom inbox view.
	$data['pm_index'] = _privatemsg_views_views_table_pm_index();
	$data['pm_message'] = _privatemsg_views_views_table_pm_message();

	return $data;
}
For readability, I have separated the actual tables into a different file named privatemsg_views.tables.inc. This file is in a sub-folder /includes. privatemsg_views.tables.inc
 array(
			'group' => t('Private MSG'),
			'join' => array(
				'users' => array(
				  'left_field' => 'uid',
				  'field' => 'uid',
				  'real name' => 'shared_users',
				),
			),
		),

		// Columns
	  'uid' => array(
	  	'title' => t('User ID'),
	  	'help' => t('Badge: User ID'),
	  	'field' => array(
	  		'handler' => 'views_handler_field_numeric',
	  		'click sortable' => TRUE,
	  	),
		  'sort' => array(
		    'handler' => 'views_handler_sort',
			),
	  ),
	  'mid' => array(
	  	'title' => t('Message ID'),
	  	'help' => t('PM: Message ID'),
	  	'field' => array(
	  		'handler' => 'views_handler_field_numeric',
	  		'click sortable' => TRUE,
	  	),
		  'filter' => array(
		    'handler' => 'views_handler_filter_numeric',
			),
		  'sort' => array(
		    'handler' => 'views_handler_sort',
			),
	  ),
	  'thread_id' => array(
	  	'title' => t('Thread ID'),
	  	'help' => t('PM: Thread ID'),
	  	'field' => array(
	  		'handler' => 'views_handler_field_numeric',
	  		'click sortable' => TRUE,
	  	),
		  'filter' => array(
		    'handler' => 'views_handler_filter_numeric',
			),
		  'sort' => array(
		    'handler' => 'views_handler_sort',
			),
	  ),
	  'is_new' => array(
	  	'title' => t('Flag: Is New'),
	  	'help' => t('PM Flag: Is New'),
	  	'field' => array(
	  		'handler' => 'views_handler_field_numeric',
	  		'click sortable' => TRUE,
	  	),
		  'filter' => array(
		    'handler' => 'views_handler_filter_numeric',
			),
		  'sort' => array(
		    'handler' => 'views_handler_sort',
			),
	  ),
	  'deleted' => array(
	  	'title' => t('Flag: Deleted'),
	  	'help' => t('PM Flag: Deleted'),
	  	'field' => array(
	  		'handler' => 'views_handler_field_numeric',
	  		'click sortable' => TRUE,
	  	),
		  'filter' => array(
		    'handler' => 'views_handler_filter_numeric',
			),
		  'sort' => array(
		    'handler' => 'views_handler_sort',
			),
	  ),

	);
	
	return $table;
}


function _privatemsg_views_views_table_pm_message() {
 	$table = array(
		
		// Table Descriptions
		'table' => array(
			'group' => t('Private MSG'),
			'join' => array(
				'users' => array(
				  'left_table' => 'pm_index',
				  'left_field' => 'mid',
				  'field' => 'mid',
				  'real name' => 'shared_users',
				),
			),
		),

		// Columns
	  'mid' => array(
	  	'title' => t('Message ID'),
	  	'help' => t('PM: Message ID'),
	  	'field' => array(
	  		'handler' => 'views_handler_field_numeric',
	  		'click sortable' => TRUE,
	  	),
		  'filter' => array(
		    'handler' => 'views_handler_filter_numeric',
			),
		  'sort' => array(
		    'handler' => 'views_handler_sort',
			),
	  ),
	  'author' => array(
	  	'title' => t('Author ID'),
	  	'help' => t('PM: Author ID'),
	  	'field' => array(
	  		'handler' => 'views_handler_field_numeric',
	  		'click sortable' => TRUE,
	  	),
		  'sort' => array(
		    'handler' => 'views_handler_sort',
			),
	    'relationship' => array(
	    	'label' => 'author',
	      'base' => 'users',
	      'base field' => 'uid',
	      'real name' => 'shared_users',
			),
	  ),
	  'subject' => array(
	  	'title' => t('Subject'),
	  	'help' => t('PM: Subject'),
	  	'field' => array(
	  		'handler' => 'views_handler_field',
	  	),
	  ),
	  'body' => array(
	  	'title' => t('Body'),
	  	'help' => t('PM: Body'),
	  	'field' => array(
	  		'handler' => 'views_handler_field',
	  	),
	  ),
	  'timestamp' => array(
	  	'title' => t('Timestamp'),
	  	'help' => t('PM: Timestamp'),
	  	'field' => array(
	  		'handler' => 'views_handler_field_date',
	  		'click sortable' => TRUE,
	  	),
		  'filter' => array(
		    'handler' => 'views_handler_filter_date',
			),
		  'sort' => array(
		    'handler' => 'views_handler_sort_date',
			),
	  ),

	);
	
	return $table;
}

That’s all the code you need to get started building views from privatemsg tables. Go ahead and enable your new module, clear your cache and create a new view. You’ll see a new group under Fields called Private MSG with all of the fields we’ve just described above. Voila! Of course, now that you have your new inbox view, you can theme it, put it in a block, and place it anywhere on your site. Be sure to check out the views documentation below for adding relationships, arguments, etc. If you’re looking for documentation, head on over to http://views-help.doc.logrus.com/help/views/api-tables where you’ll learn how to describe your tables to Views. Suggestions? Tips? Comments? Let us know.
Post Tags: