So it happened last week that I created a View in Drupal 7 and I wanted to customize conditions (aka Filters) in a way that I thought cannot be done with the Views interface (I later discovered I was wrong and D7 Views UI could have been used to express the filters in the way I needed).
Basically I needed a complex combination of nested AND/OR filters. Googling brought me to a comment on Drupal StackExchange that described the outline of how to do it. The process consisted of using the Views hook_views_query_alter and changing the where clause of the $query object passed to this hook.
The background was like this:
I had a content type with 3 CCK (aka Content) fields:
Agency, State, Country
The Views url seemed like this:
http://mysite/clientlinq_feed/agency/state/country
The Views results needed to contain all nodes where:
- Agency was the one passed in first url argument or “All” (a special Agency value defined for the content field).
- State was the one passed in second url argument (optional)
- Country was the one passed in third url argument (optional).
So there were 3 Contextual filters defined for the View, mapping these 3 arguments to the url.
Assuming the url contains all 3 arguments, the $query->where[0][‘conditions’] clause (which is an array) contained 3 conditions ANDed together. To make this more clear, here’s the print_r output of the entire $query->where clause:
Array
(
[1] => Array
(
[conditions] => Array
(
[0] => Array
(
[field] => node.type
[value] => Array
(
[0] => feed_item
)
[operator] => in
)
[1] => Array
(
[field] => node.status
[value] => 1
[operator] => =
)
[2] => Array
(
[field] => DATE_FORMAT(FROM_UNIXTIME(field_data_field_cl_feed_from.field_cl_feed_from_value), '%Y-%m-%d') <= :field_data_field_cl_feed_from_field_cl_feed_from_value
[value] => Array
(
[:field_data_field_cl_feed_from_field_cl_feed_from_value] => 2012-05-26
)
[operator] => formula
)
)
[args] => Array
(
)
[type] => AND
)
[2] => Array
(
[conditions] => Array
(
[0] => Array
(
[field] => DATE_FORMAT(FROM_UNIXTIME(field_data_field_cl_feed_from.field_cl_feed_from_value2), '%Y-%m-%d') >= :field_data_field_cl_feed_from_field_cl_feed_from_value2
[value] => Array
(
[:field_data_field_cl_feed_from_field_cl_feed_from_value2] => 2012-05-26
)
[operator] => formula
)
[1] => Array
(
[field] => field_data_field_cl_feed_from.field_cl_feed_from_value2
[value] =>
[operator] => IS NULL
)
)
[args] => Array
(
)
[type] => OR
)
[0] => Array
(
[conditions] => Array
(
[0] => Array
(
[field] => field_data_field_cl_agency_identifier.field_cl_agency_identifier_value = :field_data_field_cl_agency_identifier_field_cl_agency_identifier_value
[value] => Array
(
[:field_data_field_cl_agency_identifier_field_cl_agency_identifier_value] => Caa
)
[operator] => formula
)
[1] => Array
(
[field] => field_data_field_cl_feed_state.field_cl_feed_state_value
[value] => Haryana
[operator] => =
)
[2] => Array
(
[field] => field_data_field_cl_feed_country.field_cl_feed_country_value = :field_data_field_cl_feed_country_field_cl_feed_country_value
[value] => Array
(
[:field_data_field_cl_feed_country_field_cl_feed_country_value] => India
)
[operator] => formula
)
)
[args] => Array
(
)
[type] => AND
)
)
If you analyze this output, you would notice:
- $query->where[0] contains conditions based off Contextual filters (also called arguments in Views and normaly supplied through url).
- $query->where[1] and all subsequent array elements in $query->where array contain conditions based off the “Filter criteria” section in Views.
Here’s the related code from my module where I customize the View’s conditions based on my requirements and above structure of $query->where clause:
{syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }function mymodule_views_query_alter(&$view, &$query) {
switch($view->name) {
case ‘clientlinq_feed’:
_mymodule_feed_views_query_alter($view, $query);
break;
}
}
function _mymodule_feed_views_query_alter(&$view, &$query) {
unset($query->where[0][‘conditions’][1]);
unset($query->where[0][‘conditions’][2]);
$query->where[0][‘conditions’][] = array(
‘field’ => ‘field_data_field_cl_agency_identifier.field_cl_agency_identifier_value’,
‘value’ => ‘All’,
‘operator’ => ‘=’,
);
$query->where[0][‘type’] = ‘OR’;
$custom_state = array ();
$custom_state[‘conditions’] = array ();
if(count($view->args) > 1) {
$custom_state[‘conditions’][] = array(
‘field’ => ‘field_data_field_cl_feed_state.field_cl_feed_state_value’,
‘value’ => $view->args[1],
‘operator’ => ‘=’,
);
$custom_state[‘conditions’][] = array(
‘field’ => ‘field_data_field_cl_feed_state.field_cl_feed_state_value’,
‘value’ => NULL,
‘operator’ => ‘IS NULL’,
);
}
$custom_state[‘args’] = array();
$custom_state[‘type’] = ‘OR’;
$query->where[] = $custom_state;
$custom_country = array ();
$custom_country[‘conditions’] = array ();
if(count($view->args) > 2) {
$custom_country[‘conditions’][] = array(
‘field’ => ‘field_data_field_cl_feed_country.field_cl_feed_country_value = :field_data_field_cl_feed_country_field_cl_feed_country_value’,
‘value’ => array(
‘:field_data_field_cl_feed_country_field_cl_feed_country_value’ => $view->args[2]
),
‘operator’ => ‘formula’,
);
$custom_country[‘conditions’][] = array(
‘field’ => ‘field_data_field_cl_feed_country.field_cl_feed_country_value’,
‘value’ => NULL,
‘operator’ => ‘IS NULL’,
);
}
$custom_country[‘args’] = array();
$custom_country[‘type’] = ‘OR’;
$query->where[] = $custom_country;
//print ‘<pre>’;
//print_r($query->where);
//print ‘</pre>’;
}{/syntaxhighlighter}
Here are the interesting points from this code:
- clientlinq_feed is the machine name of my view, obviously the filters needed to be customized for only this single view.
- The first thing the code does is to unset the conditions corresponding to State and Country contextual arguments:
unset($query->where[0][‘conditions’][1]);
unset($query->where[0][‘conditions’][2]); - Next an ‘OR’ filter is defined for Agency argument (notice how $query->where[0][‘type’] is set to ‘OR’).
- Then if number of Views arguments is greater than 1 (recall both State and Country were optional), an OR condition is defiined for State to either match View’s argument 1 ($view->args[1]) or State is allowed to be NULL (meaning no State was selected for this node).
- Similarly an ‘OR’ condition is defined for Country to match 2nd View’s argument or to be NULL.
- These conditions are themselves ANDed together by $query->where in effect giving us a Sql statement fragment on the following lines (for the contextual filters in the View):
WHERE (Agency = args[0] OR Agency = ‘All’)
AND (State = args[1] OR State IS NULL)
AND (Country = args[2] OR Country IS NULL) - This WHERE Clause further gets ANDed with the filters defined in Views ‘Filter Criteria’ section.
- The actual query generated by Views for me based of the Contextual arguments, the filters and after passing through customization for conditions from my code looked like this:
{syntaxhighlighter brush: sql;fontsize: 100; first-line: 1; }SELECT node.created AS node_created, node.nid AS nid
FROM
{node} node
LEFT JOIN {users} users_node ON node.uid = users_node.uid
LEFT JOIN {field_data_field_cl_feed_from} field_data_field_cl_feed_from ON node.nid = field_data_field_cl_feed_from.entity_id AND (field_data_field_cl_feed_from.entity_type = ‘node’ AND field_data_field_cl_feed_from.deleted = ‘0’)
LEFT JOIN {field_data_field_cl_agency_identifier} field_data_field_cl_agency_identifier ON node.nid = field_data_field_cl_agency_identifier.entity_id AND (field_data_field_cl_agency_identifier.entity_type = ‘node’ AND field_data_field_cl_agency_identifier.deleted = ‘0’)
LEFT JOIN {field_data_field_cl_feed_state} field_data_field_cl_feed_state ON node.nid = field_data_field_cl_feed_state.entity_id AND (field_data_field_cl_feed_state.entity_type = ‘node’ AND field_data_field_cl_feed_state.deleted = ‘0’)
LEFT JOIN {field_data_field_cl_feed_country} field_data_field_cl_feed_country ON node.nid = field_data_field_cl_feed_country.entity_id AND (field_data_field_cl_feed_country.entity_type = ‘node’ AND field_data_field_cl_feed_country.deleted = ‘0’)
WHERE (( (field_data_field_cl_agency_identifier.field_cl_agency_identifier_value = ‘Test’ ) OR (field_data_field_cl_agency_identifier.field_cl_agency_identifier_value = ‘All’) )AND(( (node.type IN (‘feed_item’)) AND (node.status = ‘1’) AND (DATE_FORMAT(FROM_UNIXTIME(field_data_field_cl_feed_from.field_cl_feed_from_value), ‘%Y-%m-%d’) <= ‘2012-05-26’) )AND( (DATE_FORMAT(FROM_UNIXTIME(field_data_field_cl_feed_from.field_cl_feed_from_value2), ‘%Y-%m-%d’) >= ‘2012-05-26’) OR (field_data_field_cl_feed_from.field_cl_feed_from_value2 IS NULL ) )AND( (field_data_field_cl_feed_state.field_cl_feed_state_value = ‘Haryana’) OR (field_data_field_cl_feed_state.field_cl_feed_state_value IS NULL ) )AND( (field_data_field_cl_feed_country.field_cl_feed_country_value = ‘India’) OR (field_data_field_cl_feed_country.field_cl_feed_country_value IS NULL ) )))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0{/syntaxhighlighter}
I know this looks a bit daunting on the onset but that is because this View is based on my specifications. If you try something similar based on your requirements and adapt the above code for you, I am sure things would come across much more naturally.
And it was after I had done all this, that I noticed the following in the Views UI:
clicking which brought up this UI (click to enlarge):
Had I noticed this earlier, I could have simplified other portions of my code (not shown in this blog post). But Views ability to define AND/OR conditions was not applicable to the portion of code discussed in this blog post because notice that Views allow AND/OR conditions for ‘Filters criteria’ section and I needed AND/OR for the Contextual arguments section, which meant I had to do it in code anyways.
Nevertheless, I learnt the valuable technique of customizing raw Views query in code. If we can customize the WHERE clause, this is a no-brainer that other Sql clauses for the View (SELECT, FROM etc.) can be customized too.
Recent Comments