Views 2 Table Aliases and Extra Criteria

Here are two tricks that you can use when you need to provide data for use in views.

If you need to provide data to views that rely on tables already present in a view you can use the table parameter to ensure views builds your joins the way you desire. This can be handy when using organic groups and you want data from two different nodes to be available in your result.

Chris Johnson, VP of Engineering
#Drupal | Posted

Here are two tricks that you can use when you need to provide data for use in views.

If you need to provide data to views that rely on tables already present in a view you can use the table parameter to ensure views builds your joins the way you desire. This can be handy when using organic groups and you want data from two different nodes to be available in your result.

In this case I want to have access to a custom content type field associated with a node that is the organic group node while also getting data from another node in the group which acts as the primary node of interest. Here is a rough form of the SQL I want generated:

[pgsql]SELECT
  *
FROM
  node n_main
  JOIN og_ancestry og ON n_main.nid = og.nid
  JOIN node n_group ON og.group_nid = n_group.nid
  JOIN content_type_example cto ON n.vid = cto.vid[/pgsql]

Here is how to describe the relationship to views.

  1. $data['n_group']['table']['join'] = array (
  2. 'node' => array (
  3. 'left_field' => 'group_nid',
  4. 'left_table' => 'og_ancestry',
  5. 'field' => 'nid',
  6. 'table' => 'node',
  7. ),
  8. );
  9.  
  10. $data['cto']['table']['join'] = array (
  11. 'node' => array (
  12. 'left_field' => 'vid',
  13. 'left_table' => 'n_group',
  14. 'field' => 'vid',
  15. 'table' => 'content_type_example',
  16. ),
  17. );
  18.  
  19. $data['cto']['field_desired_value'] = array (
  20. 'title' => t('Desired Field'),
  21. 'help' => t('The data you desire'),
  22. 'field' => array (
  23. 'handler' => 'views_handler_field',
  24. 'click sortable' => TRUE
  25. ),
  26. 'sort' => array (
  27. 'handler' => 'views_handler_sort_string',
  28. ),
  29. 'filter' => array (
  30. 'handler' => 'views_handler_filter_string',
  31. ),
  32. 'argument' => array (
  33. 'handler' => 'views_handler_argument_string',
  34. ),
  35. );

This code tells views that you have a data item (field_desired_value) that is available from the cto table. The cto table is defined as being able to join to the node table through the n_group table which can join to the node table via the og_ancestry table. The og_ancestry table definition is provided by the organic groups module.

The key thing here is that both the cto and n_group tables don’t actually exist which is why they define the ‘table’ element in their join. Using the alias name keeps this relationship separate from the existing node and og_ancestry relationships.

The second is for when you need to specify additional criteria in your join. Nodewords is a prime example of this as the relationship between a node and its nodewords data requires the nid, a type of 'node' and a name that indicates whether you want the description or keywords value. To accomplish this you can use the table alias and extra join criteria like so:

  1. $data['example_nodewords_description']['table']['join'] = array(
  2. 'node' => array(
  3. 'left_field' => 'nid',
  4. 'field' => 'id',
  5. 'table' => 'nodewords',
  6. 'extra' => array(
  7. array('field' => 'type', 'value' => 'node'),
  8. array('field' => 'name', 'value' => 'description'),
  9. )
  10. )
  11. );
  12.  
  13. $data['example_nodewords_description']['content'] = array(
  14. 'title' => t('Description'),
  15. 'help' => t('The nodewords description meta tag info'),
  16. 'field' => array (
  17. 'handler' => 'views_handler_field',
  18. 'click sortable' => TRUE,
  19. ),
  20. 'sort' => array (
  21. 'handler' => 'views_handler_sort_string',
  22. ),
  23. 'filter' => array (
  24. 'handler' => 'views_handler_filter_string',
  25. ),
  26. );

The alias in this case will let you define multiple pairs so that you can get the description, keyword and any other data you want. The extra parameter allows you to specify additional criteria for the join. In this case the join will look like:

[pgsql]SELECT
  *
FROM
  node n
  JOIN nodewords nw USING n.nid = nw.id and nw.type = 'node' and nw.name = 'description'[/pgsql]

Views allows for describing many complex relationships for making data available. Be sure to check out the documentation available through the advanced help module for more information and use the power of views previews to see the effects your changes have on the resulting SQL.

Chris Johnson

Chris Johnson

VP of Engineering