I have a module that implements hook_views_query_alter(). In the module, I am attempting to add a WHERE clause to the existing query that drives the view.

Here's my code:

<?php
/**
* Implementation of hook_views_query_alter
* @param type $view
* @param type $query
*/
function Last_48_Hours_views_query_alter(&$view, &$query) {
if ($view->name == 'last_48_hours') {
$query->add_where(0,'webform_submitted_data_webform_submissions.data ', 'DATE_SUB( CURDATE( ) , INTERVAL 2 DAY )', '>=');
}
}

When I look at the query that is created via MySQL, here's what I see:

SELECT webform_submissions.sid AS sid, webform_submitted_data_webform_submissions.data AS webform_submitted_data_webform_submissions_data
FROM
{webform_submissions} webform_submissions
LEFT JOIN {webform_submitted_data} webform_submitted_data_webform_submissions ON webform_submissions.sid = webform_submitted_data_webform_submissions.sid AND (webform_submitted_data_webform_submissions.nid = '7' AND webform_submitted_data_webform_submissions.cid = '1')
LEFT JOIN {webform_submitted_data} webform_submitted_data_webform_submissions_1 ON webform_submissions.sid = webform_submitted_data_webform_submissions_1.sid AND (webform_submitted_data_webform_submissions_1.nid = '7' AND webform_submitted_data_webform_submissions_1.cid = '107')
WHERE (( (webform_submitted_data_webform_submissions.data >= 'DATE_SUB( CURDATE( ) , INTERVAL 2 DAY )') ))
ORDER BY webform_submitted_data_webform_submissions_data DESC

The problem is that the clause after the '>=' is enclosed in quotes and the query returns no results.

When I run this query in MySQL without the single quotes around 'Date_SUB...', the appropriate number of records are returned.

I'm new at this and suspect that the problem is something really easy that I'm not seeing. Does anyone have a suggestion or a piece of code that adds a where clause to a query?

Many thanks in advance for any assistance!

Comments

skwilliams’s picture

I figured it out -- the WHERE clause was looking for the value obtained by the PHP equivalent of 'DATE_SUB( CURDATE( ) , INTERVAL 2 DAY )'. I wrote the PHP code and added it to my module. All works fine now.