CiviCRM Community Forums (archive)

I have several custom checkbox fields defined in my database; of course, since one or more value can be checked, the field can have none, one, or more than one value (up to the limit of how many available choices there are for the field).

What I would like to be able to do, but can't quite figure out how to do so using Search Builder, is define smart groups whose criterion are something like "where field X has 1 or more values," "where field X has 2 or more values," "where field X has 3 or more values," etc.  For purposes of these particular queries, the field value themselves are not important.

Any ideas?

Hmm.

What's the actual internal format of a CiviCRM checkbox field?  Is it
a) "token [delimiter token]*" or
b) "delimiter token [delimiter token]* delimiter"   
(i.e. if a field has N boxes checked, does the field contain N tokens and N-1 delimiters, or N+1 delimiters and N tokens)?

Reason I ask is, I found the following suggestion for counting the number of words a given SQL Server string contains:

Assuming that the space character separates each word, counting the number of words in a string can be performed using the following query on the given definition of a Health Insurance:

DECLARE @String VARCHAR(4000)
SET @String = 'Health Insurance is an insurance against expenses incurred through illness of the insured.'

SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1

A technique similar to this might work for my purposes, if the actual internal format of a checkbox field is as in (a) above -- but I would also need to know what separator character is used by CiviCRM.  Of course, if the actual internal format of a checkbox field is as in (b) above, then I would need to subtract 1 instead of add 1.

Of course, I am still faced with never having written a custom CiviCRM search (i.e. I do not have a working example to go by).  All I know is the name of the MySQL table that contains my checkbox field, and of course the name of the particular checkbox field in question. 

Well, instead of having to select on something like:
   LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1

via phpMyAdmin I was able to determine that I really only need to do the following:
   where LENGTH(`fieldName`) >= N
for only a few different values of N

Unfortunately, my knowledge of PHP is too limited to be able to make much sense out of the "Creating A Custom Search Extension" link :-(

I just want to select Individual (i.e. as opposed to Household/Organization/Activity) records; at one point I was hoping that a "quick and dirty" solution would be to formulate a query (e.g. "where `fieldName` IS NOT NULL"), save it as a new smart group, and just edit the where_clause of the corresponding civicrm_saved_search to replace the "where `fieldName` IS NOT NULL" with "where LENGTH(`fieldName`) >= N". 

Yes, I know that's probably not supported... but is there any reason this approach would not work?  are there any other "interconnects" that I would have to edit?

hey erich:

do u wanna get on irc and discuss this. Not sure i follow what u r trying to do etc.

The mapping field basically stores the user input in a structured manner and not a lot of the query stuff, which is dynamically generated by CRM/Contact/BAO/Query.php

lobo

>> Not sure i follow what u r trying to do

> Smart Group (or other) query where the results of interest are those records where:
> LENGTH(field) operator value (e.g. "LENGTH(field) > 3")

Does this sufficiently describe what I am trying to do?  Or do you need more info?

Anyone?

First I was told that I would need to write a custom search.  Then I was told that queries are dynamically generated by CRM/Contact/BAO/Query.php (recall that I only want to change the search criteria to use the SQL LENGTH() function, I don't want to change what fields are displayed in the report output or anything like that).

Basically, I just want to create a set of queries where the results are those records in my database where:
"LENGTH(custom_data_set.custom_field) operator value" (e.g. "LENGTH(custom_data_set.custom_field) > 3")

The example at http://wiki.civicrm.org/confluence/display/CRMDOC41/Creating+A+Custom+Search+Extension describes how to retrieve a specific field for individual contacts, which is not what I want to do.  I want to retrieve individual contacts based on the length of a specific field (i.e. not the value of a specific field, which is all that the Query Builder allows me to do)

> You should adapt the sql in the example to meet your specific needs

What I want is a set of queries that returns Name/Address/City/State/Postal/Country/Email/Phone (i.e. the same columns as all the other CiviCRM searches) for all contacts where the search criteria is simply:
   LENGTH(`civicrm_value_custom_group_1.column_name`) >= N
for different values of N.  Basically, N=3 for query1, N=6 for query2, N=9 for query3, and so on. 

As I understand custom queries, I'll eventually need separate files for query1, query2, query3, etc.  Fair enough.

I specified a Custom PHP Path Directory via CiviCRM » Administer CiviCRM » Global Settings, and I created a CRM/Contact/Form/Search/Custom directory below that.

I looked at all the php files in administrator/components/com_civicrm/civicrm/CRM/Contact/Form/Search/Custom and it appeared to me that the Basic.php file was the only one that had the right set of fields in the __construct function.  So, I copied Basic.php to my newly-created <Custom_PHP_Path_Directory>/CRM/Contact/Form/Search/Custom under a new name, and updated the class definition in the copied file.  So far, so good. 

Since I have no need to edit my search criteria for this particular set of queries, I tried completely deleting the buildForm function in my copied file, but I got the following error message:

Fatal error: Class CRM_Contact_Form_Search_Custom_1R contains 1 abstract method and must therefore be declared abstract or implement the remaining methods (CRM_Contact_Form_Search_Interface::buildForm) in <Custom_PHP_Path_Directory>/CRM/Contact/Form/Search/Custom/1R.php on line 132

So, I added back a buildForm function that is just:

    function buildForm( &$form ) {
    }

and now get just a "Search" button in my Edit Search Criteria section.  Not ideal, but at least I know I'm invoking my custom query script.

Finally, using the "Creating a Custom Search Extension" example as a guide, I added my required selection criteria as follows:

    function where( $includeContactIDs = false ) {
        $clauses = array( );
        $clauses[] = "LENGTH(`civicrm_value_custom_group_1.column_name`) >= 3";

        if ( $whereClause = $this->_query->whereClause( ) ) {
            return $whereClause;
        }
        return ' (1) ' ;
    }

But I'm clearly missing something, as all rows in my database are being returned, and not just the rows that meet the selection criteria.

What am I missing in my where function?  And how do I completely remove the need for the buildForm function (i.e. when I select my custom search from CiviCRM » Find Contacts » Custom Searches, I want to go straight to the results)?  Finally, I noticed that the "Actions" column heading is missing from above the column with the View/Edit/Map links... how do I add it back?

just append the parameter force=1 to the url.

something like:

http://drupal.demo.civicrm.org/civicrm/contact/search/custom?csid=5&reset=1&force=1

lobo