I am encountering an issue on using views filter. I want to filter on a value that is either NULL or NOT NULL. I believe the problem is that if the value is NULL when a user creates a submission the value is not stored all together, so the outcome is duplications multiplied by the number of submissions (10,000 submissions would easily become 100,000 views rows).

Example query:

SELECT webform_submissions.sid AS sid
FROM 
{webform_submissions} webform_submissions
LEFT JOIN {webform_submitted_data} webform_submitted_data_webform_submissions ON webform_submissions.sid = webform_submitted_data_webform_submissions.sid AND (webform_submitted_data_webform_submissions.nid = '5' AND webform_submitted_data_webform_submissions.cid = '34')
WHERE (( (webform_submitted_data_webform_submissions.data IS NOT NULL ) ))

With 7 submissions 1 of which does not contain the filtered value. I get a result of 46 rows. It seems to remove the 1 NULL value.

Am I setting up the view wrong?

$view = new view();
$view->name = 'surveys';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'webform_submissions';
$view->human_name = 'Surveys';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Surveys';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'value' => 'value',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
  'value' => array(
    'align' => '',
    'separator' => '',
    'empty_column' => 0,
  ),
);
/* Relationship: Webform submissions: Data */
$handler->display->display_options['relationships']['data']['id'] = 'data';
$handler->display->display_options['relationships']['data']['table'] = 'webform_submissions';
$handler->display->display_options['relationships']['data']['field'] = 'data';
$handler->display->display_options['relationships']['data']['webform_nid'] = '5';
$handler->display->display_options['relationships']['data']['webform_cid'] = '34';
/* Field: Webform submission data: Value */
$handler->display->display_options['fields']['value']['id'] = 'value';
$handler->display->display_options['fields']['value']['table'] = 'webform_submissions';
$handler->display->display_options['fields']['value']['field'] = 'value';
$handler->display->display_options['fields']['value']['webform_nid'] = '5';
$handler->display->display_options['fields']['value']['webform_cid'] = '34';
/* Field: Webform submissions: Sid */
$handler->display->display_options['fields']['sid']['id'] = 'sid';
$handler->display->display_options['fields']['sid']['table'] = 'webform_submissions';
$handler->display->display_options['fields']['sid']['field'] = 'sid';
/* Filter criterion: Webform submission data: Data field */
$handler->display->display_options['filters']['data']['id'] = 'data';
$handler->display->display_options['filters']['data']['table'] = 'webform_submitted_data';
$handler->display->display_options['filters']['data']['field'] = 'data';
$handler->display->display_options['filters']['data']['relationship'] = 'data';
$handler->display->display_options['filters']['data']['operator'] = 'not empty';
$handler->display->display_options['filters']['data']['value'] = '0';

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'surveys/json';
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

quicksketch’s picture

Category: bug » support

I get a result of 46 rows. It seems to remove the 1 NULL value.

Does the field your selecting on potentially have multiple values? If you select on a "select options" component that is multiple values (e.g. checkboxes), then a user can select multiple items, making multiple entries in the webform_submitted_data table.

From this report and the SQL query shown above, I don't think this is misbehavior on Webform's part, so I'm moving this to a support request.

dobe’s picture

The field is a textarea. 1 Value. Should test the issue for yourself. Post an export and a query.

dobe’s picture

Status: Active » Closed (fixed)

Think I fixed it, the value was storing more than 1 value in the webform_submission_table for some reason.

dobe’s picture

Status: Closed (fixed) » Active

Yeah its not fixed. The value is being stored. But it is being stored as a blank value. NOT a NULL value. So it cannot determine if it is NULL or not. I believe we can get around this by adding op functions for op_empty in webform_handler_filter_submission_data.inc like:

function op_empty($field) {
    if ($this->operator == 'empty') {
      $operator = "=";
    }
    else {
      $operator = "!=";
    }

    $this->query->add_where($this->options['group'], $field, '', $operator);
  }

Could you please test to make sure I am not going crazy though. I will submit a patch with the code above if you believe that it is a bug and this is a good workaround.

quicksketch’s picture

That seems like a reasonable addition to me. However if we're going to call it "empty", maybe it should be either an empty string *or* NULL. If you just wanted to check for an empty string, you can do that already with the "is equal to" and "is not equal to" comparison operators and just leave the value as an empty string.

Hm, actually thinking more about this, you can already manually add this OR operation by using Views' AND/OR options on the list of filters.

dobe’s picture

I totally agree you can accomplish the same thing with other operators (op_empty is just the default null operator for filters so this code just overrides the default class). However, if we are going to do that, we should at least unset the NULL / NOT NULL operators as it could cause needless issues for folks. Attached is a patch.

dobe’s picture

Try this one my tabs were messed.

quicksketch’s picture

Category: Support request » Task
Status: Active » Needs review

I'm not sure about this patch. Webform has had Views integration for a while now (probably over a year). I'm not sure that we can just remove these options at this point. Webform actually did try to prevent empty entries at one point, and I think it could still apply when dealing with conditional logic, where entire components or pages are skipped. In those situations, the value in the database really is NULL, since it doesn't exist at all. I think we still need both options, but you're right it's less than optimal requiring site-builders to distinguish between them.

Status: Needs review » Needs work

joshuautley’s picture

@ quicksketch

Your comments in #5 helped me out today. thank you. I was noticing the absence of NULL and I couldn't figure out how to do this until I read your *is not equal to* empty string comment.

- Cheers

DanChadwick’s picture

Status: Needs work » Closed (works as designed)

I don't think there's an actual issue here. I certainly don't want to remove views operators that have been out in the wild.

Also, this relates a bit to the question of perhaps not storing empty strings, but there is a note it that issue about updating views support to match.