I have a Drupal 7 site that is having a some problems with a calendar page managed by views. The sessions that make up an event our used to populate the events on a calendar. The sessions grouping into a single event gets mixed up when an event spans across multiple months.
When looking over the query Views generates, I see that date ranges is managed with a WHERE clause using the start+end dates of sessions. This is causing the calendar to show a new event start for the first instance of a session within an event. In other words, the event gets incorrect listings as starting in multiple months.
I know I want to change the start+end date WHERE clauses to a single HAVING MIN(start) date clause, but I don't know how to do this within Views. Does anyone have a tip or two on how to get this worked out?
Drupal 7, Views 3.13, Calendars 3.5
Comments
Hey Rob, I haven't used
Hey Rob, I haven't used Calendar module since D6, so I can't really remember how those views were done... I'll see if I have a drupal test install, if i do I'll through calendar module and check it for you.
Answer
Well I got myself in the right area fast but then found every way not to get the full answer or at least that's how it felt.
<?phpfunction mymodule_views_query_alter(&$view, &$query) {
if ($view->name == 'myview_calendar' && $view->current_display == 'page_calendar') {
$where =& $query->where;
foreach($where AS $k => $clause) {
if (!empty($clause['conditions'][0]['value'][':field_startdate_date_argument'])) {
$query->add_having_expression(
0,
"DATE_FORMAT(MIN(node.field_startdate), '%Y-%m') = '{$view->args[2]}' ",
array()
);
unset($where[$k]);
}
}
}
}
?>
First off you are best off to work with hook_views_query_alter(). Started off with a checking the view name and current_display to target the correct view and display and created a foreach to run through all of the where clauses. Next I used the where clause created by contextual filter of the requested (or default) date compared to my starting date field to add the HAVING clause. I had a complex expression with the use of DATE_FORMAT and MIN do I had to use add_having_expression vs add_having for simple cases. And finally I used the key variable $k from the foreach to remove the where clause as it was not needed.
One thing that also gave me a headache was passing in the view argument for the date. It was simple to get from the $view object, but I did have to place it directly in the field variable of the add_having_expression function to avoid the variable being miscast and causing an error.
Hope this can help someone else some time.