Hook_views_api – Use custom db table in Drupal Views – Tutorial

October 19th, 2012 admin Location: Raleigh, NC, Topic: Web Design | Digital Media

Ok so perhaps you have been in a position where you are developing a specific set of functions for a client in Drupal and you are leaning towards displaying some specific information from the database. It could be in a block or it could be in a page. This information would be displayed perfectly by Views (Drupal module) however you are creating a custom table in your Drupal database and of course Views doesn’t know about it.

You may think that since you are doing custom tables, why not simply bypass Views altogether and create a custom query as well. However, you understand that Views gives you great functionality out of the box like a pager, different display options (blocks, attachments, pages, and sometimes even CSV and more). So you decide that you need to learn how to modify the SQL query. Believe me, you could do that, but it would be a long night. So instead of attempting to change the query directly (which sometimes is inevitable) I am going to share with you how to “present” or “introduce” or “let Views know about” your new custom table so that you can use it for relationships within the GUI.

First let me say that you could do this by utilizing the Data Module. However, you have to enable the Schema Module as well and believe me when it comes to database modules, less is better.


[private]

The Solution:

Ok so here we go:

1. Go ahead and create your table in the database. You can of course head to PhpMyAdmin and do all the work from there or you can simply create the Schema and add it to a custom module. I am not going to go into the details on how you should do that part, but as an overview I would say that what you need is to create a new module, and call a hook when it is being installed. This hook will call your schema functions that define the new custom table. If you are doing this only for this one project, it makes little sense to define your schema that way. It is just easier to go directly in the DB and add your table.

2. Now that you have your table and your fields we are ready to introduce it to Views. For the sake of this tutorial let’s say that you called your new custom table: “my_custom_table” that way it would be easier to refer to it.

3. In order to let Views know about your table, we need to call first the hook_views_api hook. Within your module. So go to the file : yourmodule.module and add the following lines:

function yourmodule_views_api() {
	return array(
		'api' => 2
	);
}
/*As you can see I am defining api as 2 in other words, Views version 2 is the minimum that we'll need */

4. Great, now go ahead and create a new file called: your_module.views.inc in the same directory as your .module file. This file will be recognized and included automatically thanks to the previous step.

5. In that file we are going to call the following code:


// First implement hook_views_data with no parameters
// make sure to return the data array.
// remember that our table is called: my_custom_table

function your_module_views_data() {
	$data = array();
	
	// Views are organized in Groupings
	// each section respects these groups
	// for example in fields you can select group NODE, CONTENT, ETC.
	// here we define our own group. We are calling it PROFILES
	// notice also that the first key of the array is the name 
	// of our table
	// The second key is the actual word "table"
	// The third key is the actual word "group"
	$data['my_custom_table']['table']['group'] = t('PROFILES');
	
	// We now define our table as a base table.
	// what this means is that views could use our table
	// as the main table to query.
	// VIEWS always needs a base table that gets "joined",
	// to other tables. 
	// In this case, we don't need to define this as a base
	// because we are only joining our table to other base
	// tables. But we do it anyways for demo purposes.
	$data['my_custom_table']['table']['base'] = array(
		// Only one field can be defined as the "Primary Key of SQL" for
		// Views.
    'field' => 'order_id',  // My table is going to have an order_id that is a foreign key
    'title' => t('Order Profiles Table'),  // the title is the name that will appear in the GUI
    'help' => t("A small description"), // a small description below the title in the GUI 
    'weight' => -10, // Do we want this option to appear higher or lower in the GUI
  );

	// Here we now define the JOIN of our table. This is an explicit join.
	// What it means is that Views would now understand how to join our
	// table to other tables it knows about.
	$data['my_custom_table']['table']['join'] = array(
		// first we define the join to the node table - using the key 'node'
		'node' => array(
			// the left_field means the field in the table of the join
			// in this case the node table has a field nid.
			'left_field' => 'nid',
			// the field means the field in our new table. In this case,
			// our table my_custom_table has a field with name "nid"
			// this is of course a foreign key
			'field' => 'nid'
		),
		// second we also want to join our table to another table.
		// Let's say the UC_ORDERS table of Ubercart for example.
		'uc_orders' => array(
			// left_field - is the field in the uc_orders table 
			// called order_id
			'left_field' => 'order_id', // field in the uc_orders table
			'field' => 'order_id' // field is the field in our my_custom_table table
		)
	);
	
	// After we are done defining the "table" key of our table 
	// in the form of $data['my_custom_table']['table']['group'] = array();
	// and in the form of $data['my_custom_table']['table']['join'] = array();
	// We move into defining the actual fields of our table.
	
	// Describe each of the fields of our table
	// first we describe the nid field of our my_custom_table table
	$data['my_custom_table']['nid'] = array(
		// the title that will appear in the UI
		'title' => t('Node ID Title'),
		// The help appears as a comment under the title in the UI
		'help' => t('Example of Help'),
		// we then define the relationship,
		// what this means is how is this field related to the VIEW?
		// well, the answer is that first it is supposed to appear
		// in the "relationships" part of the VIEWS UI.
		'relationship' => array(
		      'base' => 'node',  // what is the base table? In this case node 
		      'field' => 'nid', // the field is the nid
		      'handler' => 'views_handler_relationship',  // lets use the handler views_handler_relationship
		      'label' => t('NODE ID PRO'), // Let's give it a Label so that it shows up like this in the GUI of Views
		    ),
		
	);
	// We then define another field for good measure
	// notice how we do exactly the same as before
	$data['my_custom_table']['order_id'] = array(
		'title' => t('The Order ID Title'), // a title for our title field
		'help' => t('A small description below the title'), 
		// once again define the relationship to VIEWS
		'relationship' => array( 
		      'base' => 'uc_orders', // base table is Ubercart Orders Table
		      'field' => 'order_id', // the field of relationship is the order_id
		      'handler' => 'views_handler_relationship', // we use the same handler
		      'label' => t('Orders ID PRO'), // We give it a nice label for the GUI
		    ),
	);
	
	return $data; // finally we return the array $data so that it connects with VIEWS.
}

/*End of Code*/

As always, make sure that you refresh your cache before refreshing your browser, or you may see inconsistent results.

That’s it. Now you should be able to see your new table available to VIEWS in the relationships section. Of course, you could have done fields or filters instead.

The great thing is that now you can relate your new table and its information to your other Drupal tables like node and user.

Until next time…
[/private]

About the Author

Alex Centeno MBA., is an international creative director and digital media strategist. Currently residing minutes away from the Research Triangle Park (RTP) - North Carolina, and with over 10 years of interactive marketing training and experience, Alex leads Merkados' international clients to maximizing their online business strategies. One of Alex's biggest strengths is his world-class capacity to effectively combine interactive marketing, digital media design and web development.

You may find further information about Alex Centeno MBA. at: and Twitter.

Sobre el Autor

Alex Centeno MBA., Es un director creativo y estratega de medios digitales reconocido internacionalmente. En la actualidad reside a tan solo minutos del Research Triangle Park (RTP) - Carolina del Norte, y con más de 10 años de entrenamiento y experiencia en marketing interactivo, Alex lidera a los clientes internacionales de Merkados™ para maximizar sus estrategias de negocios en línea. Una de las mayores fortalezas de Alex es su capacidad global de combinar de manera efectiva, mercadeo interactivo, diseño de medios digitales y desarrollo web.

Para más información sobre Alex Centeno MBA. puede visitar: y Twitter.