"connector" filtering and NULL values in db

Suite and Optimus Suite 5
#1

Hello,

I have a problem when using “connector” filtering with db table having some NULL values in columns (Postgres).
I have a script:

grid_zap.attachHeader("#connector_text_filter,#connector_text_filter,#connector_text_filter,#connector_text_filter,#connector_text_filter,#connector_text_filter,,,,#connector_text_filter,#connector_text_filter");

Starting, the grid initializes with all records. When I’m starting to type something in the filter fiels, Connector generates something like

SELECT  *,'' as _l FROM dok_ksiegowy WHERE netto LIKE '%%' AND brutto LIKE '%%' AND status LIKE '%%' AND vat LIKE '%%' AND nr_dok LIKE '%AM%' AND typ_dok LIKE '%%' AND kontrahent LIKE '%%' AND data_dok LIKE '%%' AND miesiac LIKE '%%' AND opis LIKE '%%' AND nr_barcode LIKE '%%' AND nr_dok_sys LIKE '%%' AND rok LIKE '%%' ORDER BY nr_barcode DESC OFFSET 0 LIMIT 100

This is a problem, because record having NULL in any of the columns listed does not match the ‘%%’ pattern.
Is it possible - without editing the Connector code - make Grid not to send all empty strings from header filter to Connector, but the non-empty ones only?
Is it possible to make this filtering case- insensitive?

#2

I realize that this post was very old, but since I was searching for the same issue and didn’t find an answer.
After looking through the source code I found that you can include OR values for checking Null fields like so:

WHERE [b](Mfg_date LIKE '%1970-01-01%' OR Mfg_date IS NULL)[/b] AND active=1

$grid = new GridConnector($res);
$grid->event->attach("beforeFilter", 'myFilter');

function myFilter($filter_by)
{
    //SORT BY some_field ASC
    if (sizeof($filter_by->rules)) {
        while(list($key,$rules)=each($filter_by->rules)) {
            $name = $filter_by->rules[$key]["name"];
            $value = $filter_by->rules[$key]["value"];
            if ( (strlen($value) > 0) && (in_array($name, array('Mfg_date', 'field_with_null')))  ) {
                $filter_by->rules[$key] = "(`".$name."` LIKE  '%".mysql_real_escape_string($value)."%' OR `".$name."` IS NULL)";
            }
        }
     }
}

Please be aware that mysql_* has been depreciated and was included to show you have to manually escape the value.

You will need to tell the system which fields have null values to be included or adjust the code accordingly.

Basically by setting the rule to TEXT instead of an array of details, then it will be included “as is” (unescaped).