Add "OR IS NULL" as an option to views_handler_filter_numeric

That's an SQL issue. Since the "count" column is NULL (for items never flagged), the expression count < 5 evaluates to NULL, which is false. So the record isn't shown.

Things to investigate:
1. How are the CCK people handle this same issue? (I'm refering to CCK's Numeric field)
2. Perhaps a newer views_handler_filter_numeric is smart enough to deal with a NULL?

Like Mooffie said a while back, this is because of the way SQL works. NULL is not less than 1, it just doesn't exist. To fix this, the Views views_handler_filter_numeric class has to support creating a query that does a "WHERE field < 5 OR field IS NULL". Flag (and many other modules) will then gain the ability to list content that is either less than a certain value or 0.

Is there a way to override this then? I'd also like the ability to filter by 'is below value or null'.

Hi
Patch is attached that adds the following additional operators to the numeric filter:

• Equal to OR empty
• Less than OR empty
• Less than or equal to OR empty
• Greater than OR empty
• Greater than or equal to OR empty

Lee

Perhaps a checkbox to "Include NULL rows" would be preferable to adding 5 additional options in the select list? Thanks for the patch either way though.

yeah good point, I needed this in a hurry and only needed one of the five but after writing it thought I should help others in the same bind out so added the other four, it would have been better your way, if I get time I'll redo it but that's not likely to be till next month at least.

+1

Subscribing

quicksketch's #6 is the way to go here, I think.

Here's a patch as per #6, applied to views-6.x-2.12 (6.x-2.x-dev yields an error with relationships and I can't use it, as some refactoring seems to be in progress).

Includes a checkbox that checks for NULL values.

(My knowledge of Views coding is pretty limited, so hopefully this is correct)

Ugh, left a dpm() in there.

@mmilo: Here's a re-roll of your patch. View it to understand that yours wasn't generated correctly.

allownull needs to be allow_null.
There are coding standard violations (IF without braces for example)

+      '#description' => t('Equates the value of NULL as zero (0).'),

Not valid english. Try "Equates the value of NULL rows to zero." or "Treats NULL as zero."

@bojanz Can't say I agree it's invalid english - it would treat a NULL value the same as if it was a ZERO value. Changed it anyways.
Don't know what you're referring to when you say 'IF without braces' either? Could you point this out in the patch?

@mooffie Odd, I didn't look over the patch, weird it did that.

+        if ($this->value['value'] > 0)
+          $is_null = FALSE;

Also:

+  function check_for_null($field)
+  {
+    $is_null = $this->options['allow_null'] ? "OR $field IS NULL" : FALSE;
+    switch ($this->operator)
+    {

Opening brace comes in the same line as the function / switch.

Thanks, we are almost there!

Doh, I read "braces" as "parentheses".

subscribe

Committed to 6.x-3.x -- doesn't apply to 7.x, for hopefully obvious reasons.

This hilites that the add_where on this filter needs to change. In the New World ORder we aren't allowed to assume an AND when adding multiple where clauses. We need to fix that. Created an issue for that here: http://drupal.org/node/1101876

This issue is about 6.x-3.x, not about 7.x-3.x.

Well the patch got already committed to 6.x-3.x but wasn't ported to 7.x-3.x yet, so the issue status was correct

and exposed filter support?

For 7.x last patch + change op_simple function, and don't change op_between..

function op_simple($field) {
	$is_null = $this->check_for_null($field);
	if (!$is_null) {
                 $this->query->add_where($this->options['group'], $field, $this->value['value'], $this->operator);
	} else {
		$this->query->add_where($this->options['group'], db_or()->condition($field, $this->value, $this->operator)->condition($field, NULL, 'IS NULL'));
	}
}	

and change check_for_null function:

function check_for_null($field) {
    $is_null = $this->options['allow_null'] ? TRUE : FALSE;
    switch ($this->operator) {
      case '>':
        $is_null = FALSE;
      case '>=':
      case '=':
        if ($this->value['value'] > 0) {
          $is_null = FALSE;
        }
    }
    
    return $is_null;
  }

Related issue, but for contextual filters: #2411811: Add an "OR IS NULL" option to contextual filters