Author Topic: LIKE searching (Read 913 times)

Eileen · October 30, 2011, 06:15:20 pm

Hi,

One of the things we talked about doing for 4.1 was making it easier to do 'LIKE' searching using the API. We talked about adding it to copyValues() & I had a quick go & it seemed easy (my unit test gave the right result - also on fail but not shown).

Code: [Select]

    /**
     * Test civicrm_address_get with sort option- success expected.
     */
    public function testGetAddressLikeSuccess()
    {  
        civicrm_api('address','create',$this->params);
        $subfile = "AddressLike";
        $description = "Demonstrates Use of Like";
        $params = array( 'street_address' => '%mb%',
                         'version' => $this->_apiversion  ,
                         'sequential' => 1,
                          );
        $result = civicrm_api('Address', 'Get', ($params));
        $this->documentMe($params,$result,__FUNCTION__,__FILE__, $description,$subfile);
        $this->assertEquals( 0, $result['is_error'], 'In line ' . __LINE__ );
        $this->assertEquals( 1, $result['count'], 'In line ' . __LINE__ );
        $this->assertEquals( 'Ambachtstraat 23',$result['values'][0]['street_address'], 'In line ' . __LINE__ );
   }

Code: [Select]

Index: CRM/Core/DAO.php
===================================================================
--- CRM/Core/DAO.php	(revision 37037)
+++ CRM/Core/DAO.php	(working copy)
@@ -351,6 +351,8 @@
             if ( $exists ) {
                 if ( $pValue === '' ) {
                     $this->$dbName = 'null';
+                } elseif (stristr($pValue, '%')){
+                  $this->whereAdd($dbName . " LIKE '$pValue'");
                 } else {
                     $this->$dbName = $pValue;
                     $allNull = false;
Index: api/v3/utils.php
===================================================================
--- api/v3/utils.php	(revision 37037)
+++ api/v3/utils.php	(working copy)
@@ -380,13 +380,14 @@
 function _civicrm_api3_dao_set_filter (&$dao,$params, $unique = TRUE ) {
     $entity = substr ($dao->__table , 8);
 
-    $fields = _civicrm_api3_build_fields_array($dao,$unique);
-    $fields = array_intersect(array_keys($fields),array_keys($params));
+  //  $fields = _civicrm_api3_build_fields_array($dao,$unique);
+   // $fields = array_intersect(array_keys($fields),array_keys($params));
     if( isset($params[$entity. "_id"])){
         //if entity_id is set then treat it as ID (will be overridden by id if set)
         $dao->id = $params[$entity. "_id"];
 
     }
+    $dao->copyValues($params);
     //apply options like sort
     _civicrm_api3_apply_options_to_dao($params, $dao );
 
@@ -406,12 +407,6 @@
         }
     }
 
-
-    if (!$fields)
-        return;
-    foreach ($fields as $field) {
-        $dao->$field = $params [$field];
-    }
     if(!empty($params['return']) && is_array($params['return'])){
       $dao->selectAdd( );
       foreach ($params['return'] as $returnValue ) {

Eileen · October 30, 2011, 06:51:05 pm

Lobo has pointed out we'd be better having a separate function that adds the 'like' clauses e.g

$dao->copySearchValues();

- this could be called by 'get' variants & wrap the 'copyValues();' & add the WHERE clauses.

(We'd need to slightly restructure api to call this ONLY when the action is 'get' - also changing the API to call copyValues() overall will add better support for unique field names).

So, question

Do we just support

street_address = '%mb%';

Or do we follow on from the _high _low filtering in the BAO & API & go for

street_address_like = 'mb'

(which would tend to suggest that we could extend to

street_address_in = '1,2,3'

(which may not be a good thing in a multilingual set-up).

Or, more flexible

'filters.street_address' = "LIKE '%mb%'";
'filters.street_address' = 'IN (1,2,3);

Like we do with SORT

xavier · October 31, 2011, 02:28:24 am

What about having several format for a field?

street_address = '42 end of loop' (as today)

contact_id = array (1,2,5) (translated as IN 1,2,5)

street_address = array ('like' => 'loop%') (translated as LIKE 'loop%')

Probably will need to be presented differently for ajax, eg.

&contact_id=1,2,3
&street_address.like=loop%

X+

xavier · October 31, 2011, 02:58:28 am

We are discussing with Eileen and I think polymorphism should be used in more case:

activity_date = array ('<'=>'12/10/1980', '>'=>'31/12/2000')

- that is implemented now as filter ('activity_date_low' if I recall)

I don't think we will support every sql combination in the foreseeable future, but I think it should be the "right" way of adding it

I think that for the complex api calls over rest or ajax, we should stick to /api/json?{whateveryouwant} (ie the param is a json formatted string, already implemented)

X+

Eileen · October 31, 2011, 03:10:29 am

So,

Just to say where we are at so far

1) we think we need a function to handle 'get' filters based on mysql including

LIKE
IN
>/ <
>!=<

2) we're expecting this to apply to API DAO based functions (about 60%) which are the main functions we have managed to stdise

3) The function used to set the filters will be used for the API only @ this stage (although it may sit on DAO.php or in Utils.php). Probably better in DAO.php as the object is already instantiated

4) main discussion is around format. Xavier's suggestion is to use operators as array key activity_date = array ('<'=>'12/10/1980', '>'=>'31/12/2000') & he claims it works. It makes me nervous but he assures me it works in all required formats.

Donald Lobo · October 31, 2011, 07:55:23 am

Any specific reason not to add ALL of mysql operators, especially if you are passing it onto mysql as the where clause?

lobo

xavier · October 31, 2011, 08:20:56 am

Hi,

No specific reason (they should indeed IMO) but I think these have to be whitelisted (ie. by default, it's rejected until programmed that it should work). And that's likely we will start implementing what we know/need first.

But as we will not passing it onto mysql directly (unless one can prove that it won't allow any sql injection), handling every mysql operator is a mid term goal.

X+

Erik Hommel · November 01, 2011, 10:59:18 am

Agree with the whitelisted option, sounds like the best way to move forward through versions. Do not feel I can contribute to the syntax questions, all options fine with me. I feel I do need to add API documentation to the list for the Euro code sprint?

Eileen · November 01, 2011, 01:37:20 pm

Erik - have you caught up with the _spec functions & the fact they are now showing in

http://api.civicrm.org/v3/ (& soon api explorerer I hope)

Check address_create - you can see required fields & default fields marked & they are the same fields that are checked by the code (as they are defined in code).

I expect the definition will be moved into the xml later but from the api wrapper's point of view that is black box stuff

Erik Hommel · November 01, 2011, 01:58:24 pm

Yep, I did see some of those. I was thinking more of webcasts of presentations of examples, based on fairly simple use cases.

CiviCRM Community Forums (archive)

News:

Author Topic: LIKE searching (Read 913 times)

Eileen

LIKE searching

Eileen

Re: LIKE searching

xavier

Re: LIKE searching

xavier

Re: LIKE searching

Eileen

Re: LIKE searching

Donald Lobo

Re: LIKE searching

xavier

Re: LIKE searching

Erik Hommel

Re: LIKE searching

Eileen

Re: LIKE searching

Erik Hommel

Re: LIKE searching