Jump to content

Pages database filters give incorrect results for booleans


mcsg
 Share

Recommended Posts

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?

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...