Additional criteria to api

[Eileen]

We have a few methods for advanced filtering in the API. The one we see as the one we are implementing it

http://svn.civicrm.org/civicrm/trunk/api/v3/examples/Address/AddressLike.php

But, for many api we also support the slightly older / more core-like

http://svn.civicrm.org/civicrm/trunk/api/v3/examples/Activity/DateTimeHigh.php

However, I just found that there is a problem with NULL values ie. (or the equivalent of the other format)

Code: [Select]

$params = array('contact_id_a' => $contact_id,
                  'version'  => 3,
                  'relationship_type_id' => 22,
                  'is_active' => 1,
                  'filters' => array('start_date_high' => date('Ymd'),
                                     'end_date_low' => date('Ymd') )
  );
  return civicrm_api('relationship', 'get', $params);
Will not return a value if end date is null.

I could make it work as follows - and probably argue the case fairly comfortably for date fields at any rate....

Code: [Select]

Index: api/v3/utils.php
===================================================================
--- api/v3/utils.php (revision 39148)
+++ api/v3/utils.php (working copy)
@@ -405,13 +405,21 @@
  * @param object $dao DAO object
  */
 function   _civicrm_api3_apply_filters_to_dao($filterField,$filterValue, &$dao ){
-    if( strstr($filterField, 'high') ){
+
+  if( strstr($filterField, 'high') ){
+     if($filterValue == ''){
+       //ideally add a check here for field type
+      throw new Exception("You have set $filterField to an empty string");
+     }
         $fieldName = substr($filterField ,0,-5);
-        $dao->whereAdd( "($fieldName <= $filterValue )" );
+        $dao->whereAdd( "($fieldName <= $filterValue OR $fieldName IS NULL)"   );
     }
     if(strstr($filterField, 'low')){
+     if($filterValue == ''){
+        throw new Exception("You have set $filterField to an empty string");
+     }
         $fieldName = substr($filterField ,0,-4);
-        $dao->whereAdd( "($fieldName >= $filterValue )" );
+        $dao->whereAdd( "($fieldName >= $filterValue OR $fieldName IS NULL)"   );
     }
 }
 /*

[totten]

I think the suitability depends on the meaning of "null" for a given field. For example, these two searches use the same apparent data-structure but should be implemented differently because "null" has a different in each context:

a) Find all {civicrm_relationship}s that were (not) active on date X
b) Find all {civicrm_event}s that were (not) held on date X

In the case of the start/end date on relationships, "null" means (IIRC) that the relationship has no bound. For example, if start_date is defined but end_date isn't, then the relationship never ends. If both values are null, the relationship endures forever.

In the case of the start/end date on events, the same datapoints (start_date is defined but end_date isn't) would mean that the event only endures for one day (the start date).

If the query-builder assumed the same interpretation of nulls for both "a" and "b", then it would return invalid results for one situation or the other.

As a practical matter, I don't know if this patch would be a problem for any actual users. Ideally, we would provide developers with a search interface that supported boolean expressions and null-ity tests so that the downstream could decide how to handle it.

[Eileen]

hmm - I had a feeling it wouldn't be easy :-(

[xavier]

Eileen,

wouldn't no assigning the date_end be a solution on the call? or are you specifically looking for all relationships that are open ended?

As a tangential answer, I found dealing with relationship harder than necessary because you have to watch both active or with no date_end or date end in the future, and on some cases I had to fetch both a->b and b->a

I'd venture that most of the needs are "all the active relationships" and having the API answering that would help.

X+

[Eileen]

Hi,

Yes, another solution would be to have a custom filter on relationship dao for active relationships.

There is a BAO function that gets called if you pass in contact_id - but I tend to avoid it due to it not giving the result in an 'api-like' way. Passing in contact_a_id causes it to use our main api dao get function.

[Eileen]

Have created ticket to track this

http://issues.civicrm.org/jira/browse/CRM-9932

[Eileen]

OK - I updated per ticket

http://issues.civicrm.org/jira/browse/CRM-9932?focusedCommentId=41312#comment-41312
http://svn.civicrm.org/civicrm/trunk/api/v3/examples/Relationship/filterIsCurrent.php