| Project: | Views |
| Version: | 7.x-3.x-dev |
| Component: | exposed filters |
| Category: | bug report |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs work |
Issue Summary
If we have a table t1 with an optional relationship to table t2, views will generate SQL that looks something like:
SELECT [fields]
FROM t1
LEFT JOIN t2 on t1.nid = t2.nidThis has the intended effect of returning results from table t1 even if it doesn't have a match to table t2. Suppose, however, that table t2 has a field called "title" that we want to create an exposed filter for. When we submit the search with out anything in the form field (with the intention of not filtering that field at all) The SQL that views generates now looks like this:
SELECT [fields]
FROM t1
LEFT JOIN t2 on t1.nid = t2.nid
WHERE upper(t2.title) LIKE upper('%%')The way that LEFT JOINs work in SQL is that they return NULL for all the fields in the left table if the join fails. Since NULL isn't LIKE any string, even one as accepting as '%%', the WHERE condition is not true for any rows in t1 which don't have a match in t2, and our optional relationship has effectively become required.
Views is too large for me to wrap my head around well enough to make sure that this solution is the right way to go about fixing the problem, but what I've done to fix this for myself is add an if() clause to all of the op_whatever() functions in handlers/views_handler_filter_string.inc that checks to see if $this->value is empty before adding the where clause. Thus:
<?php
function op_contains($field, $upper) {
$this->query->add_where($this->options['group'], "$upper($field) LIKE $upper('%%%s%%')", $this->value);
}
?>becomes:
<?php
function op_contains($field, $upper) {
if(!empty($this->value)) {
$this->query->add_where($this->options['group'], "$upper($field) LIKE $upper('%%%s%%')", $this->value);
}
}
?>Hopefully I've provided a thorough enough description of the problem and a potential solution for this to be an easy fix; I couldn't figure out a way to get this to work without modifying Views code directly, and Views gets updated often enough that maintaining it across updates will get old really fast.
Comments
#1
#2
works for me with views 7.x-3.3
<?phpfunction op_contains($field) {
$this->query->add_where($this->options['group'], $field, '%' . db_like($this->value) . '%', 'LIKE');
}
?>
become
<?phpfunction op_contains($field) {
if(!empty($this->value))
$this->query->add_where($this->options['group'], $field, '%' . db_like($this->value) . '%', 'LIKE');
}
?>
thanks !
#3
Running into this same issue and this seems to be a reasonable approach?
#4
Here is a proper patch.
#5
#6
There is philosophical question - if user wants only rows with certain filter applied to certain column, should views return any results at all if that column does not exist for a given row?
If yes, should it treat empty string filter as an - Any - filter value? Maybe.
Should it treat 0 as - Any - ? No way! And with this patch it does. Look at http://php.net/manual/en/function.empty.php#refsect1-function.empty-retu...
empty() as is is not the way to go. If anything, maybe test for a length of a string.