Create a new join in hook_views_query_alter

Posted on: October 8th, 2013 by admin 4 Comments

I tried to do this recently and found very few examples available online so am putting this up here for reference.

My original query looked like this:

1
2
3
4
SELECT node.title, node.nid
FROM node node
WHERE node.status = '1' AND node.type IN  ('resource') 
ORDER BY node_title ASC

However I wanted to filter nodes by which taxonomy terms were attached. This varied user by user depending on their permissions so had to be done on the fly in hook_views_query_alter.

To achieve this I used this code:

1
2
3
4
5
6
7
8
9
function hook_views_query_alter(&$view, &$query) {
	$join = new views_join();
	$join->table = 'field_data_field_category';
	$join->field = 'entity_id';
	$join->left_table = 'node';
	$join->left_field = 'nid';
	$join->type = 'inner';
	$query->add_relationship('field_data_field_category', $join, 'node');
}

Once that join was in place I could add my where (also in hook_views_query_alter):

1
2
3
4
5
$query->where[1]['conditions'][] = array(
	'field' => 'field_data_field_category.field_category_tid',
	'value' => array('111', '114', '125', '123'),
	'operator' => 'in'
);

After this was added by final query looked like this:

1
2
3
4
5
SELECT node.title, node.nid
FROM node node
INNER JOIN field_data_field_category field_data_field_category ON node.nid = field_data_field_category.entity_id
WHERE (( (node.status = '1') AND (node.type IN  ('resource')) AND (field_data_field_category.field_category_tid IN  ('111', '114', '125', '123')) ))
ORDER BY node_title ASC

Another good tip when playing around with hook_views_query_alter is to preview your query once your changes have been applied you can use the following code:

1
2
3
4
function permissions_views_pre_execute(&$view){
	// Debug: print the query with dpq
	dpq($view->build_info['query']);
}