Originally posted on Yellow Pencil's blog. Follow @kimbeaudin on Twitter
Here is the deal
Drupal generally runs on a LAMP based setup (which I strongly recommend), but if clients are restricted to certain hardware and software, sometimes you have to make it work with what you've got. On a recent Drupal 7 project of ours, our clients wanted to use their in-house setup on Windows Servers with IIS and a MS SQL database back-end. With a bit of research, we saw that it was mostly possible thanks to the SQLSrv module. With the exception of a few hiccups* here and there, we got the site up and running.
Arg! Broken Date Filters
After a large portion of the site was complete, we needed to add a few more views. One view, with multiple displays, had data that would update every 5 minutes in a custom cron job. In the larger display of this view we showed all the data, ranging from two hours ago to 25 hours from the current timestamp. In the smaller block of the view we only wanted to show 5 rows where the rowís datetime field was now or later. In other words, we needed the small view to have the date filter for time greater than or equal to now.
The second view was an events listing and we wanted to have an exposed filter on the start and end dates.
Normally, this should be a simple addition to the view with date_views module enabled: go to the filters in the view, add the date filter, and done! However, with a MS SQL back-end you get this error:
SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '>'.
Oops! The query that date_views builds is not compatible with MS SQL.
I spent some time trying to see if anyone else had encountered this problem, and unsurprisingly I found an issue for it. There was no solution however, and just a temporary quick-fix. I made the changes as suggested, but it did not fix the problem for me. It was just as well, as I wanted something more robust for the long-term. If I had used the solution and in the future updated views or date_views, it had the potential to break. I would have to re-apply the quick fix and even then it may not work after the update. Alternatively, I could opt (if it had worked) to turn updates off, but that is not a preferred resolution either.
I then spent some time trying to figure out how I could alter a views query, and my Googling returned the hook_views_query_alter. I couldn't find a lot of documentation on how to actually use it - the best I found was this guy's blog post, which was a great starting point for me.
Alter the views query
I started my function by including the field in the query (that is selected), because for some reason, even though I had it added as a field on the view, it wasn't being generated as part of the query - this wasn't an issue on the second view so the join was not necessary. Basically, I add in the field array, the name of my field, what table it belongs to, and the alias I use for it (for those familiar with SQL the following may make a bit more sense to you).
// add the field
$view->query->fields['field_data_field_mytimefield'] = array(
'field' => 'field_mytimefield_value',
'table' => 'field_data_field_mytimefield',
'alias' => 'field_time_mytimefield'
);
Since all fields are their own table, I would have to create a join in the query which is where things get messy. The join in the query is actually it's own object, so you have to do something like:
// create the join object
$join = new views_join;
$join->table ='field_data_field_mytimefield';
$join->left_table = 'node';
$join->left_field = 'nid';
$join->field = 'entity_id';
$join->extra = array(
0 => 'field' => 'entity_type', 'value' => 'node'),
1 => array('field' => 'deleted', 'value' => 0, 'numeric' => true)
);
$join->type = "LEFT";
$join->extra_type = 'AND';
$join->adjusted = 'TRUE';
Basically, I am creating a left join on the datetime field table, using the nid from the node to the entity_id on the field table. Then you can actually use this join object in the table queue:
// add the join
$view->query->table_queue['field_data_field_mytimefield'] = array(
'table' => 'field_data_field_mytimefield',
'num' => 1,
'alias' => 'field_data_field_mytimefield',
'join' => $join,
'relationship' => 'node'
);
I then added the table to the list of tables (essentially the SQL from):
$view->query->tables['node']['field_data_field_mytimefield'] = array(
count' => 1,
alias' => 'field_data_field_mytimefield'
);
Finally, the part that I actually wanted, is adding the condition to the query (the where clause). I need to filter where mytimefield is greater than or equal to the current timestamp. Originally, I had tried to use the CURRENT_TIMESTAMP of GETDATE() function of MS SQL in the query, but it was not possible to use in the value field without it being interpreted as a string. Hence, I use the PHP gmdate function (our dates are stored in GMT) to get the current date. This view does not cache, so the query will run everytime the page/view is loaded and the timestamp will change.
// add the condition of greater than current timestamp
$date = gmdate('Y-m-d H:i:s');
$view->query->where[1]['conditions'][] = array(
'field' => 'field_mytimefield_value',
'value' => $date,
'operator' => '>='
);
Success!
The end result looks like:
function themename_views_query_alter(&$view, &$query) {
if( ($view->name == 'view-name' && $view->current_display == 'block_1') {
// add the field
$view->query->fields['field_data_field_mytimefield'] = array(
'field' => 'field_mytimefield_value',
'table' => 'field_data_field_mytimefield',
'alias' => 'field_time_mytimefield'
);
// create the join object
$join = new views_join;
$join->table ='field_data_field_mytimefield';
$join->left_table = 'node';
$join->left_field = 'nid';
$join->field = 'entity_id';
$join->extra = array(
0 => 'field' => 'entity_type', 'value' => 'node'),
1 => array('field' => 'deleted', 'value' => 0, 'numeric' => true)
);
$join->type = "LEFT";
$join->extra_type = 'AND';
$join->adjusted = 'TRUE';
// add the join
$view->query->table_queue['field_data_field_mytimefield'] = array(
'table' => 'field_data_field_mytimefield',
'num' => 1,
'alias' => 'field_data_field_mytimefield',
'join' => $join,
'relationship' => 'node'
);
// add the table
$view->query->tables['node']['field_data_field_mytimefield'] = array(
'count' => 1,
'alias' => 'field_data_field_mytimefield'
);
// add the condition of greater than current timestamp
$date = gmdate('Y-m-d H:i:s');
$view->query->where[1]['conditions'][] = array(
'field' => 'field_mytimefield_value',
'value' => $date,
'operator' => '>='
);
}
}
So now my view joins with mytimefield table, adds the field value, and filters on the condition that the mytimefield value is greater than or equal to the current timestamp.
Changing Exposed Filters
For the second view, with the exposed filters, it was a lot simpler. I can create the exposed filter ahead of time and it doesn't break until the user actually attempts to use the filter. This way I only had to change the query not build an entire new component to it.
Basically I removed the last condition (the auto-generated) date filter, and replaced it with two of my own.
$view->query->where[1]['conditions'][] = array(
'field' => 'field_data_field_date_time.field_date_time_value',
'value' => $view->exposed_data['field_date_time_value']['min'],
'operator' => '>='
);
and
$view->query->where[1]['conditions'][] = array(
'field' => 'field_data_field_date_time.field_date_time_value',
'value' => $view->exposed_data['field_date_time_value']['max'],
'operator' => '<='
);
In other words, set the condition where the date is greater than or equal to the min value of the exposed filter in the view, and less than or equal to the max value of the exposed filter. Now we have two views, that originally did not want to work at all with views_dates, doing exactly as we desired!
* A few modules, including Twitter, Elysia Cron, etc, require heavy modifications in order to work on this set-up. Running on a MS SQL back-end is generally not recommended, but hopefully by reporting issues these modules, including SQL Srv, will be able to resolve them on future releases.
