1

I need to add a WHERE clause to the query altered by a hook_views_query_alter() implementation. The WHERE clause needs to contain the following CASE statement.

$sql = "(CASE WHEN node_field_data.type IN ('course', 'events') AND ((node__field_event_start_date.field_event_start_date_value > today) OR (node__field_event_start_time.field_event_start_time_value > 0 AND node__field_event_start_time.field_event_start_time_value > current_timestamp)) THEN 1 WHEN node_field_data.type = 'insight' THEN 1 ELSE 0 END) = 1";

$query->addWhere(2, $sql, 1, '=');

I tried to use addWhere(), but it removes all the spaces from the condition and causes the following error.

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '."field_event_start_time_value0ANDnode__field_event_start_time"."field_event_sta' at line 6

How can I make this work?

2 Answers 2

2

Resolved it by adding additional AND OR condition groups. Sharing this in case anyone needs it in future.

    $start_date_configuration = [
      'table' => 'node__field_event_start_date',
      'field' => 'entity_id',
      'left_table' => 'node_field_data',
      'left_field' => 'nid',
      'operator' => '=',
    ];
    $join = Views::pluginManager('join')->createInstance('standard', $start_date_configuration);
    $query->addRelationship('node__field_event_start_date', $join, 'node_field_data');

    $start_time_configuration = [
      'table' => 'node__field_event_start_time',
      'field' => 'entity_id',
      'left_table' => 'node_field_data',
      'left_field' => 'nid',
      'operator' => '=',
    ];
    $join = Views::pluginManager('join')->createInstance('standard', $start_time_configuration);
    $query->addRelationship('node__field_event_start_time', $join, 'node_field_data');

    $current_timestamp = (new DrupalDateTime())->getTimestamp();

    $query->setWhereGroup('OR', 3);
    $query->addWhere(3, 'node_field_data.type', 'insight');
    $query->addWhere(3, (
      $query->getConnection()->condition('AND'))
      ->condition('node_field_data.type', ['course', 'events'], 'IN')
      ->condition(
        $query->getConnection()->condition('OR')
          ->condition('node__field_event_start_date.field_event_start_date_value', date('Y-m-d'), '>=')
          ->condition(
          $query->getConnection()->condition('AND')
            ->condition('node__field_event_start_date.field_event_start_date_value', NULL, 'IS NULL')
            ->condition('node__field_event_start_time.field_event_start_time_value', $current_timestamp, '>=')
        )
      )
    );

Thanks.

1

You can accomplish this with $query->addWhereExpression().
https://api.drupal.org/api/drupal/core%21modules%21views%21src%21Plugin%21views%21query%21Sql.php/9

For example: $query->addWhereExpression($group, 'Expression with CASE', [$arguments]);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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