Pages database filters give incorrect results for booleans

[mcsg]

We have a database of schools that we're searching with the standard database page and filters.

The odd part is that boolean fields as filters are searching as if 0 is selected rather than <nothing>

Example:
 

/?advanced_search_submitted=1&csrfKey=53aae60572ae6367eaeaa2280dc62610&content_field_268[1]=North%20Chicago&&&content_field_304_checkbox=&content_field_305_checkbox=&content_field_306_checkbox=&content_field_307_checkbox=&sortby=field_267&sortdirection=asc&record_type=all&time_frame=show_all

There are 327 schools in 268="North Chicago"
But only 5 show up in the database page search.
These 5 have 304-307 = 0
It should find ALL the North Chicago schools regardless of those checkboxes values.

It appears to be searching:

SELECT * 
FROM `cms_custom_database_21` 
WHERE (`field_307` = 0 )AND
      (`field_306` = 0) AND
      (`field_305` = 0) AND
      (`field_304` = 0) AND
      (`field_268` LIKE '%North Chicago%')

rather than

SELECT * 
FROM `cms_custom_database_21` 
WHERE (`field_307` LIKE '%%') AND 
(`field_306` LIKE '%%') AND 
(`field_305` LIKE '%%') AND 
(`field_304` LIKE '%%') AND 
(`field_268` LIKE '%North Chicago%')

Can the database boolean fields or filters be changed in order generate a query that does not add the 0 value?

 

 

[Morrigan]

It’s not incorrect technically. If the toggle is off then it’s filtering for the listings that have the toggle off. I understand your need (I have wanted it before and ended up removing these toggles for this exact reason). I think you should post in suggestions though.

I don’t think it’s as easy as making 0 NULL though. Because some may actually have a use/need for it to recognize those toggles.

[bfarber]

Yes if you have a Yes/No field then it makes sense if "No" is selected we honor that in the search. We'd essentially need a checkbox next to the toggle that says "Ignore this" or similar to accomplish what you are after without limiting the current available functionality.

[mcsg]

Yes, it would have to have an "ignore this" for a boolean to work.

I will have to combine the fields into a multi-select, I guess, for the filters to work as expected. Before I go spinning my wheels, does that seem like it would work as we wanted?

I want to search for everything in "North Chicago", regardless of the school type. So if school_type is a multi-select, and the filter has nothing selected, would it find all schools in North Chicago regardless of the school_type(s)? Or would it only find North Chicago schools that have no school_type selected?