Query builder - Searching int value=0

[sticcino]

v:2024

Field Name: status
type: TINYINT
values: 0 or 1

field type: "Radio"
Value: 1 - Yes
Value: 0 - No

on list page "status" displays correct values (yes/no)

if query: status == 1/yes, returns 3609 records from 4445 -- which is correct (836 are inactive...)

if query: status == 0/no, returns 4445 records from 4445 -- which is incorrect should return 836

i activated the extended search and noticed that 2 fields createdby and modifiedby have a default value of my admin account (-1) that is set, if i clear these the search for active = 0 succeeds on the extended panel, but on the query form it still doesn;t return the status =0 result set.

i disabled/removed them from the search options, so on the extended search the query works with status=0, but on the query builder it doesn't show those two fields in the picklist, but still doesn't filter correctly.

[arbei]

You may

1. Check HTTP Response and view the HTTP header of the HTTP request, make sure the value (0) is sent to server.
2. You may enable Debug and check the SQL executed first. After enabling Debug, you can see the executed SQL in the page's debug panel or in the log file.

[sticcino]

When status =1
show query:
Current filters: Activestatus = 1

Payload:
csrf_name: csrf_name
csrf_value: csrf_value
t: washrooms
action: search
modal: 1
rules: {"condition":"AND","rules":[{"id":"status","field":"status","type":"integer","operator":"equal","value":1,"data":{"format":""}}],"not":false,"valid":true}
washrooms_query_builder_rule_0_value_0: 1

when status=0
show query: [blank] - no query filter displayed

Payload:
csrf_name: csrf_name
csrf_value: csrf_value
t: washrooms
action: search
modal: 1
rules: {"condition":"AND","rules":[{"id":"status","field":"status","type":"integer","operator":"equal","value":0,"data":{"format":""}}],"not":false,"valid":true}
washrooms_query_builder_rule_0_value_0: 0

the Preview/Response headers appear to be the same and are should all the records and the status =1

[sticcino]

interesting...

test:
business_Status = TEMP_CLOSED

sql result:
sql: SELECT COUNT(*) FROM washrooms WHERE poi_Business_Status = 7001, executionMS: 0.0031921863555908
sql: SELECT * FROM washrooms WHERE poi_Business_Status = 7001 ORDER BY DateCreated DESC, ModifiedBy DESC LIMIT 10, executionMS: 0.012639999389648

test:
status = 0

sql result: (should return 836 records)
sql: SELECT COUNT(*) FROM washrooms, executionMS: 0.0013608932495117
sql: SELECT * FROM washrooms ORDER BY DateCreated DESC, ModifiedBy DESC LIMIT 10, executionMS: 0.01725697517395

test:
status = 1

sql result:
sql: SELECT COUNT(*) FROM washrooms WHERE status = 1, executionMS: 0.0031828880310059
sql: SELECT * FROM washrooms WHERE status = 1 ORDER BY DateCreated DESC, ModifiedBy DESC LIMIT 10, executionMS: 0.016381025314331

it appears the code is not adding WHERE if any search criteria value = 0, i also tried another item i have "sync with maps" with a 0 or 1 value, querying for 0 returned the full sql statement with no WHERE clause.

test with sync_with_gmaps = 0
sql: SELECT COUNT(*) FROM washrooms, executionMS: 0.0013608932495117
sql: SELECT * FROM washrooms ORDER BY DateCreated DESC, ModifiedBy DESC LIMIT 10, executionMS: 0.02022910118103

[Webmaster]

Please update to the latest template (Tools -> Update Template) and try again.

[sticcino]

thank you, Issue resolved with the new template.