CRM-16581 API and checking if a custom field is empty is ignored

https://github.com/civicrm/civicrm-core/pull/5924

I haven't had the chance to fix all the unforeseen issues created when allowing empties through at line 1538. That one change appears to open a can of worms.

The main detrimental side affect (other than a few failed failed tests) is that the global search doesn't work as the query generator adds "And ()" which breaks the mySql syntax.

I have improved the PR changes https://github.com/civicrm/civicrm-core/pull/6044 to work for all operators against multi-select and single select custom fields in api, especially in this commit https://github.com/monishdeb/civicrm-core/commit/0ea0a703666710d2e1e79ba8da13dcad322005e1 and you are right about that And() part.

Also handling for created_date, modified_date etc. is already done in 4.6 (please check https://github.com/civicrm/civicrm-core/pull/5914/files) and will be introduced in master/4.7 via merge-forward

I have taken a look at these and looks like some good improvements.. but do either of these pull requests deal with the API searching/filtering out empty values? rather than just ignoring them?

i.e. an API parameter such as custom_111 != "" (empty)

Alex, already there is operator available for that NOT EMPTY/EMPTY and I have checked with both, it's working fine. Also in civicrm you cannot enter a empty value "" for an option bcoz it is a required field so there won't be such case to check empty value custom_111 != "" OR custom_111 = "".

I think this PR is ready to merge

Also Search Builder is fixed for contact groups in this ticket https://issues.civicrm.org/jira/browse/CRM-16483 against 4.6 so in near future, the fix will be introduced to master/4.7 too.

I have created an example at dmaster where the API can't be used to filter out blank values..

The custom fields are called.. 'might be null or empty x'

http://dmaster.demo.civicrm.org/civicrm/api#explorer

$result = civicrm_api3('Contact', 'get', array(
'sequential' => 1,
'return' => "custom_13,custom_14,custom_15",
'custom_13' => array('IS NOT NULL' => 1),
));

There doesn't appear to be a NOT EMPTY/EMPTY operator on the API explorer..

IS EMPTY and IS NOT EMPTY seems to translate to IS NULL and IS NOT NULL in the code so there isn't actually anything that checks for empty rather than null.

My bad I was looking in wrong direction, actually in some case api value param is in array('sqlop' => (mixed)values) format. So IS NOT NULL/IS NULL falls under this use-case. Yes you are right EMPTY operators aren't available in api explorer. But in search builder for sure where it is eventually looks for the empty string. Anyways did the additional fix https://github.com/civicrm/civicrm-core/pull/6058 now it is working against NULL operator case.

Do you have any hints on how to go about making the API check for empties (rather than nulls)?

Should IS EMPTY and IS NOT EMPTY be added to the API explorer?

Sounds like this is still ongoing, regarding the 'empty' issue described, so I've reopened it. Hope that's OK.

Alex, when the api-explorer was re-written as per the https://github.com/civicrm/civicrm-core/pull/2924/files changes, CRM_Core_DAO::acceptedSQLOperators() operators are available which doesn't include IS EMPTY/IS NOT EMPTY operator, because its a generic function in a way that it returns those operators which can be applied to all kind of data-type. However I agree that ONLY fields with 'string' data type should have those extra two EMPTY operators to lookout for empty values, also the CRM_Contact_BAO_Query::buildClause() funtion supports it which eventually gets called.

So I extended the api to accept empty operators for ONLY string type of fields. This is my changes https://github.com/civicrm/civicrm-core/pull/6058, so now like IS NULL/IS NOT NULL operators you can use empty operators
e.g. 'custom_10' => array('IS NOT EMPTY => 1)
(But these two operators won't be available in api-explorer as the reason stated above.)

But unfortunately you can't use empty values "" with != or <> operators as per the code in https://github.com/civicrm/civicrm-core/blame/master/CRM/Contact/BAO/Query.php#L1526 which bypasses/avoids to build search parameters with empty values.

Some of these changes were introduced in 4.6.x so I've updated the issue. This may also be causing some side-effects in how custom fields are read in the Views integration offered by the CiviCRM Entity module. I've posted in that module's issue queue here: https://www.drupal.org/node/2549607