Search Builder: IN (list of postal codes)

[michaels23]

Can I use Search Builder to find contacts IN a list (*not* a range) of postal codes, e.g., 70124,70128,70003,70119,70065,70129,70002,70005,70006,70056,70115 ?

Everything I've tried results in either "in valid format" or a DB error:

Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(\'70124\')' AND contact_a.contact_type IN ('Individual') ) ) AND (contact_a.' at line 1, 1064

TIA!

[Yashodha Chaku]

michaels23 :
This has been fixed for 3.3 and should be available in the next alpha release.
You could also apply the patch.

-Yashodha

[gastrit]

Hello!

I just had the same problem in my Civicrm 4.0/Drupal 7.0 installation and I also get the same message "Please enter correct Data (in valid format)" at demo.civicrm.org (drupal 7.X).

I am trying to search for contacts with addresses in Scandinavia (Sweden, Norway and Denmark) and my search criteria looks like this:
Individual -> Country -> Home -> IN -> (Sweden,Norway,Denmark) results in above mentioned error message
and
Individual -> Country -> Home -> IN -> ('Sweden','Norway','Denmark') results in no matches although I verified that my database contains contacts from Sweden (I used 'United States' at demo.civicrm.org and got no matches) and when I look in civicrm_saved_search.where_clause it has the following value:
( LOWER(civicrm_country.name) IN ('\\\'Sweden\\\'','\\\'Norway\\\'','\\\'Denmark\\\''))

Am I doing something wrong or is this still a bug?

//Jonas Tano

[xavier]

If you are on 4.0, you should upgrade to the latest version.

Can you try on demo.civicrm.org?

X+

[gastrit]

Thanx for fast reply in an old thread!

As I mentioned in my post I have already tried it on demo.civicrm.org:
("I also get the same message "Please enter correct Data (in valid format)" at demo.civicrm.org (drupal 7.X).")

I just tried it again with Firefox (used Chrome earlier) and got the same result:
I searched for (United States) using the IN operator (instead of the scandinavian countries) and didn't get any matches with apostrophes and got the error message when I tried without apostrophes. I also tried without parentheses with no luck. If I search for United States using the = operator I get matches.

To mee it seems to be a bug but I am fairly new to CiviCRM so I wouldn't really know. Could someone else please try to search for contacts in some countries using the IN operator in the search builder?

[Donald Lobo]

I suspect this is a bug / missing feature in search builder. You'll need to investigate and submit a patch. Search builder is not being actively developed or maintained

lobo

[gastrit]

Ok,

I think I have found a solution to my problem but I'm not sure if this is the way to go for search builder in general. In the file CRM/Contact/Form/Search/Builder.php in the function formRule() there is a check for if the search word is in valid format. The regular expression expects to find apostrophe (') before and after every search word for the IN operator. But in the code that builds the database question there is an escape functions that adds backslashes before every SQL-dangerous character (e.g. apostrophes).

So I simply removed the check for apostrophes (just for the IN operator) and my searches are now working. I also added the possibilty to write some scandinavian and german letters (å, ä, ö, æ, ø and ü). Here are the old and new code lines:

if ( $v[1] == 'IN' ) {
...
OLD

Code: [Select]

if (!preg_match( '/^[(][\']([A-Za-z0-9\'\,\s]+)[\'][)]$/', $inVal) ) {NEW

Code: [Select]

if (!preg_match( '/^[(]([A-Za-z0-9åäöÅÄÖæÆøØüÜœŒ\,\s]+)[)]$/', $inVal) ) {...
}

I was also annoyed by the fact that I couldn't learn myself to not put spaces after the commas so I added the possibility to do so :0) That means I added a trim for each of the values found within the parentheses in the file CRM/Contact/BAO/Query.php in the function buildClause():

Code: [Select]

case 'IN'
...
foreach ( $values as $v ) {
     $v = trim( $v, " " );    // NEW LINE OF CODE
     $val[] = "'" . CRM_Utils_Type::escape( $v, $dataType ) . "'";
}
I haven't tested this much but my searches works now and I have only changed code for the IN operator so I think this pretty safe to implement. Comments anyone?

[Donald Lobo]

Can you please file an issue and attach your patch (http://drupal.org/node/367392) to the issue. Also please add this forum link to the issue

thanx for investigating and patching. Much appreciated

lobo

[Eileen]

karma +

good sleuthing!

[gastrit]

Thanx 

This is my first patch ever so please bear with me. Is this somewhat correct? http://issues.civicrm.org/jira/browse/CRM-8548

[tklawsuc]

I know this is an old post but I am using v4.0.6 and it seems the patches have already made it into that release. However, when I try to search using IN for city with value (City1,City2) I get an error because the buildClause (line 1813 in my version) is being called without a datatype in the Query.php file. As a result buildClause does not escape the cities and the application fails with a db error because it is using (City1,City2) instead of ('City1','City2'). When I change the call to pass in 'String' for dataType it works fine. So the question is should the buildClass function use the String dataType as a default or should the call to buildClass for IN pass in the 'String' datatype?

[Donald Lobo]

can u try this patch:

Code: [Select]

Index: CRM/Contact/BAO/Query.php
===================================================================
--- CRM/Contact/BAO/Query.php   (revision 36807)
+++ CRM/Contact/BAO/Query.php   (working copy)
@@ -1811,9 +1811,15 @@
                         }
                     }
                     
+                    $type = null;
+                    if ( CRM_Utils_Array::value( 'type', $field ) ) {
+                        $type = CRM_Utils_Type::typeToString( $field['type'] );
+                    }
+
                     $this->_where[$grouping][] = self::buildClause( $fieldName,
                                                                     $op,
-                                                                    $value );
+                                                                    $value,
+                                                                    $type );
                     $this->_qill[$grouping][]  = "$field[title] $op $value";
                 }
                 


[tklawsuc]

That works and it's a much better solution than what I had. Thanks!