1

I successfully created a view gathering, let's say, events, sorted by date. I also created exposed filters so one could filter the view.

I'm attempting to create another exposed filter that would filter the data on their field_date, but only by month. I quickly understood that this feature is not provided out of the box, so I managed to implement hook_views_query_alter() in the .views_execution.inc file.

This is what it contains.

function djm_views_query_alter(ViewExecutable $view, QueryPluginBase $query){
    if($view->id() == 'calendar'){
        $date_format = $query->getDateFormat("node__field_precise_date.field_precise_date_value", "m");
        $query->addWhere(5, $date_format, 12, "=");
    }
}

$date_format should return DATE_FORMAT('node__field_precise_date.field_precise_date_value', '%m'). The thing is that (I inspected the query my view outputs) the view totally strips my parentheses, percentage signs and, I guess, any special character, so it becomes DATE_FORMATnode__field_precise_date.field_precise_date_valuem.

Has anyone ever experience something like this? I can't figure what to do to "fix" this. Am I using the right hook?

1

I managed to get my code working, if anyone is stucked at the same point, you have to use addWhereExpression instead of addWhere. Here's what it looks like overall.

function HOOK_views_query_alter(ViewExecutable $view, QueryPluginBase $query){
    if($view->id() == 'calendar'){
        $definition = array(
            'table' => 'node__field_precise_date',
            'field' => 'entity_id',
            'left_table' => 'node_field_data',
            'left_field' => 'nid',
            'extra' => [ // These are extra conditions views adds by default, I just copied them so it looks like any filter.
                0 =>[
                    'field' => 'deleted',
                    'value' => 0
                ],
                1 => [
                    'left_field' => 'langcode',
                'field' => 'langcode'
                ]
            ]
        );
        $join = Views::pluginManager('join')->createInstance('standard', $definition);

        $query->addRelationship('node__field_precise_date', $join, 'node_field_data');
        // ouptuts "LEFT JOIN {node__field_precise_date} node__field_precise_date ON node_field_data.nid = node__field_precise_date.entity_id AND (node__field_precise_date.deleted = '0' AND node__field_precise_date.langcode = node_field_data.langcode)"

        $date_format = $query->getDateFormat("node__field_precise_date.field_precise_date_value", "m")." = :value";
        $query->addWhereExpression(2, $date_format, [":value" => 12]); // Where 12 is the month I need.
    }
}

I'm still opened to better ideas so feel free to share them !

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.