how to create views with month and day in contextual filter

Question

Let suppose I have actor content type, it has a birthday field (it is date ), I want get all actor that has equal month and day of birth,I want to create a views with birthsearch/%/% path, and then I want pass month and day to it , as exapmle birthsearch/02/25 , it should return all actor that birthday is 25 feb,I think contextual filter is good idea to achieve this but, by default drupal Granularity of date field is

it is means I can restrict it to year and month, but I cant restrict it to day and month,

my question is how can I have views with month and day in contextual filter (passed to it by page Url)?

Answer 1

I achieve this with below solution,
create birthday field filter in views and set it to something fix =2000-02-02(not important later it will bypass),
then in my custom module I implement hook_views_query_alter and change condition to what I want "DATE_FORMAT(field_data_field_birth_date.field_birth_date_value, '%m%d') = :field_data_field_birth_date_field_birth_date_value"; and work correctly,and views page path is sbirthdaymd/% ( as example link will be something like sbirthdaymd/0524 )

/**
 * Implements hook_views_api().
 */
function mymodule_views_api() {
  return array(
    'api' => 3,
    'path' => drupal_get_path('module', 'mymodule'),
  );
}

/**
 * Implementation of hook_views_query_alter
 * @param $view
 * @param $query
 */
function mymodule_views_query_alter(&$view, &$query) {
    if($view->name == 'search_cast_by_birth_and_death') {
//        ym($view->query,'message');
        $url = parse_url(current_path());
        $temp = explode('/', $url['path']);
         if(!intval($temp[1]))
             $temp[1]="0406";
        if($view->current_display=='page_2'){
        $view->query->where[1]['conditions'][2]['field']="DATE_FORMAT(field_data_field_birth_date.field_birth_date_value, '%m%d') = :field_data_field_birth_date_field_birth_date_value";
        $view->query->where[1]['conditions'][2]['value'][':field_data_field_birth_date_field_birth_date_value']=$temp[1];
        }

     }
    }

Answer 2

I've just tested this with the post date field so assume it will work the same with a normal date field.

I added the field post date and then set the format to custom d and called the field post day.

I then added the field post date again and set the format to custom m and called the field post month.

These fields don't have to be visible so can be excluded from display.

I then added a contextual filter for "Content: Created day" and then "Content: Created month.

Now going to my_view/01/12 will show post that were created on 1st December.

Answer 3

Building on Zhilevan's answer I was able to add the where entirely programmatically. Doing it this way also meant I had to create the join programmatically.

/**
 * Implements hook_views_api().
 */
function misc_views_api() {
  return array(
    'api' => 3,
    'path' => drupal_get_path('module', 'misc'),
  );
}

/**
 * Implementation of hook_views_query_alter
 * @param $view
 * @param $query
 */
function misc_views_query_alter(&$view, &$query) {

    if($view->name == 'date_test') {

        $url = parse_url(current_path());
        $temp = explode('/', $url['path']);

        // Make sure month and date are zero padded.
        $month = str_pad($temp[1], 2, '0', STR_PAD_LEFT);
        $day = str_pad($temp[2], 2, '0', STR_PAD_LEFT);

        // Create join.
        $join = new views_join;
        $join->construct('field_data_field_date', 'node', 'nid', 'entity_id', array(), 'INNER');
        $query->add_relationship('field_data_field_date', $join, 'node');

        // Add where.
        $query->add_where_expression(0, "DATE_FORMAT(field_data_field_date.field_date_value, '%m%d') = :month_day", array(':month_day' => $month.$day));

    }
}

/**
 * Implements hook_views_pre_execute().
 */
function misc_views_pre_execute(&$view) {
    // This line outputs the generated SQL to the message area for debugging.
    dpq($view->build_info['query']);
}