At some point, you might want to pass date ranges as arguments to a view. Unfortunately, the built-in functionality of views does not allow you to use a date argument as a range. If you try to specify the date arguments by editing your view, the views module will automatically insert SQL to match the dates exactly, which will not work in this scenario. Assuming that you already have a view and a content type that has two fields for start and date ranges, you can add a hook_db_rewrite_sql() function to add your own SQL to the view's query.
<?php
// define the db_rewrite_sql hook:
function MYMODULE_db_rewrite_sql($query, $primary_table = 'n', $primary_field = 'nid', $args = array()) {
// search for the view
if (is_object($args['view']) && $args['view']->name=='MYVIEWNAME') {
// if there are no view arguments, don't bother continuing
if (!is_array($args['view']->args)) return;
// get the start and end date ranges from the view arguments
// NOTE: if you don't know which arguments are which,
// you can use: print_r($args['view']->args)
$startDate = $args['view']->args[0];
$endDate = $args['view']->args[1];
// validate dates. exit function if issue
if (strlen($startDate) && !checkdate(date('m', strtotime($startDate)), date('d', strtotime($startDate)), date('Y', strtotime($startDate)))) return;
if (strlen($endDate) && !checkdate(date('m', strtotime($endDate)), date('d', strtotime($endDate)), date('Y', strtotime($endDate)))) return;
// create var for where clause
$where = "";
// define table alias
$tableAlias = 'MYALIASNAME';
if ($startDate) {
$where = " {$tableAlias}.field_start_date_value >= '$startDate' ";
}
if ($endDate) {
if (strlen($where)) $where .= " and ";
$where .= " {$tableAlias}.field_end_date_value <= '$endDate' ";
}
if (strlen($where)) {
return array('join' => "inner join {content_type_MYCONTENTYPE} $tableAlias on node.vid = $tableAlias.vid and $where");
}
}
}
?>Now, you can pass the date ranges in as arguments using the views_embed_view function:
<?php
$viewName = 'MYVIEWNAME';
$arg = array('2008-01-01','2008-12-31');
$viewsHtml = views_embed_view($viewName, 'default', $arg);
?>
Where does it go?
I have a custom module set up and it works when iuse other code in it. However, when I swap it out with this code it no longer shows anything.
Am I correct to assume I can od this:
1. create a custom mod that writes content to a block.
2. use your sql function to modify the view that populates tha block.
3. Add the php call to embed the view on the page
That is what I am doing, but I am not getting any results back. No errors either.
Thanks.
You don't need db_rewrite_sql...
I followed a tip here: http://stackoverflow.com/questions/1301138/drupal-6-views-2-setting-date...
Seems that you actually don't need to do a rewrite of the sql with complex code. You can do it like this:
<?php$view = views_get_view('your_view');
$args[] = '2008-11-12--2010-11-12'; // from - to arguments
$view->set_arguments($args);
$view->build('default');
$view->pre_execute('default');
$view->execute('default');
$output = $view->render();
?>
Since views_embed_view is a wrapper around views_get_view(): this might work too...
<?php$viewName = 'MYVIEWNAME';
$arg = array('2008-01-01--2008-12-31');
$viewsHtml = views_embed_view($viewName, 'default', $arg);
?>
Works for me.
yup!
thanks, I really need to clean up my older articles :)
this is easier
maybe a newer version of Views.. but this works: http://drupal.org/node/673906#comment-4012218
and is much much easier.. :)
where to put the code
This is exactly what I have been looking for. Where do I put that code? In what file?
module
You'll have to create your own Drupal module: http://drupal.org/node/231276
"args" argument of function hook_db_rewrite_sql
when is the args array filled with values?
I checked this in an implementation of hook_db_rewrite_sql (D5) using print_r and it looks that "args" is always empty.
Drupal 6
I wrote this code for Drupal 6 :(