DataTable: Multiple filters for same field (AND) #8559

michael-milette · 2019-02-08T18:14:26Z

Does anyone know if there is a way to have multiple filters apply to the same field in a datatable?

For example, I have a province field which contains a commas delimited list of one or more provinces. I want to find all of the entries that include BOTH Ontario AND Quebec.

I tried the new Checkbox filter and, while it works great and we will be using it, it's filter returns results that include Ontario OR Quebec.

I also tried creating two province filters hoping that the second filter fields would filter the results produced by the first field. Unfortunately it only seems to take the second province filter into consideration, ignoring the first province filter.

Is there any way to have more than one filter apply to the same field?

Best regards,

Michael

duboisp · 2019-02-08T18:33:54Z

The datatable third party plugin support regular expression, so yes it feasible.

In order to do, when applying programmatically the filter on a column, the person would need to provide additional parameter where it activate the regular expression and deactivate the smart search.

See the docs here:
https://datatables.net/reference/api/column().search()

RobJohnston · 2019-02-08T19:48:05Z

Does this relate to my issue #8500 where I found the search lacking the regex, smart, and caseInsen?

ghost · 2019-02-08T19:53:05Z

I enable regex for the filters when introducing checkboxes. It be really simple to update it so it not looking for exact string.

Also the regex I enable does not affect the search box. FYI.

ghost · 2019-02-08T20:06:03Z

Also @michael-milette what do you mean by?

Is there any way to have more than one filter apply to the same field?

With the current design it searches using this code:

$datatable.column( parseInt( $elm.attr( "data-column" ), 10 ) ).search( "(" + $value + ")", true ).draw();

for checkboxes the value be Ontario|Quebec, so it searches the field looking for Ontario OR Quebec.

Are you looking for it to search with Ontario AND Quebec instead of OR?

Maybe the checkbox filer could be updated in away that allows for AND or OR...should this be predetermined or up-to the user searching?

michael-milette · 2019-02-08T21:27:43Z

Hi @thekodester ,

Ideally we would like to give the user the option of selecting AND or OR. The checkboxes already provide the OR which is great. I am definitely using this feature. Just looking for a way to provide the AND now. It can be AND or OR like the advanced filter option in excel offers. Looks like this:

It would preferably be on a per-fieldset basis and not a global setting. For example, I have another datatable where I have provinces, which I would like to OR, and a population type which I would like to AND or OR so that the user can select multiple population types in a given region of provinces.

It would be most appreciated if you have any thoughts on how to make this happen.

Best regards,

Michael

michael-milette · 2019-02-14T21:08:20Z

Hi @thekodester ,

Just wanted to confirm that you saw my reply last week.

Best regards,

Michael

ghost · 2019-02-14T21:10:10Z

It wouldn't be overly complicated, but unsure if I be able to work on it right now. I do this in my free time when I have it.

michael-milette · 2019-02-15T17:22:27Z

I understand @thekodester and thank you again - all of your efforts are so very much appreciated.

ghost · 2019-02-17T15:31:10Z

These are the results:

Whole Word (and):

Whole Word (or):

Contains Word (and):

Contains Word (or):

ghost · 2019-02-17T15:32:51Z

This is the basic regex idea:

// ADVANCE OPTIONS:

// CONTAINS OPTIONS:
//(Ontario) - MUST CONTAIN ONTARIO
//(Ontario|Quebec) - MUST CONTAIN ONTARIO OR QUEBEC
//(?=.*\bOntario\b)(?=.*\bQuebec\b).* - MUST CONTAIN ONTARIO AND QUEBEC

// WHOLE WORD OPTIONS:
//^(Ontario)$ - ONLY ONTARIO
//^(Ontario|Quebec)$ - ONLY ONTARIO OR QUEBEC
//^(Quebec|Ontario|[,\s])(Quebec|Ontario|[,\s])+$ - ONLY ONTARIO AND QUEBEC

ghost · 2019-02-17T15:33:56Z

Filters:

<details>
							<summary><h6>Advanced Options</h6></summary>
							<div class="radio">
								<label for="advanced_options1_1"><input type="radio" name="advanced_options1" id="advanced_options1_1" data-aopts='{"type": "any", "column": "5"}' checked />Any</label>
							</div>
							<div class="radio">
								<label for="advanced_options1_2"><input type="radio" name="advanced_options1" id="advanced_options1_2" data-aopts='{"type": "both", "column": "5"}' />Both</label>
							</div>
							<div class="radio">
								<label for="advanced_options1_3"><input type="radio" name="advanced_options1" id="advanced_options1_3" data-aopts='{"type": "either", "column": "5"}' />Either</label>
							</div>
							<div class="radio">
								<label for="advanced_options1_4"><input type="radio" name="advanced_options1" id="advanced_options1_4" data-aopts='{"type": "and", "column": "5"}' />And</label>
							</div>
						</details>

ghost · 2019-02-17T15:35:02Z

Script:

<script>
    $( document ).on( "submit", ".wb-tables-filter", function( event ) {

	event.preventDefault();

	var $form = $( this ),
		$datatable = $( "#" + $form.data( "bind-to" ) ).dataTable( { "retrieve": true } ).api();

	// Lets reset the search;
	$datatable.search( "" ).columns().search( "" );

	// Lets loop throug all options
	var $lastColumn = -1, $cbVal = "";
	$form.find( "[name]" ).each( function() {
		var $elm = $( this ),
			$value = "",
			$regex = "",
			$isAopts = $elm.data( "aopts" ),
			$column = parseInt( $elm.attr( "data-column" ), 10 );

		// Ignore the advanced options fields
		if ( $isAopts ) {
			return;
		}

		// Filters based on input type
		if ( $elm.is( "select" ) ) {
			$value = $elm.find( "option:selected" ).val();
		} else if ( $elm.is( ":checkbox" ) ) {

			// Verifies if using same checkbox list
			if ( $column !== $lastColumn || $lastColumn === -1 ) {
				$cbVal = "";
			}
			$lastColumn = $column;

			// Verifies if checkbox is checked before setting value
			if ( $elm.is( ":checked" ) ) {
				var $aoptsSelector = "[data-aopts*='\"column\": \"" + $column + "\"']:checked",
					$aopts = $( $aoptsSelector ),
					$aoType = ( $aopts && $aopts.data( "aopts" ) ) ? $aopts.data( "aopts" ).type.toLowerCase() : "";

				if ( $aoType === "both" ) {
					$cbVal += "(?=.*\\b" + $elm.val() + "\\b)";
				} else {
					$cbVal += ( $cbVal.length > 0 ) ? "|" : "";
					$cbVal += $elm.val();
				}

				$value = $cbVal;
				$value = $value.replace( /\s/g, "\\s*" );

				// Adjust regex based on advanced options
				switch ( $aoType ) {
				case "both":
					$regex = "(" + $value + ").*";
					break;
				case "either":
					$regex = "^(" + $value + ")$";
					break;
				case "and":
					$regex = ( $value.indexOf( "|" ) > -1 ) ? "^(" + $value + "|[,\\s])(" + $value + "|[,\\s])+$" : "(" + $value + ")";
					break;
				case "any":
				default:
					$regex = "(" + $value + ")";
					break;
				}
			}
		} else {
			$value = $elm.val();
		}

		if ( $value ) {

			// Verifies if regex was preset, if not preset use 'contains value' as default
			if ( !$regex ) {
				$value = $value.replace( /\s/g, "\\s*" );
				$regex = "(" + $value + ")";
			}

			$datatable.column( $column ).search( $regex, true ).draw();
		}
	} );

	return false;
} );
</script>

ghost · 2019-02-17T15:39:02Z

I'm not sure if this should be merged in the filters demo or not. Any feedback on the code or ideas are
greatly appreciated . @michael-milette @duboisp

Was just able to publish a live demo see: kodz.ca/projects/wet-boew/demos/tables/checkbox-filter

I haven't done much testing so there probably some edge cases.

michael-milette · 2019-02-18T06:18:34Z

You are absolutely the best @thekodester , Thank you so much Kody! I can't wait to try it out when I get back in the office on Tuesday. I will be sure to let you know how it work out.

Best regards,

Michael

michael-milette · 2019-02-26T20:24:33Z

Hi @thekodester ,

Sorry for the delay in getting back to you. Life has a way of getting in the way.

I implemented your code and it seems to work great so far. I think it would be great to see this included in the working examples.

As you mentioned (and I missed - too excited to try it!), the fields need to be located outside the form. I feel that this issue should be addressed or it could lead others, like me, to wonder why the AND options always return zero (0) entries.

Turns out that the solution is pretty straight forward. Just add the following lines right after the $regex = "" line. So far, it looks like these option fields can then reside within the form.

    // Ignore the Advanced Options fields.
    if ($elm.context.name == "advance_options") {
        return;
    }

For any documentation that you might be preparing (or to help others):

Here is my understanding - assuming I have two check boxes, A and B that are selected. Please correct me if I have misunderstood of the Advanced Options:

Contains Word - OR (or Any) option - this option will return entries that contain either A or B, or both.
Contains Word - AND (or Both or All) option - this option will only return entries that contain both A and B.
Whole Word - OR (or Either) option - this option will return all entries that are just A or just B - and not combinations of the two.
Whole Word - AND option - Could you please explain to me what Whole Word (AND) does? It just seems to me that a field can't include only A and only B at the same time so your code must be doing something else.

Thank you again! Your help is so greatly appreciated.

Best regards,

Michael

ghost · 2019-02-26T21:26:42Z

4. Whole Word - AND option - Could you please explain to me what Whole Word (AND) does? It just seems to me that a field can't include only A and only B at the same time so your code must be doing something else.

This options looks for ONLY A and B (together), so if you use the example on my site and pick ONTARIO and QUEBEC (or ONTARIO AND CANADA) should get a result of one or more.

Not sure if your using the latest version I did make some changed to the script I posted about 9 days ago (noticed some bugs).

michael-milette · 2019-02-27T03:02:19Z

Hi @thekodester ,

Thank you for the explanation about Whole Word (AND) option. Makes sense.

I compared the JavaScript code you posted 9 days ago to the code in your working example page at www.thekodester.ca/wet/tables/checkbox-filter and they appear to be identical. This is what I am using. Is there a newer version somewhere that I should be looking at?

I am assuming that there hasn't been any change for this in the wet-boew.js file.

Best regards,

Michael

ghost · 2019-02-27T03:11:23Z

Oh I reread it and think I get it now. You mean adding the advance filters into the actual form. Sorry my confusion.

michael-milette · 2019-02-27T03:20:31Z

Indeed. So for example, one of my pages has several drop down "select" fields and then one fieldset of check boxes. Now the AND/OR radio buttons will only apply to the checkboxes so I wanted to put the And/Or with the checkboxes to avoid any confusion (like people thinking that it would AND/OR the dropdown fields). In order to include the And/Or fields within the form, I had to add the previously mentioned lines of code.

ghost · 2019-02-27T03:22:14Z

Yes for sure!

Also does this seem better or should I label the demo differently?

michael-milette · 2019-02-27T03:31:43Z

Contains Words is fine. I had to think about what Whole Word meant.

In our forms, I actually preceded the list of checkboxes with something like "Include O Any or O All of the following checked terms:" for the "Contains Words" field. I felt this would be clearer to non-technical visitors. Still doing some Guerilla style QA testing around the office.

As for Whole Word, I am still struggling to figure out how to explain this on the page in a way that won't require additional instructions. As you saw in my previous message, it wasn't even clear to me :-D

Michael

ghost · 2019-02-27T03:36:07Z

Maybe 'Keyword' or 'Exact Phrase' or 'Entire Word'?

If explanation is need maybe something like: "It will only be applied if it matches the whole word/words."

Also going off your original post could be: Any, Both, Either, And (with an explanation when hovered on)?

michael-milette · 2019-02-27T03:42:42Z

Ha! It is so funny you suggested that.

As a matter of fact, O Any, O Both or O Either is exactly what I came up with earlier this afternoon. Then I tried to translate "Either" into French and none of the francophones around me could come up with a translation. Even Termium and Larousse didn't have anything useful. I may need to give professional translators a shot at this. "Soit" came closest but it doesn't make sense on its own.

ghost · 2019-02-27T03:43:52Z

Okay, we'll do up a PR.

michael-milette · 2019-03-01T17:39:30Z

Hi @thekodester ,

I discovered an issue with the AND/OR script. It seems to break filtering of fields that contain numbers. For example, in our datatable, we have a field that contains just a single year (publication year). Without your script, it filters fine. As soon as I add your script, it returns 0 entries.

Unfortunately our implementation is only on a development server so, to demonstrate the issue, I made a copy of the working example script from your site as well as your JSON file and put these on our public site with a base href so that it gets the WET files from your server. I added a "PUBYEAR" field to the JSON file and modified some of the dates so that it would return data for years 2016 to 2018. Then I added a select dropdown field to your form.

See: https://cfc-swc.gc.ca/test/checkbox-filter.html

Any idea why it would fail just with numeric fields containing years (it is our only numeric field)?

Best regards,

Michael

ghost · 2019-03-01T18:26:56Z

~~So there is two issue, one I have updated. The other appears be an issue with DataTables.~~

~~I revert all code back (even using the original search method) $datatable.column( $column ).search( $value ).draw(); DataTables fails to search using 2016.~~

Scratch that...gah...found the issue...needed some coffee. lol

michael-milette · 2019-03-01T19:17:03Z

Sorry for the confusion. The example I had prepared for you had issues in the JSON file in addition to having the incorrect offset. It is fixed now. However, it still does not work using WET from your site.

I have it working on our dev site (only without the extra JS for the AND/OR) however I pulled the latest version of WET from https://github.com/wet-boew/themes-dist.git

I was happy to see the updated wet-boew.js included and that the AND/OR was working. I was surprised when the Year field failed. I was pretty sure I had checked this previously while using the wet-boew.js you posted when working on the checkboxes but I could be mistaken.

Let me know when you figured it out. I will be happy to do some testing.

Best regards,

Michael

ghost · 2019-03-01T19:19:59Z

Nothing to be sorry about, just my own confusion.

The problem is the old patch (never triggered):

if ( $column !== $lastColumn && $lastColumn !== -1 ) {
 $value = "";
}

It too specific. Probably should be:

if ( $column !== $lastColumn || $lastColumn === -1 ) {
 $value = "";
}

michael-milette · 2019-03-01T20:58:10Z

Hi @thekodester ,

Any luck with the numeric (Publication Date) filter field? You mentioned that you found the issue but didn't mention what it was. Not trying to pressure you or anything of course :-)

Best regards,

Michael

ghost · 2019-03-01T20:59:26Z

Issue related too: #8592 (PR for the fix).

michael-milette · 2019-03-01T21:08:42Z

I implemented the change you recommended. It doesn't appear to help.

Are you sure that is the solution? I could be doing something wrong but it seems to me that the line right above the line you modified is a test to check for checkboxes:

} else if ( $elm.is( ":checkbox" ) ) {

How would that affect a "select" (dropdown) tag?

Michael

ghost · 2019-03-01T21:13:20Z

Issue is with the order:

I updated the PR, to prevent this or any feature edge case, the checkboxes use the own value variable, and the $value gets erased each time (as soon as the loop starts).

ghost · 2019-03-10T03:23:18Z

Should be all fixed now and I updated #8588.

ghost · 2019-03-13T05:31:02Z

I updated the code so the advanced options only applies to one set of checkboxes. The PR has more info.

michael-milette · 2019-03-13T12:31:38Z

Hi Cody, Thanks for the update. In case it is of interest to you or anyone else, here is the latest language we are using for the AND/ORs - which more people seem to understand. In English: o Includes any, o Includes all, o includes only or o includes just the combination of the following checked terms: In French: o Comprend, o comprend toutes, o comprend seulement les terms ou o comprend uniquement la combinaison des terms cochés suivants : Best regards, Michael

ghost · 2019-03-21T15:27:13Z

Hey it was approved and we'll be in the next release of WET.

DEMO:
https://wet-boew.github.io/v4.0-ci/demos/tables/tables-en.html

DOCUMENTATION:
https://wet-boew.github.io/v4.0-ci/docs/ref/tables/tables-en.html

Advanced Options

Allows the user to change how checkboxes are filtered.

Add data-aopts='{"type": "", "column": ""}' arrtibute to any checkbox/radio element to apply the advanced options.

Available filter types:
ANY - This option will return entries that contain either A or B, or both.
BOTH - This option will only return entries that contain both A and B.
EITHER - This option will return all entries that are just A or just B - and not combinations of the two.
AND - This option will only return entries that are a combination of A or B.
Column is the checkboxes that the filter well apply too (must match there 'data-column').

<label for="advanced_options1_1"><input type="radio" name="advanced_options1" id="advanced_options1_1" data-aopts='{"type": "any", "column": "5"}' checked />Any</label>

As for naming the on the Front-end/User-end you can name them whatever works for you that is completely customizable since it only need data-aopts='{"type": "[FILTER_TYPES]", "column": "[NUMBER]"}' (on each option) to work.

ghost · 2020-10-17T04:28:50Z

This can be closed now. :)

duboisp added Query: Question Query: Feature Request Feature: Table enhancement (datatable) labels Feb 8, 2019

ghost mentioned this issue Feb 20, 2019

DataTable filter with SELECT MULTIPLE field #8546

Open

ghost mentioned this issue Mar 7, 2019

Checkbox Filtering Bug #8592

Merged

ghost mentioned this issue Oct 5, 2020

filter wb-tables by range? #8953

Open

DataTable: Multiple filters for same field (AND) #8559

DataTable: Multiple filters for same field (AND) #8559

Comments

michael-milette commented Feb 8, 2019

duboisp commented Feb 8, 2019

RobJohnston commented Feb 8, 2019 • edited

ghost commented Feb 8, 2019 • edited by ghost

ghost commented Feb 8, 2019 • edited by ghost

michael-milette commented Feb 8, 2019

michael-milette commented Feb 14, 2019

ghost commented Feb 14, 2019 • edited by ghost

michael-milette commented Feb 15, 2019 • edited

ghost commented Feb 17, 2019 • edited by ghost

ghost commented Feb 17, 2019 • edited by ghost

ghost commented Feb 17, 2019 • edited by ghost

ghost commented Feb 17, 2019 • edited by ghost

ghost commented Feb 17, 2019 • edited by ghost

michael-milette commented Feb 18, 2019

michael-milette commented Feb 26, 2019

ghost commented Feb 26, 2019 • edited by ghost

michael-milette commented Feb 27, 2019 • edited

ghost commented Feb 27, 2019 • edited by ghost

michael-milette commented Feb 27, 2019

ghost commented Feb 27, 2019

michael-milette commented Feb 27, 2019

ghost commented Feb 27, 2019 • edited by ghost

michael-milette commented Feb 27, 2019

ghost commented Feb 27, 2019

michael-milette commented Mar 1, 2019

ghost commented Mar 1, 2019 • edited by ghost

michael-milette commented Mar 1, 2019

ghost commented Mar 1, 2019 • edited by ghost

michael-milette commented Mar 1, 2019

ghost commented Mar 1, 2019 • edited by ghost

michael-milette commented Mar 1, 2019

ghost commented Mar 1, 2019 • edited by ghost

ghost commented Mar 10, 2019

ghost commented Mar 13, 2019 • edited by ghost

michael-milette commented Mar 13, 2019 via email

ghost commented Mar 21, 2019 • edited by ghost

ghost commented Oct 17, 2020

RobJohnston commented Feb 8, 2019 •

edited

ghost commented Feb 8, 2019 •

edited by ghost

ghost commented Feb 8, 2019 •

edited by ghost

ghost commented Feb 14, 2019 •

edited by ghost

michael-milette commented Feb 15, 2019 •

edited

ghost commented Feb 17, 2019 •

edited by ghost

ghost commented Feb 17, 2019 •

edited by ghost

ghost commented Feb 17, 2019 •

edited by ghost

ghost commented Feb 17, 2019 •

edited by ghost

ghost commented Feb 17, 2019 •

edited by ghost

ghost commented Feb 26, 2019 •

edited by ghost

michael-milette commented Feb 27, 2019 •

edited

ghost commented Feb 27, 2019 •

edited by ghost

ghost commented Feb 27, 2019 •

edited by ghost

ghost commented Mar 1, 2019 •

edited by ghost

ghost commented Mar 1, 2019 •

edited by ghost

ghost commented Mar 1, 2019 •

edited by ghost

ghost commented Mar 1, 2019 •

edited by ghost

ghost commented Mar 13, 2019 •

edited by ghost

ghost commented Mar 21, 2019 •

edited by ghost