Filtering on multiple values

[kev1n]

I think you just need to update the function like this:

Code: Select all

$getDistinctrendelesekColumnQuery = function($column) {
  return "SELECT DISTINCT `$column`, `$column` FROM `rendelesek` WHERE IFNULL(`$column`,'') <> '' ORDER BY `$column`";
};

[Diggger5]

OK,
I updated the getDistinctrendelesekColumnQuery as you suggested.
I tried updating the SQL based on your example:

Code: Select all

((sus_language = '#nu5bad6cb3746cc1b_filter#' OR LEFT('#nu5bad6cb3746cc1b_filter#',1) IN ('#','')) OR (IFNULL(sus_language,'') = '' AND '#nu5bad6cb3746cc1b_filter#' = '-1'))

But this way the filtering does not work neither NULL nor BLANK.
And the multi filter does not work either.

If I use LOCATE then the multi filter works:

Code: Select all

(rend_modellkod = '#66192bd91f4f4f8_filter#' OR LEFT('#66192bd91f4f4f8_filter#',1) IN ('#','') OR LOCATE(rend_modellkod, '#66192bd91f4f4f8_filter#') > 0 )

Why does the filtering not work for NULL and BLANK values?
And how should I combine it so that IFNULL and LOCATE work too?

Thank you!

[kev1n]

I made a small update in nuform.js to handle empty values in nuSearchableMultiPopup.


Below is the complete code I used for testing (note: sqlSTATUSZ is not included in my test).

BB PHP

Code: Select all

[b]$getDistinctrendelesekColumnQuery = function($column) {
    return "SELECT DISTINCT `$column`, `$column` FROM `rendelesek` WHERE IFNULL(`$column`,'') <> '' ORDER BY `$column`";
};

$sqlKER = function() use ($getDistinctrendelesekColumnQuery) {
    return $getDistinctrendelesekColumnQuery('rend_ertekesitokod');
};

$sqlOPTIONS = function() use ($getDistinctrendelesekColumnQuery) {
    return $getDistinctrendelesekColumnQuery('rend_options');
};

$KER = nuEncodeQueryRowResults($sqlKER(), [], ['-1', '']);
$OPTIONS = nuEncodeQueryRowResults($sqlOPTIONS(), [], ['-1', '']);

$filterJS = "

  function getData(data) {
    return JSON.parse(atob(data));
  }
  function getKER() {
    return getData('$KER');
  }
    function getOPTIONS() {
    return getData('$OPTIONS');
  }
";

nuAddJavaScript($filterJS);[/b]

Custom Code

(Note: my Ids are different):

Code: Select all

nuAddBrowseFilter('68a7263f0b3c449').nuSearchableMultiPopup({ items: getKER() });
nuAddBrowseFilter('68a7263f0b5c0f3').nuSearchableMultiPopup({ items: getOPTIONS() });

Browse SQL

For multiple-value filtering, the SQL needs a slight adjustment.
Also, my filter IDs differ from yours

Code: Select all

SELECT *
FROM rendelesek
WHERE
(
  (LEFT('#68a7263f0b3c449_filter#',1) = '#' OR TRIM('#68a7263f0b3c449_filter#') = '')
  OR (
       LEFT('#68a7263f0b3c449_filter#',1) <> '#' AND TRIM('#68a7263f0b3c449_filter#') <> ''
       AND (
            (FIND_IN_SET('-1', '#68a7263f0b3c449_filter#') > 0 AND IFNULL(rend_ertekesitokod,'') = '')
            OR FIND_IN_SET(rend_ertekesitokod, '#68a7263f0b3c449_filter#') > 0
       )
     )
)
AND
(
  (LEFT('#68a7263f0b5c0f3_filter#',1) = '#' OR TRIM('#68a7263f0b5c0f3_filter#') = '')
  OR (
       LEFT('#68a7263f0b5c0f3_filter#',1) <> '#' AND TRIM('#68a7263f0b5c0f3_filter#') <> ''
       AND (
            (FIND_IN_SET('-1', '#68a7263f0b5c0f3_filter#') > 0 AND IFNULL(rend_options,'') = '')
            OR FIND_IN_SET(rend_options, '#68a7263f0b5c0f3_filter#') > 0
       )
     )
);

As our SQL queries has grown more complex, I’ve introduced a new custom function called nu_filter_match().

This function acts as a placeholder inside your SQL. At runtime, it will automatically be expanded into the full filtering construct (as shown above), saving you from writing long and repetitive conditions by hand.

Usage becomes much simpler. For example:

Code: Select all

SELECT * FROM rendelesek
WHERE
  nu_filter_match('rend_ertekesitokod', '68a7263f0b3c449') AND 
  nu_filter_match('rend_options', '68a7263f0b5c0f3')

If you’d like to use a value other than -1 (which is our placeholder for empty values), simply pass it as the third parameter.

For example:
nu_filter_match('rend_options', '68a7263f0b5c0f3', 'EMPTY_VALUE');

And use that 'EMPTY_VALUE' in place of '-1' in the PHP BB code.

PS: The updated nuform.php is required.

Update: nu_equals_filter has been renamed to nu_filter_match.

[Diggger5]

You are amazing Kev1n!

The multiple filtering now works perfectly!

Thank you!

But there is a small error again.
The date picker window does not appear for date fields and I get the following error message:
Uncaught TypeError: Cannot convert undefined or null to object

Please check the browser developer console for details.

[kev1n]

This issue has just been fixed. See here: viewtopic.php?t=12592